\set command in EDB Postgresql

Question

In EDB Postgres, \set command is used to set variables, like for example \set detpno 80 and while inserting we can use :deptno instaed of actual value like

insert into dept values (:deptno, 'SALES', 'HYD');

it's working fine, but when i use it in Procedure or anonymous block its throwing error.

simple anonymous block

begin
insert into dept values (:deptno, 'SALES', 'LONDON');
end;

when i execute this block, I am getting below error

ERROR: syntax error at or near ":"
LINE 2: insert into dept1 values (:deptno, 'SALES', 'BAN');

Please help me out to use \set variable in procedures or functions.

Hi Suresh Welcome to StackOverFlow. Could you \d+ tablename for us? so we can replicate your problem. — , Jan 06 '20 at 16:07
Does this answer your question? [How to declare a variable in a PostgreSQL query](https://stackoverflow.com/questions/1490942/how-to-declare-a-variable-in-a-postgresql-query) — , Jan 06 '20 at 16:11

score 0 · Answer 1 · answered Jan 06 '20 at 17:08

The substitution of :varname with something that was previously \set is a feature of a particular database client, namely "psql" (although maybe other clients have emulated this behavior). It is not a feature of the database itself. The database doesn't see the :varname, it only sees what it was substituted with.

When you use an anonymous code block, you are by-passing the client's substitution behavior.

Perhaps you should create a function, which declares what parameters it takes in a proper way, rather than using shell-like variable interpolation.

score 0 · Answer 2 · answered Jan 06 '20 at 18:26

The psql variables cannot be used inside server side code - anonymous blocks. It is just not allowed - the substitution is blocked inside string. You can use a workaround.

set a custom guc variable (custom configuration value). The prefix 'xx' is requested.

postgres=# \set myval AHOJ
postgres=# select set_config('xx.myval', :'myval', false);
 ┌────────────┐
 │ set_config │
 ╞════════════╡
 │ AHOJ       │
 └────────────┘
(1 row)

Now you can read the value by function current_setting on server side

postgres=# do $$
begin
  raise notice '%', current_setting('xx.myval');
end;
$$;
NOTICE:  AHOJ
DO

Currently there are not any other way how to do it. This is Postgres - but EDB is very similar in this case.

\set command in EDB Postgresql

2 Answers2