Re: Proposal: USING clause for DO statement

2009/11/21 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> Hello,
>
> I am still thinking, about using DO statement from psql console. I am
> missing any parametrisation. It could not be a problem. All pl have a
> support for parameters, we have a libpq function PQexecParams - so we
> need only some "USING" clause. I propose following syntax (and using
> from client environments)
>
> DO $$ body $$ USING expr [ ,expr [...]];
>
> body should to have a unnamed parameters in syntax related to any PL.
> I'll use plpgsql for this moment.
>
> so some the most simply sample should look like:
>
> DO $$
> BEGIN
>  RAISE NOTICE 'Hello, %', $1;
> END
> $$ USING 'World';
>
> From psql:
>
> \set message World
> DO $$BEGIN RAISE NOTICE 'Hello, %', $1; END$$ USING :message;
>
> From pgscript:
>
> SET @message = 'World';
> DO $$BEGIN RAISE NOTICE 'Hello, %', $1; END$$ USING @message;
>
> From C
>
> values[0] = "World";
> result = PQexecParams(cn, "DO $$BEGIN RAISE NOTICE 'Hello, %', $1;
> END;$$ USING $1",
>                                                 1,
>                                                  NULL, values,
>                                                  NULL, NULL,
>                                                  0);
>

last note - type of parameter is taken from a value. When value will
be NULL, then type will be unknown.

Pavel

> What do you thing about this proposal?
>
> Regards
> Pavel Stehule
>