Can't execute dynamic DDL, argument is NULL

Question

I'm running PostgreSQL 9.4.4.
I'm new to Postgres (used to Oracle & SQL Server) so I might be doing something really silly.

I'm trying to dynamically create partitions for a table. I've got the individual parts of the code working however I can't get the function to execute the dynamic DDL statements. If I replace the execute with an insert into a log table, the statements that are generated are valid and I can run them myself but if the function attempts to execute the following error is produced:

select part_test();
ERROR:  query string argument of EXECUTE is null
CONTEXT:  PL/pgSQL function phil_test() line 18 at EXECUTE statement

A reproducible function is as follows:

create table harry(logged_time timestamp, id integer);

create or replace function part_test () returns void as $$
declare
rec record;
begin
for REC in SELECT 'create table if not exists '||'harry'||'_'
                ||to_char(date_trunc('day', my_date), 'YYYY_MON_DD')
                ||' (check (logged_time>=DATE'''
                ||to_char(date_trunc('day', my_date), 'YYYY_MON_DD')
                ||''' and logged_time < DATE'''
                ||lead(to_char(date_trunc('day', my_date), 'YYYY_MON_DD'),1)
                  over (order by date_trunc('day', my_date))
                ||''')) inherits ('||'harry'||');' STMT
                FROM generate_series
                        ( '2015-SEP-01'::timestamp 
                        , '2015-SEP-10'::timestamp
                        , '1 day'::interval) my_date
LOOP
    execute REC.STMT;
END LOOP;
end;
$$ LANGUAGE plpgsql;

score 1 · Accepted Answer · edited May 23 '17 at 12:40

@dezso already explained the direct cause of your error. But the whole function is needlessly convoluted. Use instead:

CREATE OR REPLACE FUNCTION part_test()
  RETURNS void AS
$func$
DECLARE
   _sql text;
BEGIN
   FOR _sql IN
      SELECT format ('
         CREATE TABLE IF NOT EXISTS harry_%s (
           CHECK (logged_time >= date %L AND logged_time < date %L)
         ) INHERITS (harry)'
       , to_char(d, 'YYYY_MON_DD')  -- safe against SQL injection!
       , d::date, d::date + 1)
      FROM  generate_series (timestamp '2015-09-01'
                           , timestamp '2015-09-09'  -- 1 day before last
                           , interval  '1 day') d
   LOOP
   -- RAISE NOTICE '%', _sql;  -- to debug
      EXECUTE _sql;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Major points

Use format() to make concatenating statements simpler and safer.
No need for date_trunc(), the result of to_char() is the same.
You don't need a record variable, it's just text.
You don't need a window function, just add 1 day to the start.
Always use ISO 8601 format for date and timestamp literals. Those work irregardless of your current locale settings.
I am using default format in the CHECK constraint now. (date parameters to format() are cast to text with default format automatically.) You might consider the pattern 'YYYY_MM_DD' instead of ~~'YYYY_MON_DD'~~ for the table name as well.
Resulting table names are lower case (like they should be), because I did not double-quote.
- Are PostgreSQL column names case-sensitive?
Not sure why you compare logged_time to a date, and not a timestamp or timestamptz. There may be a problem with time zones / volatility lurking there. Consider timestamptz. Or if the column is actually a date type, don't call it "time" ...

The formatted date happens to be safe against SQL injection. Else you have to do more. Consider:

SQL injection in Postgres functions vs prepared queries

Can't execute dynamic DDL, argument is NULL

1 Answers1

Major points

Linked