Format interval date type as a parameter in PostgreSQL function

December 15, 2021

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"

>Solution :

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$;