Re: R: Re: R: Re: Weird EXECUTE ... USING behaviour

2010/1/14 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
> 2010/1/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> 2010/1/14 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
>>> 2010/1/14 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>:
>>>> 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