2

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;

closed as off-topic by dezso, Colin 't Hart, RLF, RolandoMySQLDBA, Michael Green Sep 22 '15 at 10:35

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, Colin 't Hart, RLF, RolandoMySQLDBA, Michael Green
If this question can be reworded to fit the rules in the help center, please edit the question.

  • If any operand is null then || will yield null as well. So, one of your arguments is null. – a_horse_with_no_name Sep 21 '15 at 12:53
  • 1
    Your problem is that lead() returns a NULL as the 10th value in the series and that renders the whole text NULL. This can be easily found out if you systematically do what @a_horse_with_no_name suggests above. This also means that you have only 9 statements logged and a NULL instead of the last one. – dezso Sep 21 '15 at 12:53
1

@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.

  • 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:

  • 1
    Did you mean YYYY-MM-DD, or the version with the underscores? – dezso Sep 21 '15 at 14:22
  • Thank you. ☺A lot of the nasty-ness was just to get something reproducible and rip it out of a larger procedure. – Blootac Sep 21 '15 at 14:23
  • @dezso: Yes, thanks. I fixed my bug in the date format in the CHECK constraint and simplified a bit more. – Erwin Brandstetter Sep 21 '15 at 14:28

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