How can use custom_variable_class

Question

i am create custom_variable_class= myapp in postgresql.conf. And set the value is inside of function like

CREATE OR REPLACE FUNCTION fn_purchase(xmode text, xuserno integer)
  RETURNS text AS

set myapp.user_no=xuserno
..........
..........

END;

Now i am using myapp.user in my trigger

CREATE OR REPLACE FUNCTION public.delete_history()
  RETURNS trigger AS

$BODY$
    DECLARE userno text;
    BEGIN

    SELECT current_setting('myapp.user_no') into userno;
END;
$BODY$

If set userno is integer then it show error message.

invalid input syntax for integer: "xuserno".

and SELECT current_setting('myapp.user_no') is show xuserno not xuserno value. That means xuserno=5 it show xuserno not 5. I am doing any thing wrong?

Try PERFORM set_config('myapp.user_no', xuserno::text, 'f'::boolean) instead of SET. — Craig Ringer
– Craig Ringer, Commented Apr 2, 2013 at 11:49
It show same result. so i try to change the name like myapp.user-no1 that time it show error unrecognized configuration parameter myapp.user_no. what i am doing wrong — Sathish
– Sathish, Commented Apr 2, 2013 at 12:21
Since you haven't provided the complete and unmodified code you're running I really cannot help you any more. Provide a self-contained, complete and unmodified example that shows the problem exactly as you run it on your computer. (....... is not valid PL/PgSQL). Or just use the TEMPORARY TABLE approach. — Craig Ringer
– Craig Ringer, Commented Apr 2, 2013 at 13:48
@CraigRinger You probably do, but you realise 'f'::boolean can be written false? Seems a lot more readable... — IMSoP
– IMSoP, Commented Apr 2, 2013 at 22:42
@IMSoP Huh, nope; I didn't realise that'd been added to the syntax at some point. Sometimes using a package for a long time has downsides... — Craig Ringer
– Craig Ringer, Commented Apr 2, 2013 at 23:20

Chris Travers · Accepted Answer · 2013-05-14 02:05:03Z

2

Your problem is a very subtle one:

According to the docs instead of:

 custom_variable_class= myapp

You should have

custom_variable_classes='myapp'

Note that custom_variable_classes is intended to be used to set multiple classes for multiple addons.

From there, with the comments above, this should work.

answered May 14, 2013 at 2:05

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

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

Collectives™ on Stack Overflow

How can use custom_variable_class

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