How to parametrize DDL in a plpgsql procedure?

Question

How do I parametrize DDL statements in a plpgsql procedure? Namely, I want to use the org_label param as the role and schema name but the code below takes it literally and I end up with the role "org_label":

create or replace procedure create_org_schema (org_label varchar(63)) -- max identifier len in pgsql is 63
language plpgsql AS $$ BEGIN
  create role org_label IN GROUP organization;
  GRANT org_label TO ardoq_api_customers; -- so that AUTHORIZATION... below works
  CREATE SCHEMA AUTHORIZATION org_label;
  SET ROLE org_label;
  GRANT USAGE on SCHEMA org_label to ardoq_api_customers; -- so that we can list it
  RESET ROLE;
END $$;

I have tried to change the first line to:

EXECUTE 'create role $1 IN GROUP organization' USING org_label;

But that fails with:

ERROR:  syntax error at or near "$1" at character 13
QUERY:  create role $1 IN GROUP organization

Answer 1

You can not. The manual:

Another restriction on parameter symbols is that they only work in SELECT, INSERT, UPDATE, and DELETE commands. In other statement types (generically called utility statements), you must insert values textually even if they are just data values.

EXECUTE .. USING does not work for this. You have to concatenate the query string before executing. Typically, format() is most convenient to do that safely:

CREATE OR REPLACE PROCEDURE create_org_schema (org_label varchar(63)) -- max identifier len in pgsql is 63
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format ('CREATE ROLE %I IN GROUP organization', org_label);
   -- ...
END
$func$;

Note the use of the format specifier %I to properly quote the identifier and defend against otherwise possible SQL injection. Use legal, lower-case names to keep your life with Postgres simple.

See:

• "ERROR: there is no parameter $1" in "EXECUTE .. USING ..;" statement in plpgsql
• SQL injection in Postgres functions vs prepared queries
• Are PostgreSQL column names case-sensitive?