"ERROR: there is no parameter $1" in "EXECUTE .. USING ..;" statement in plpgsql

Question

I have a plpgsql function to create child tables using table inheritance in PostgreSQL like this:

CREATE TABLE parent_table (
  value integer,
  end_time timestamp without time zone
);

CREATE OR REPLACE FUNCTION mk_child(_year INTEGER, _month INTEGER)
  RETURNS text AS $$
DECLARE
  tname varchar;
  start_date date;
  end_date date;
  next_month varchar := (_month + 1)::text;
  next_year varchar := (_year + 1)::text;
BEGIN
  tname := 'child_y' || substring(_year::text from 3 for 2)
           || 'm' || lpad(_month::text, 2, '0');
  start_date := DATE (_year::text || '-' || _month::text || '-01');
  IF ( _month = 12 ) THEN
       end_date := DATE (next_year || '-01-01');
  ELSE
       end_date := DATE (_year::text || '-' || next_month || '-01');
  END IF;

  RAISE NOTICE 'Creating child table %', tname;
  EXECUTE format('CREATE TABLE %I ( CHECK ( end_time >= %L AND end_time < %L))
                  INHERITS (parent_table)', tname, start_date, end_date);
  -- EXECUTE format('CREATE TABLE %I ( CHECK ( end_time >= $1 AND end_time < $2))
  --                 INHERITS (parent_table)', tname)
  -- USING start_date, end_date;
  RETURN tname;
END
$$ LANGUAGE plpgsql;

When I call it, it is successful:

# select mk_child(2015,1);                                           
NOTICE:  Creating child table child_y15m01
   mk_child   
--------------
 child_y15m01
(1 row)

However, if I use the EXECUTE ... USING ...; form (commented out in the snippet above), I get an error:

# select mk_child(2015,2);
NOTICE:  Creating child table child_y15m02
ERROR:  there is no parameter $1
CONTEXT:  SQL statement "CREATE TABLE child_y15m02 ( CHECK ( end_time >= $1 AND end_time < $2 ) ) INHERITS (parent_table)"
PL/pgSQL function mk_child(integer,integer) line 21 at EXECUTE statement

The PostgreSQL docs say explicitly that this format works and is more efficient.

So why does it not work as expected?

Answer 1

You can only pass values to DML statements. 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.

So CREATE TABLE statement does not accept parameters via USING clause, even if expressions in a CHECK constraint look like values that might be parameterized.

That aside, you can largely simplify your function:

CREATE OR REPLACE FUNCTION mk_child(_year int, _month int)
  RETURNS text AS
$func$
DECLARE
   start_date date := to_date(_year::text || _month::text, 'YYYYMM');
   tname text := to_char(start_date , '"child_y"YY"m"MM');
BEGIN
   RAISE NOTICE 'Creating child table %', tname;
   EXECUTE format('
      CREATE TABLE %I (CHECK (end_time >= %L AND end_time < %L))
      INHERITS (parent_table)'
    , tname, start_date, (start_date + interval '1 month')::date);
   RETURN tname;
END
$func$ LANGUAGE plpgsql;

Related:

• Self-managing PostgreSQL partition tables

If this is about table partitioning, look to the new declarative table partitioning in Postgres 10 or later. Related answer with sample code and links:

• Better database for "keep always the 5 latest entries per ID and delete older"?