1

I'm trying to set a custom option in a stored procedure, but it is storing the variable name and not contents of the variable. 

CREATE OR REPLACE FUNCTION set_user(_user_id bigint, is_local boolean default true) returns void AS $$
BEGIN
  SET my.user_id TO _user_id;
END;
$$ LANGUAGE PLPGSQL;

select set_user(1);
select current_setting('my.user_id');
 current_setting 
-----------------
 _user_id   
(1 row)

I expect current_setting to return 1, not the string value "_user_id".

Kamil Gosciminski
  • 15,392
  • 4
  • 45
  • 65
Krut
  • 3,962
  • 2
  • 32
  • 42
  • There is already a function for this: `set_config()`: http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-SET – a_horse_with_no_name Mar 29 '16 at 19:57
  • @a_horse_with_no_name I tried `set_config()` but it only works with text – Krut Mar 29 '16 at 21:05
  • I'm rather certain that custom variables (`my.user_id`) are also stored as text. I can't find a reference for that however – a_horse_with_no_name Mar 29 '16 at 21:13
  • @a_horse_with_no_name http://www.postgresql.org/docs/current/static/config-setting.html It seems logical they would be all text in the actual config file, however I'm using custom options which seem to hold their type – Krut Mar 29 '16 at 21:19
  • `SET my.user_id TO 1;` followed by `show my.user_id;` returns a text column so I would say they do not preserve their type – a_horse_with_no_name Mar 29 '16 at 21:22

1 Answers1

1

First solution

Syntax for SET is:

SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value |'value' | DEFAULT }

where value is the new value for a given configuration_parameter.

In order to assign a value stored in _user_id variable, you need to generate a dynamic command and then EXECUTE it.

This would be the way to do that:

CREATE OR REPLACE FUNCTION set_user(_user_id bigint, is_local boolean default true) 
RETURNS void 
LANGUAGE PLPGSQL
AS $$
BEGIN
  EXECUTE 'SET my.user_id TO ' || quote_nullable(_user_id);
END;
$$;

Attaching SQL Fiddle link for testing purposes.

Note:

  • quote_nullable() function would return NULL if the input argument is null. It may not be necessary in your case.

Second solution

You could also achieve the same thing with set_config() function as @a_horse_with_no_name noted. Your function would then look like that:

CREATE OR REPLACE FUNCTION set_user(_user_id bigint, is_local boolean default true) 
RETURNS void 
LANGUAGE PLPGSQL
AS $$
BEGIN
  PERFORM set_config('my.user_id', _user_id::TEXT, false);
END;
$$;

Attaching SQL Fiddle link for testing purposes.

Note:

  • You have to explicitly cast second argument to a varchar type
  • PERFORM is used to evaluate an expression and discard the result since it's not needed
  • You could use quote_nullable() function here as well
Kamil Gosciminski
  • 15,392
  • 4
  • 45
  • 65