2018-03-05 16:19 GMT+01:00 Pavel Luzanov <p.luzanov@xxxxxxxxxxxxxx>:
On 05.03.2018 18:01, Pavel Stehule wrote:
But there is absence of wanted usage too.
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.
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.
Yes and Oracle sqlplus (I khow than you know this tool) has special command to control this: set define ...
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
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
