Re: psql variables in the DO command

2018-03-05 15:02 GMT+01:00 Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru>:

> On 05.03.2018 16:56, Pavel Stehule wrote:
>
>
>> I can't use psql variable in the DO command. Is it intentional behavior?
>>>
>>
>> yes. psql variables living on client side, and are not accessible from
>> server side . DO command is executed on server side.
>>
>>
>> But SELECT command also executed on a server side ))
>> I thought that the command is sent to the server after variable's
>> replacement.
>>
>
> The psql variables are injected into SQL string before execution (before
> SQL string is sent to server). But this injection is disabled inside
> strings - and body of DO command is passed as string.
>
> Yes, now I understand this. But at first glance this is not an obvious
> behavior.
>

It is most correct when you thinking about it.

1. :xx is out of SQL syntax, so can by safely used. There is not risk of
unwanted usage.

2. but string literal can contain :xxx symbols and not necessary it means
so it should be usage of psql variable - so additional syntax for disabling
evaluation should be necessary

3. I understand to request to use psql variables in DO command. But you
should remember - body of DO command is string. body of any function is
string too. Some unwanted psql variable evaluation in CREATE FUNCTION can
be tragic.

Unfortunately DO command is half baked - and doesn't support parameters. I
am working on schema variables and I hope it will be a solution of this
issue:

CREATE VARIABLE var as integer;

LET var = :psqlintvar;

DO $$
BEGIN
RAISE NOTICE '%', var;
END;
$$;

> -----
> Pavel Luzanov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>