Format interval date type as a parameter in PostgreSQL function

Question

What is the best way to parameterize interval date type in a PostgreSQL function?. I have the following function

CREATE OR REPLACE FUNCTION testing(
min_time integer
) 
RETURNS void AS
$BODY$
BEGIN
 EXECUTE format('CREATE TABLE foo AS
 SELECT
     gid,
     now() + concat(%s, ' hours')::interval as x,

  FROM foobar
  limit 3  ',min_time  );

 END;
$BODY$
LANGUAGE plpgsql;

Each time I try to execute the function I get the following error ERROR: syntax error at or near "hours"

You are not nesting strings correctly. Single quotes embedded in a string constant need to be escaped by doubling them. `concat(%s, '' hours'')::interval` - but using `make_interval()` would probably be a lot easier — , Dec 15 '21 at 09:43
Why use dynamic SQL at all? This seems pretty pointless in this case — , Dec 15 '21 at 09:44
This is just a dummy example. I have my use case of using dynamic SQL because I need to use it in different context — kartoza-geek, Dec 15 '21 at 09:51

score 0 · Accepted Answer · answered Dec 15 '21 at 09:50

Single quotes have to be escaped in a string:

CREATE OR REPLACE FUNCTION testing(min_time integer) RETURNS void
   LANGUAGE plpgsql AS
$BODY$
BEGIN
   EXECUTE format(
              'CREATE TABLE foo AS
               SELECT gid,
                      now() + ''%s hours''::interval as x
               FROM foobar
               limit 3',
              min_time
           );
END;
$BODY$;

But actually, you don't need dynamic SQL for that, and static SQL might be the better choice:

CREATE OR REPLACE FUNCTION testing(min_time integer) RETURNS void
   LANGUAGE plpgsql AS
$BODY$
BEGIN
   CREATE TABLE foo (
      gid bigint NOT NULL,
      x timestamp with time zone NOT NULL
   );

   INSERT INTO foo
   SELECT gid,
          now() + min_time * '1 hour'::interval
   FROM foobar
   limit 3;
END;
$BODY$;

Format interval date type as a parameter in PostgreSQL function

1 Answers1