Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using psql variables in DO-blocks

Using psql variables in DO-blocks

From
Andreas Joseph Krogh
Date:
Hi all.
 
I'm trying to use a psql variable in a DO-block, but it fails:
 
\set resource_group 'Ressurser'
\set quoted_resource_group '\'' :resource_group '\''

DO $$
begin
    if not exists(SELECT * FROM tbl_group WHERE groupname = :quoted_resource_group) then
        raise notice 'Group % not found, creating it.', :quoted_resource_group;    end if;
end;
$$;
 
ERROR:  syntax error at or near ":"
LINE 3: ... exists(SELECT * FROM tbl_group WHERE groupname = :quoted_re...
                                                           ^
 
Any hints?
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment

Re: Using psql variables in DO-blocks

From
"David G. Johnston"
Date:
On Tue, Jan 15, 2019 at 8:48 AM Andreas Joseph Krogh <andreas@visena.com> wrote:
Hi all.
 
I'm trying to use a psql variable in a DO-block, but it fails:
 [...]
Any hints?

Don't do that.  The body of a DO block is a string literal and psql won't and shouldn't mess with its contents.  You'll need to use an actual function and pass in the psql variable data via an input parameter.

set_config(...)/current_setting(...) is another option to consider.

David J.

Attachment

Sv: Using psql variables in DO-blocks

From
Andreas Joseph Krogh
Date:
På tirsdag 15. januar 2019 kl. 16:51:09, skrev Andreas Joseph Krogh <andreas@visena.com>:
Hi all.
 
I'm trying to use a psql variable in a DO-block, but it fails:
 
[snip]
 
Seems I was a bit lazy, here's what works:
 
\set resource_group 'Ressurser'
\set quoted_resource_group '\'' :resource_group '\''

set myvars.quoted_resource_group to :quoted_resource_group;

DO $$
begin
    if not exists(SELECT * FROM onp_group WHERE groupname = current_setting('myvars.quoted_resource_group')) then
        raise notice 'Group % not found, creating it.', current_setting('myvars.quoted_resource_group');    end if;
end;
$$;
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment