Re: R: Re: R: Re: Weird EXECUTE ... USING behaviour - Mailing list pgsql-general

2010/1/14 Pavel Stehule <pavel.stehule@gmail.com>:
> 2010/1/14 Vincenzo Romano <vincenzo.romano@notorand.it>:
>> 2010/1/14 Pavel Stehule <pavel.stehule@gmail.com>:
>>> 2010/1/14 Vincenzo Romano <vincenzo.romano@notorand.it>:
>>>> 2010/1/14 Adrian Klaver <adrian.klaver@gmail.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!
>>
>
> sorry. This is too much complicate.
>
> Why do you use SELECT?
>
> just
>
> EXECUTE 'ALTER ... SET DEFAULT ' || 42.
>
> There is other argument against USING + DDL. ALTER clause has syntax:
>
> ALTER TABLE x SET DEFAULT expr.
>
> but with USING clause you can pass only a value
>
> Pavel
>
>> --
>> Vincenzo Romano
>> NotOrAnd Information Technologies
>> NON QVIETIS MARIBVS NAVTA PERITVS
>>

This is a fairly complete background of my issue.
At the very base I'm talking about a two level dynamic SQL (I have a
function which creates functions that compose and run the dynamic
SQL), thus the usage of $l0$ (higher level) and $l2$ (lower level)
quoting.
In particular, mt (master table), "co" (constraint) and "va" (variable
arguments) are text strings at the higher level and will become SQL in
the lower level. Those data are retrieved from configuration tables
and are no way static.
While "ct" (child table) is unknown at the higher level but is defined
at the lower level as a text string to become SQL at the
EXECUTE...USING level.

1. I need to create at run-time a number of child tables
(http://www.postgresql.org/docs/8.4/static/ddl-inherit.html).
I can easily do this with something like (there's no need for the USING clause):

            execute $l2$
              create table $l2$||ct||$l2$ (
                like $l0$||mt||$l0$
                including defaults
                including constraints
                including indexes
              )
            $l2$;

2. Then I need to fill those tables up from the master:
            execute $l2$
              insert into $l2$||ct||$l2$
                select * from only $l0$||mt||$l0$
                where $l0$||co||$l0$
            $l2$ using $l0$||va||$l0$;

here "co" (constraint) and "va" (variable arguments) are text
variables taken from configuration tables.
What I have is something like:
co := 'recorddate>=$1 and recorddate<$2 and afield=$3'
va := 'rec.d0,rec.d1,rec.afield'
This also works as it is DML and I expect (and can actually see) the
$1,$2 and $3 *values* replaced into the string *before* it is sent to
the execution.

3. Then I need to add the TABLE-level CHECK condition in order to
exploit the "constraint_exclusion = on":
            execute $l2$
              alter table $l2$||ct||$l2$
                add check(  $l0$||co||$l0$ )
            $l2$ using $l0$||va||$l0$;

This doesn't work for a number of reasons.


As you can see, there no easy way to replace the actual values taken
accordingly to "va" into the template "co" but using the USING clause.
This is the real and central knot in my problem.

So I tried to implement step #3 into a two pass process, one to just
expand the placeholders, one to execute a completely static DDL
command. Something like:

            execute $l2$
              select '$l0$||co||$l0$'
            $l2$ into textvar using $l0$||va||$l0$;

            execute $l2$
              alter table $l2$||ct||$l2$
                add check( $l2$||textvar||$l2$ )
            $l2$;
This also doesn't work as:
            execute $l2$select '$1'$l2$ into textvar using 'hello';
will not expand the $1 placeholder probably because it is within quotes.
If I skip the quotes, the select won't be able to find variables
mentioned into "co".

So, this is why I used SELECT. Unless there's a better (and working) advise.

--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS