Error creating a session variable in Postgres

Question

I am creating a procedure where it receives two parameters: p_user and p_id.

create or replace procedure user (p_user numeric, p_ip varchar(20))
as $$
begin 
    set session.id_user= p_user;
    set session.ip = p_ip;
end;
$$ 
language 'plpgsql';

I call the created procedure:

call user (1, '127.0.0.1');

I perform the verification query:

select current_setting('session.id_user'), current_setting('session.ip');

And I get as a result:

current_setting: p_user
current_setting: p_ip

And not the values reported in the parameters.

pifor · Accepted Answer · 2020-06-14 07:42:27Z

1

You can use FORMAT function for this:

create or replace procedure myproc (p_user numeric, p_ip varchar(20))
as $$
begin 
    execute format('set session.id_user=%s', p_user) ;
    execute format('set session.ip=%L', p_ip) ;
end;
$$ 
language 'plpgsql';
CREATE PROCEDURE

call myproc(1, '127.0.0.1');
CALL

select current_setting('session.id_user'), current_setting('session.ip');
 current_setting | current_setting 
-----------------+-----------------
 1               | 127.0.0.1
(1 row)

answered Jun 14, 2020 at 7:42

pifor

7,9722 gold badges11 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error creating a session variable in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related