Passing parameters for dynamic SQL in a function

Question

I am trying to create a function which will dynamically create a unique table name, populate the table with a result set returned from another function, and return the table name to the caller. In outline, it looks something like this:

CREATE OR REPLACE FUNCTION return_result_table(
    _param1 integer[],
    _param2 text[])
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
DECLARE
    _table_name TEXT;
    _select TEXT;
BEGIN
    _table_name := '_XYZABC_' || replace(current_date::text,'-','_') || '_' || 'ZXCVBN'; -- not the real code
    _select := '(select * from some_other_function(_param1, _param2))';
    execute 'create table some_schema.' || _table_name || ' as ' || _select;
    return _table_name; 
END;
$BODY$;

The code is accepted, and it appears to be forming the command correctly as

execute 'create table some_schema._XYZABC_2020_10_07_ZXCVBN as (select * from some_other_function(_param1, _param2))'

but when I try to execute it with:

SELECT return_result_table(ARRAY[0,1,5,19],ARRAY['AA,'BB','CC'])

I get the error

ERROR: column "_param1" does not exist

LINE 1: ...ect * from return_result_table(_param1...

It appears that the code is trying to substitute in the value of a column (of what?) rather than using the parameter.

How can I prevent this translation happening, so that I can pass the parameters through into the called function?

Answer 1

To make it work dynamically, do something like this:

CREATE OR REPLACE FUNCTION return_result_table(_param1 integer[], _param2 text[])
  RETURNS text
  LANGUAGE plpgsql AS
$func$
DECLARE
   _table_name text := to_char(current_date, '"_XYZABC_"YYYY_MM_DD"_ZXCVBN"');  -- equivalent, but still not the real code
   _select     text := $$(select * from some_other_function($1, $2))$$;  -- !
BEGIN
    EXECUTE format('CREATE TABLE some_schema.%I AS ', _table_name)
         || _select
    USING _param1, _param2;
   
    RETURN _table_name; 
END
$func$;

db<>fiddle here

This passes parameters as values to EXECUTE with the USING clause, which is cheapest and safest.

$N ($1 and $2) in the string passed to EXECUTE refer to appended arguments, not to function parameters like it would in SQL code outside of EXECUTE. (Ordinal numbers are in sync only by chance.)

Note this restriction:

Another restriction on parameter symbols is that they only work in SELECT, INSERT, UPDATE, and DELETE commands. In other statement types (generically called utility statements), you must insert values textually even if they are just data values.

The manual is a bit fuzzy there. The above command CREATE TABLE AS is just such a utility statement, but parameter passing still work because the SELECT statement is evaluated on its own before results are passed to CREATE TABLE AS.

format() is instrumental to make it simpler and safer, too.

The used format specifier %I preserves the name as given (case sensitive) adding double quotes as needed automatically. (No SQL injection possible.)

Note that I only use nested dollar-quoting to fix the currently broken syntax highlighting of the site. Else, it can just be:

  _select     text := '(select * from some_other_function($1, $2))';  -- !

There are quite a few related answers with more details here on dba.SE. Try a search for [postgres] [dynamic-sql] format code:EXECUTE code:USING

Prominently:

• How to use function parameters in dynamic SQL with EXECUTE?

Also:

• Table name as a PostgreSQL function parameter