Creating VIEW using EXECUTE ignores USING clause parameter?

Question

I am trying to create a UDF that creates a VIEW dynamically using the USING clause feature. The problem is, EXECUTE will not accept the passed parameter into the CREATE VIEW command.

CREATE OR replace FUNCTION test(name varchar) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
 EXECUTE format('create or replace view %I as select 1ドル as id limit 1;',1ドル) using 1ドル;
 RETURN;
END
$$;

Test:

select test('tabel_view');

Current output:

SQL Error [42P02]: ERROR: there is no parameter 1ドル
Where: PL/pgSQL function test(character varying) line 3 at EXECUTE

The same works for CREATE TABLE:

CREATE OR REPLACE FUNCTION test(name varchar) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
 EXECUTE format('create table %I as select 1ドル as id limit 1;',1ドル) using 1ドル;
 RETURN;
END
$$;

I am trying this way because string concatenation is prone to SQL injection. Also, the UDF that I am working on will have an array as input.

Any idea how to resolve this issue?

Answer 1

The USING clause of EXECUTE is for passing values to DML commands ("utility commands"). The manual:

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

Does not work with DDL commands like CREATE VIEW. The SELECT statement in CRATE TABLE is different in that it is treated (and executed) as actual nested SELECT statement, where parameter interpolation is implemented. It falls under "certain commands containing one of these".

Concatenate the value as string literal instead:

CREATE OR REPLACE PROCEDURE test(name text)
 LANGUAGE plpgsql AS
$proc$
BEGIN
 EXECUTE format('CREATE OR REPLACE VIEW %1$I AS SELECT %1$L AS id;', 1ドル);
END
$proc$;
CALL test('My View'); -- example with evil name

fiddle

The string literal defaults to type text. Add an explicit type cast if you need a different type - like for an array type that you mentioned.

...
EXECUTE format('CREATE VIEW %1$I AS SELECT %1$L::text[] AS id;', 1ドル);
...

• Zaki: Thank you so much for the workaround and insight ! I didn't know it falls under the "certain commands containing one of these", since table creation works perfectly.
• Erwin Brandstetter: @Zaki: To be clear, CREATE TABLE is the one that falls under "certain commands containing one of these", not CREATE VIEW.