3

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?

  • You can check good answer - stackoverflow.com/questions/19302098/… – a_vlad Nov 18 '15 at 14:15
  • 2
    @dezso: The problem is that you cannot parameterize the values of a CHECK constraint, since that is DDL code, even if it looks like a DML statement that can work with USING. This is a good question with a sneaky problem that should not have been closed. I am addressing you since you are the only Postgres native in lynch mob - the rest seems to have followed your lead. – Erwin Brandstetter Nov 19 '15 at 4:36
  • 1
    @Erwin reopened :) – Jack Douglas Nov 19 '15 at 7:53
  • @ErwinBrandstetter yes, you are right - sorry for the confusion. – dezso Nov 19 '15 at 8:37
2

You can only pass values to DML statements. The manuals explicates:

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:

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:

  • Perhaps the error message could have been a little more informative. Thank you for your help and simplifications. – donatello Nov 19 '15 at 9:46
  • @donatello: Consider the added minor improvements. – Erwin Brandstetter Nov 19 '15 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.