permanent setting of config variables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

permanent setting of config variables

Joao Miguel Ferreira-3
Hi,

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.

Thank you
Joao

Reply | Threaded
Open this post in threaded view
|

Re: permanent setting of config variables

Laurenz Albe
On Thu, 2021-02-18 at 09:09 +0000, 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.

_product_name := current_setting('company.product');

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: permanent setting of config variables

Joao Miguel Ferreira-3
Hi,


On Thu, Feb 18, 2021 at 10:52 AM Laurenz Albe <[hidden email]> wrote:
On Thu, 2021-02-18 at 09:09 +0000, 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.

_product_name := current_setting('company.product');


I can not change the code in those functions. I need to make them work as they are.

So I guess I need to set those variables from an administration action rather than from a coding action.

Something like "ALTER SYSTEM SET company.procut TO 'SpaceCraft';" but this returns an error saying "ERROR:  unrecognized configuration parameter "company.procut". If I'm not  mistaken, the "SET ...." command lasts only for the duration of a client session. I need it to be permanent.
 
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Reply | Threaded
Open this post in threaded view
|

Re: permanent setting of config variables

Laurenz Albe
On Thu, 2021-02-18 at 11:42 +0000, Joao Miguel Ferreira wrote:

> On Thu, Feb 18, 2021 at 10:52 AM Laurenz Albe <[hidden email]> wrote:
> > On Thu, 2021-02-18 at 09:09 +0000, 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.
> >
> > _product_name := current_setting('company.product');
> >
>
> I can not change the code in those functions. I need to make them work as they are.

That makes no sense.  You say that the statements in your functions are failing.
Then you *have* to change the code.

> So I guess I need to set those variables from an administration action rather than from a coding action.

Your question was about SHOW, not SET, so I have lost you here.

> Something like "ALTER SYSTEM SET company.procut TO 'SpaceCraft';" but this returns
>  an error saying "ERROR:  unrecognized configuration parameter "company.procut".
>  If I'm not  mistaken, the "SET ...." command lasts only for the duration of a
>  client session. I need it to be permanent.

These parameters do not exist, so you cannot set them permanently.
That's the way it is.

To turn them into parameters, you would have to write a small extension in C
and load it into the server.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: permanent setting of config variables

Joe Conway
In reply to this post by Joao Miguel Ferreira-3
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


signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: permanent setting of config variables

Joao Miguel Ferreira-3
Hi,

On Thu, Feb 18, 2021 at 4:05 PM Joe Conway <[hidden email]> 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