Question Details

No question body available.

Tags

sql postgresql plpgsql postgresql-15

Answers (3)

Accepted Answer Available
Accepted Answer
July 23, 2025 Score: 2 Rep: 258,914 Quality: High Completeness: 50%

Here is a solution that works with psql:

\set bucketdatatype date
SELECT 'CREATE OR REPLACE FUNCTION insert(
    ids BIGINT[],
    types TEXT[],
    buckets ' || :'bucketdatatype' || '[]
) RETURNS VOID
LANGUAGE PLPGSQL
AS $$
BEGIN
   EXECUTE 
    FORMAT(''INSERT INTO myTable(id, type, buckets)
    SELECT .id, .type, .bucket
    FROM(
        SELECT unnest(%L::bigint[]) AS monitorid,
               unnest(%L::text[]) AS featuretype,
               unnest(%L::'|| :'bucketdatatype' ||'[]) AS bucket
        ) 
        ON CONFLICT DO NOTHING;'',
           ids, types, buckets);
END
$$' \gexec

The SELECT statement composes the statement that creates the function, and \gexec executes the statement.

July 23, 2025 Score: 2 Rep: 258,914 Quality: Medium Completeness: 80%

If you nest code to create in code, you have to be careful with your quoting.

One solution would be a procedure like this:

CREATE OR REPLACE PROCEDURE mkfun(bucketdatatype text)
   LANGUAGE plpgsql AS
$mkfun$
BEGIN
   EXECUTE format(
              $code$CREATE FUNCTION %1$I(
    ids BIGINT[],
    types TEXT[],
    buckets %2$I[]
) RETURNS VOID
LANGUAGE PLPGSQL
AS $$
BEGIN
   EXECUTE 
    FORMAT('INSERT INTO myTable(id, type, buckets)
    SELECT .id, .type, .bucket
    FROM(
        SELECT unnest(%%L::bigint[]) AS monitorid,
               unnest(%%L::text[]) AS featuretype,
               unnest(%%L::%2$I[]) AS bucket
        ) 
        ON CONFLICT DO NOTHING;',
           ids, types, buckets);
END
$$;$code$,
              'insert' || bucketdatatype,
              bucketdata_type
           );
END;
$mkfun$;

Some hints at what I did there:

  • I use three levels of “dollar quoting”: $mkfun$ for my procedure body, $code$ for the function body template and $$ for the body of the function to be generated. With dollar quoting, I can escape the madness of doubling and quadrupling single quotes.

  • %1$I gets replaced with the first argument after the format, quoted as an identifier.

  • %% gets replaced with %, so I can use it to escape % in the nested format() call.

To create a function for date, I can simply execute

CALL mkfun('date');
July 23, 2025 Score: -2 Rep: 80,403 Quality: Medium Completeness: 80%

If you are using psql and variable substitution then you need to embed the variable directly into your query. Also : needs to be escaped using \: so :: becomes \:\:.

CREATE OR REPLACE FUNCTION insert(
    ids BIGINT[],
    types TEXT[],
    buckets :bucketdatatype []
) RETURNS VOID
LANGUAGE PLPGSQL
AS $$
BEGIN
   EXECUTE 
    FORMAT('INSERT INTO myTable(id, type, buckets)
    SELECT .id, .type, .bucket
    FROM(
        SELECT unnest(%L\:\:bigint[]) AS monitorid,
               unnest(%L\:\:text[]) AS featuretype,
               unnest(%L\:\: :bucketdatatype []) AS bucket
        ) 
        ON CONFLICT DO NOTHING;',
           ids, types, buckets);
END
$$;

It's not clear why you are using FORMAT in the function anyway, that's certainly not the best way to execute statements with parameters. You can just do a plain INSERT in this case, in normal SQL.

CREATE OR REPLACE FUNCTION insert(
    ids BIGINT[],
    types TEXT[],
    buckets :bucketdatatype []
) RETURNS VOID
LANGUAGE SQL
AS $$
BEGIN
   INSERT INTO myTable(id, type, buckets)
   SELECT unnest(ids) AS monitorid,
          unnest(types) AS featuretype,
          unnest(buckets) AS bucket
   ON CONFLICT DO NOTHING;
END
$$;