2010/1/14 Vincenzo Romano <vincenzo.romano@xxxxxxxxxxx>: > 2010/1/14 Pavel Stehule <pavel.stehule@xxxxxxxxx>: >> 2010/1/14 Vincenzo Romano <vincenzo.romano@xxxxxxxxxxx>: >>> 2010/1/14 Adrian Klaver <adrian.klaver@xxxxxxxxx>: >>>> On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: > ... >>> CREATE OR REPLACE FUNCTION f() >>> RETURNS VOID >>> LANGUAGE plpgsql >>> AS $function$ >>> DECLARE >>> cmd TEXT; >>> BEGIN >>> EXECUTE ' >>> SELECT $l0$ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 $l0$ >>> ' INTO cmd USING 42; >>> RAISE INFO '%',cmd; >>> END; >>> $function$ >>> >>> SELECT f(); >>> INFO: ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 >>> >>> The command to be executed is DML (SELECT). The substitution doesn't take place. >> >> yes. You cannot call SELECT 'ALTER ...' > > SELECT 'ALTER ...' is to select a text string into a variable! > You mean the parse will give a look into my constant string to see > whether I'm trying to build a dynamic DDL command? > This would be awesome! > > -- > Vincenzo Romano > NotOrAnd Information Technologies > NON QVIETIS MARIBVS NAVTA PERITVS > This instead works: CREATE OR REPLACE FUNCTION public.f() RETURNS void LANGUAGE plpgsql AS $function$ DECLARE cmd1 TEXT; cmd2 TEXT; cmd3 TEXT; BEGIN cmd1 := 'ALTER TABLE test ALTER COLUMN i SET DEFAULT '; EXECUTE 'SELECT $1' INTO cmd2 USING 42; cmd3 := cmd1||cmd2; RAISE INFO '%',cmd3; execute cmd3; END; $function$ The point (in my case) is that the list of expressions (not variables) after the USING is dynamic itself. I can also put 42 into a variable and use it's value after the USING. But this is a lot of extra work just because the values after the USING lexeme are not evaluated by the plpgsql and replaced. It will be the SQL engine itself. -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
