Обсуждение: psql variables in the DO command
Hello, I can't use psql variable in the DO command. Is it intentional behavior? postgres=# \set var 'Hello, World!' postgres=# do $$begin raise notice '%', :'var'; end;$$; ERROR: syntax error at or near ":" LINE 1: do $$begin raise notice '%', :'var'; end;$$; ^ -- ----- Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Hi
2018-03-05 14:13 GMT+01:00 Pavel Luzanov <p.luzanov@postgrespro.ru>:
Hello,
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.
you can copy psql variables to GUC variables by set_config function, and then on server side use current_setting function for getting the content.
Regards
Pavel
postgres=# \set var 'Hello, World!'
postgres=# do $$begin raise notice '%', :'var'; end;$$;
ERROR: syntax error at or near ":"
LINE 1: do $$begin raise notice '%', :'var'; end;$$;
^
--
-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
On 05.03.2018 16:42, Pavel Stehule wrote:
But SELECT command also executed on a server side ))
I thought that the command is sent to the server after variable's replacement.
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.
Yes, I know about workarounds.you can copy psql variables to GUC variables by set_config function, and then on server side use current_setting function for getting the content.
----- Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
2018-03-05 14:52 GMT+01:00 Pavel Luzanov <p.luzanov@postgrespro.ru>:
On 05.03.2018 16:42, 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.
postgres=# \set xxx ahoj
postgres=# select ':xxx';
+----------+
| ?column? |
+----------+
| :xxx |
+----------+
(1 row)
postgres=# \set xxx ahoj
postgres=# select ':xxx';
+----------+
| ?column? |
+----------+
| :xxx |
+----------+
(1 row)
Regards
Pavel
Yes, I know about workarounds.you can copy psql variables to GUC variables by set_config function, and then on server side use current_setting function for getting the content.----- Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
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.
----- Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Another possible, but inconvenient workaround - constructing the right string before execution:
postgres=# \set var 'Hello, World!'
postgres=# \set cmd '$$begin raise notice ''%'', ' :'var' '; end;$$;'
postgres=# do :cmd;
NOTICE: Hello, World!
DO
postgres=# \set var 'Hello, World!'
postgres=# \set cmd '$$begin raise notice ''%'', ' :'var' '; end;$$;'
postgres=# do :cmd;
NOTICE: Hello, World!
DO
----- Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
On 05.03.2018 17:02, Pavel Luzanov wrote:
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.----- Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
2018-03-05 15:02 GMT+01:00 Pavel Luzanov <p.luzanov@postgrespro.ru>:
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
On 05.03.2018 18:01, Pavel Stehule wrote:
It will be great. I already commented it in your blog.
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.
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 $$BEGINRAISE NOTICE '%', var;END;
$$;
It will be great. I already commented it in your blog.
----- Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
2018-03-05 16:19 GMT+01:00 Pavel Luzanov <p.luzanov@postgrespro.ru>:
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 necessaryUnfortunately 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 $$BEGINRAISE 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
On 05.03.2018 18:35, Pavel Stehule wrote:
I'm not a big expert on postgres internals, but ready to participate.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.
----- Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
2018-03-06 10:17 GMT+01:00 Pavel Luzanov <p.luzanov@postgrespro.ru>:
On 05.03.2018 18:35, Pavel Stehule wrote:I'm not a big expert on postgres internals, but ready to participate.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.
big thanks. I'll start new thread to stop do offtopic in this place.
Thank you
Pavel
----- Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company