Syntax error at or near ""20231002"" in procedure with dynamic table name

Question

I want to create procedure which can create a table with dynamic table name. It takes a date, formats and appends it to the table name.

What I did:

CREATE OR REPLACE PROCEDURE l2.accounts_balances_load(var_balancedate date)
 LANGUAGE plpgsql
AS $procedure$
declare var_balancedate_ int = to_char(var_balancedate, 'YYYYMMDD') ;  
begin

raise notice 'SQL:: %', var_balancedate_;

execute format ('create table if not exists l2.accounts_balances_%I partition of l2.accounts_balances', var_balancedate_);

end;
$procedure$
;   

I call with:

call l2.accounts_balances_load('2023-10-02');

But I get error:

SQL Error [42601]: ERROR: syntax error at or near ""20231002""
Где: PL/pgSQL function l2.accounts_balances_load(date) line 9 at EXECUTE

What am I doing wrong?

Answer 1

You need the format specifier %s instead of %I for format() in this case. Like:

CREATE OR REPLACE PROCEDURE l2.accounts_balances_load(var_balancedate date)
  LANGUAGE plpgsql AS
$proc$
DECLARE
   var_balancedate_ text := to_char(var_balancedate, 'YYYYMMDD');
BEGIN
   RAISE NOTICE 'SQL:: %', var_balancedate_;

   EXECUTE format(
      'CREATE TABLE IF NOT EXISTS l2.accounts_balances_%s
       PARTITION OF l2.accounts_balances', var_balancedate_);
END
$proc$;

Also, no point in coercing the formatted date in var_balancedate_ to int and back. I made it text.

The specifier %I treats the input as identifier and double-quotes the string if it wouldn't be valid otherwise. A string of digits gets double-quoted. The subsequent concatenation makes an illegal name.

Now, your table name is an identifier, but you are only appending the number. You would have to double-quote the whole name. Since var_balancedate_ is assigned the result of to_char(), we know the string is safe, so no SQL injection possible, and %s is fine.
Else you would concatenate the whole table name, and pass it in with the %I specifier to be quoted as a whole.

Related:

• INSERT with dynamic table name in trigger function
• Table name as a PostgreSQL function parameter
• Define table and column names as arguments in a plpgsql function?