Re: How to I select value of GUC that has - in its name?

On 2/9/21 6:40 AM, hubert depesz lubaczewski wrote:
> Hi,
> question from IRC, but I couldn't find an answer.
> 
> I can set custom guc with - in name, but I can't figure out how to
> select it.
> 
> Without minus, it works great:
> 
> =$ psql -X -c 'show custom.guc'
> ERROR:  unrecognized configuration parameter "custom.guc"
> 
> =$ psql -X -c "alter user depesz set custom.guc = '123'"
> ALTER ROLE
> 
> =$ psql -X -c 'show custom.guc'
>   custom.guc
> ------------
>   123
> (1 row)
> 
> If I'd try to set variable with - in name:
> 
> $ alter user depesz set custom.bad-guc = '1a';
> ERROR:  syntax error at or near "-"
> LINE 1: alter user depesz set custom.bad-guc = '1a';
>                                          ^
> $ alter user depesz set custom."bad-guc" = '1a';
> ALTER ROLE
> 
> $ select * from pg_db_role_setting where setrole = 'depesz'::regrole;
>   setdatabase │ setrole │                        setconfig
> ─────────────┼─────────┼─────────────────────────────────────────────────────────
>             0 │   16384 │ {application_name=xxx,custom.guc=123,custom.bad-guc=1a}
> (1 row)
> 
> OK. Looks like it's set. But I can't show it (this is after reconnect):
> 
> $ show custom."bad-guc";
> ERROR:  unrecognized configuration parameter "custom.bad-guc"
> 
> $ show "custom"."bad-guc";
> ERROR:  unrecognized configuration parameter "custom.bad-guc"
> 
> $ show "custom.bad-guc";
> ERROR:  unrecognized configuration parameter "custom.bad-guc"
> 
> I know I can simply not use dashes in names, but if I can *set* it, how
> can I get the value back?


The only way I found so far is:

select setconfig[array_position(setconfig, 'custom.bad-guc=1a')] from 
pg_db_role_setting where setrole = 'aklaver'::regrole;

      setconfig
-------------------
  custom.bad-guc=1a

> 
> depesz
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com