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