2018-03-05 15:02 GMT+01:00 Pavel Luzanov <p.luzanov@xxxxxxxxxxxxxx>:
On 05.03.2018 16:56, Pavel Stehule wrote:
Yes, now I understand this. But at first glance this is not an obvious behavior.
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.
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
