Using a script variable to generate a stored procedure in Postgresql

Question

I would like to use a script variable to set the data type, so that I can generate a few functions with different types, like so:

\set bucket_data_type DATE

Then run a script to generate this function:

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

and then again with \set bucket_data_type TIMESTAMP, for example.

It works for the parameter definitions, but not the SQL inside the FORMAT block.

I am expecting something like

\set bucket_data_type DATE

<run create script as per above>

to return

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

but I'm unable to get unnest(%L::DATE[]) AS bucket to appear.

I either get format errors like:

ERROR:  syntax error at or near ":"
LINE 15:                unnest(%L::'|| :bucket_data_type ||'[]) AS bu...

or I simply get :bucket_data_type as a string inside the execute block.

I've tried SELECT set_config('tsp.bucket_data_type', :'bucket_data_type', false); as well (as this works in DO BLOCKS where things are string literals).

Is this even possible?

Why are you using EXECUTE anyway, it seems unnecessaru. Just do a straight INSERT. And if you really do need to use EXECUTE then pass parameters properly, not using FORMAT which leavs you at risk of injection. — Charlieface
– Charlieface, Commented Jul 23 at 13:37
It's a leftover from the fact I slimmed down the code example, there's some dynamic table creation going on. But you would be correct that it's not explicitly required. — aidanmo
– aidanmo, Commented Jul 23 at 13:51
Why not use the anyarray option? See postgresql.org/docs/current/… However, the real data type is determined by the table and column definition of myTable — Frank Heikens
– Frank Heikens, Commented Jul 23 at 18:00

Laurenz Albe · Accepted Answer · 2025-07-23 22:04:56Z

2

Here is a solution that works with psql:

\set bucket_data_type date

SELECT 'CREATE OR REPLACE FUNCTION insert(
    ids BIGINT[],
    types TEXT[],
    buckets ' || :'bucket_data_type' || '[]
) RETURNS VOID
LANGUAGE PLPGSQL
AS $$
BEGIN
   EXECUTE 
    FORMAT(''INSERT INTO myTable(id, type, buckets)
    SELECT _.id, _.type, _.bucket
    FROM(
        SELECT unnest(%L::bigint[]) AS monitor_id,
               unnest(%L::text[]) AS feature_type,
               unnest(%L::'|| :'bucket_data_type' ||'[]) 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.

answered Jul 23 at 22:04

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2025-07-23 13:35:15Z

2

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(bucket_data_type 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 monitor_id,
               unnest(%%L::text[]) AS feature_type,
               unnest(%%L::%2$I[]) AS bucket
        ) _
        ON CONFLICT DO NOTHING;',
           ids, types, buckets);
END
$$;$code$,
              'insert_' || bucket_data_type,
              bucket_data_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');

answered Jul 23 at 13:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

3 Comments

aidanmo Jul 23 at 13:57

So this is the equivalent of wrapping the function in another create function, rather than using variables?

Laurenz Albe Jul 23 at 14:08

My solution is using a variable, but yes, a PL/pgSQL variable. I realize now that OP asks for a psql script, so that solution may not satisfy.

aidanmo Jul 23 at 15:02

I was hoping it would be possible with a psql script yes, so this works but doesn't fit my case unfortunately.

Charlieface · Accepted Answer · 2025-07-23 13:44:01Z

-2

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 :bucket_data_type []
) RETURNS VOID
LANGUAGE PLPGSQL
AS $$
BEGIN
   EXECUTE 
    FORMAT('INSERT INTO myTable(id, type, buckets)
    SELECT _.id, _.type, _.bucket
    FROM(
        SELECT unnest(%L\:\:bigint[]) AS monitor_id,
               unnest(%L\:\:text[]) AS feature_type,
               unnest(%L\:\: :bucket_data_type []) 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 :bucket_data_type []
) RETURNS VOID
LANGUAGE SQL
AS $$
BEGIN
   INSERT INTO myTable(id, type, buckets)
   SELECT unnest(ids) AS monitor_id,
          unnest(types) AS feature_type,
          unnest(buckets) AS bucket
   ON CONFLICT DO NOTHING;
END
$$;

answered Jul 23 at 13:44

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

4 Comments

aidanmo Jul 23 at 15:12

Thanks for your answer! The escaping doesn't seem to work the way I expect - it creates the function with the escaping characters. I need the format for some dynamic sql (table name - like FORMAT('INSERT INTO %s(id, type, buckets) (unnest statment), myTableParameter' ). Do you think this is possible with that approach? Once the format is introduced, I need to cast my variables which is where this issue arises.

Adrian Klaver Jul 23 at 17:02

1) Not seeing how this FORMAT('INSERT INTO %s(id, type, buckets) (unnest statment), myTableParameter' ). would work? I would think it would need to be FORMAT('INSERT INTO %I(id, type, buckets) (unnest statment), myTableParameter' ). 2) This buckets :bucket_data_type [] gets turned into buckets date[] in the saved function, so I don't see why you need %L::'|| :bucket_data_type ||'[]?

Pavel Stehule Jul 24 at 5:05

This reply is messy - escaping colons is nonsense. Using client side variables on server side (in procedures) is not possible. You should to use server side variables (custom GUC) instead.

Pavel Stehule Jul 25 at 3:12

cannot be solution to use a polymorphic data type? The cast to polymorphic type can be forced by assignment to aux variable of polymorphic type.

Collectives™ on Stack Overflow

Using a script variable to generate a stored procedure in Postgresql

3 Answers 3

Comments

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related