Parameter concatenation in dynamic SQL

Question

I want to update a row via Pl/pgSQL in PostgreSQL but an error occurs. This is my code:

CREATE OR REPLACE FUNCTION "Surrogate_Del"(tablename text, surro uuid)
  RETURNS void AS
$BODY$DECLARE
tblname text;
surrogate uuid;
BEGIN
tblname:=tablename;
surrogate:=surro;
execute 'UPDATE'||tblname||'SET ID=NULL WHERE surrogate='||surrogate|| ;
END$BODY$
LANGUAGE plpgsql

This is the UUID that I want to update record base on it

*syntax Error »ab7«
cd32cdf0-5ab7-11e2-abda-1c4bd605a98d
                  ^*

How can I solve this problem?

you are missing spaces after update and before set: `'UPDATE '||tblname||' SET ...` — , Jan 09 '13 at 17:20
Please include again your updated code and whatever the new error message is. Your update will have solved one syntax error, and now you have another. — Andy Lester, Jan 09 '13 at 17:39
Is this intended for repeated use or a one-time operation? For ad-hoc use, consider a [`DO`](http://www.postgresql.org/docs/9.1/interactive/sql-do.html) command. — Erwin Brandstetter, Jan 10 '13 at 16:24

Clodoaldo Neto · Accepted Answer · 2013-01-09T18:01:16.157

You need escaped quotes around the parameter:

execute 'UPDATE ' || tblname || ' SET ID = NULL WHERE surrogate = ''' || surrogate || '''';

mu's suggestion is cleaner and more robust:

execute 'UPDATE ' || tblname || ' SET ID = NULL WHERE surrogate = ' || quote_literal(surrogate);

If it is a newer version of postgresql (9.0+ I guess) you can use the format function:

execute format('UPDATE %I SET ID = NULL WHERE surrogate = %L', tblname, surrogate);

%L escapes it as a SQL literal.

score 4 · Answer 2 · answered Jan 10 '13 at 16:38

The generally superior approach is to insert values with a USING clause. This avoids the run-time overhead of casting to text and back and possible hazards with quoting special characters.

You can also use your named parameters directly. No point in declaring and assigning another layer of local variables:

CREATE OR REPLACE FUNCTION surrogate_del (_tblname text, _surro uuid)
   RETURNS void AS
$BODY$
BEGIN
   EXECUTE format('UPDATE %I SET ID = NULL WHERE surrogate = $1', _tblname)
   USING _surro;
END
$BODY$ LANGUAGE plpgsql

I also converted your function name to lower case and removed the double-quotes. My personal advice is to never use CamelCase identifiers in PostgreSQL. That's a constant source of pain.

On a related note: remember to provide the table name _tblname in a case sensitive fashion. format() or quote_ident() preserve the spelling verbatim.

Parameter concatenation in dynamic SQL

2 Answers2

Linked