Re: psql variables in the DO command

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

> On 05.03.2018 18:01, Pavel Stehule wrote:
>
>
> 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.
>
> But there is absence of wanted usage too.
>

How much strong and often? The parser of SQL in psql is not nice - and I
understand so nobody would to complicate syntax. Current design is SAFE and
good enough. The problem is not is a evaluation, but in DO implementation.

>
> 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
>
> Yes and Oracle sqlplus (I khow than you know this tool) has special
> command to control this: set define ...
>
>
> 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;
> $$;
>
>
> It will be great. I already commented it in your blog.
>

I am slowly working on prototype. The work is simple, when variables are
just scalars. But it is much harder, when we allow composite variables.
When prototype will be done, I invite any cooperation - there are lot of
question - and one very hard - where and how the variable should be stored
(values) - now I have workaround, but it is pretty ugly code.

CREATE VARIABLE x INT;
LET x = 10;
BEGIN;
DROP VARIABLE x;
ROLLBACK;
SELECT x; -- should be 10 .. for this situation, the PostgreSQL internal
caches are not prepared

Regards

Pavel

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