Re: permanent setting of config variables

Hi,

On Thu, Feb 18, 2021 at 4:05 PM Joe Conway <mail(at)joeconway(dot)com> wrote:

> 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)
>
>
I have just tried your example and it worked just fine.

I guess I was doing something wrong on my previous attempts.

And also, from your example, it also means that this can be achieved by the
client application, which is also cool, and better for me than the server
side approach

Thank you for the clear example
Joao

> 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
>
>