Re: permanent setting of config variables

On 2/18/21 4:09 AM, Joao Miguel Ferreira wrote:
> I have a few PL/pgSQL functions that use queires like "SHOW company.product INTO
> _product_name" and "SHOW company.cluster INTO _cluster_number".
>
> But these are failing because I don't know how to set those values on a
> permanent basis, or maybe on startup would also be ok.
>
> I did read the "ALTER DATABASE " and the "set_config(...)" documentation and I
> experimented a bit with that but without succes.
>
> So my question would be: how to permanently set user specific config values that
> would become accessible to the "SHOW ...." SQL comand.

Perhaps I don't understand your issue, but this works for me:

8<---------------
nmx=# alter database nmx set a.b = 'c';
ALTER DATABASE

nmx=# \q

psql nmx
psql (12.5)
Type "help" for help.

nmx=# show a.b;
a.b
-----
c
(1 row)

CREATE OR REPLACE FUNCTION test() RETURNS text AS $$
DECLARE
pn text;
BEGIN
SHOW a.b INTO pn;
RETURN pn;
END;
$$ LANGUAGE plpgsql;

nmx=# SELECT test();
test
------
c
(1 row)

nmx=# \q

psql nmx
psql (12.5)
Type "help" for help.

nmx=# SELECT test();
test
------
c
(1 row)
8<---------------

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development