Invalid name syntax on to_regrole(session_user)
Hello!
I store pg_roles.oid::int as numeric representation of database role.
Now I need to get this number for session user.
I used to_regrole(session_user) function.
Everything works fine till I allow roles with dots in role`s name.
create role "dot.net"
set session authorization 'dot.net';
select to_regrole(session_user); ERROR: invalid name syntax
Is it expected behavior that session_user produces non quoted output and I should use quote_ident anywhere in code session_user?
Is there other way (without extra type conversions) for getting oid of session_user?
Import Notes
Reply to msg id not found: 158686573592.9421.7178775688662332959@malur.postgresql.orgReference msg id not found: 158686573592.9421.7178775688662332959@malur.postgresql.org
=?UTF-8?B?0KY=?= <pfunk@mail.ru> writes:
create role "dot.net"
set session authorization 'dot.net';
select to_regrole(session_user); ERROR: invalid name syntax
Well, we've got this:
regression=> select session_user;
session_user
--------------
dot.net
(1 row)
which is reasonable: that output shouldn't be quoted.
But then:
regression=> select 'dot.net'::regrole;
ERROR: invalid name syntax
LINE 1: select 'dot.net'::regrole;
^
which is also reasonable. So you need to provide the quoting if
you want to interface these.
regression=> select to_regrole(quote_ident(session_user));
to_regrole
------------
"dot.net"
(1 row)
Is there other way (without extra type conversions) for getting oid of session_user?
select oid from pg_roles where rolname = session_user
regards, tom lane
Thank you for clarification!
PS: Re-reading initial message, I feel ashamed for such foolish questions.
Show quoted text
Вторник, 14 апреля 2020, 17:04 +03:00 от Tom Lane <tgl@sss.pgh.pa.us>:
=?UTF-8?B?0KY=?= < pfunk@mail.ru > writes:
create role "dot.net"
set session authorization 'dot.net';
select to_regrole(session_user); ERROR: invalid name syntaxWell, we've got this:
regression=> select session_user;
session_user
--------------
dot.net
(1 row)which is reasonable: that output shouldn't be quoted.
But then:regression=> select 'dot.net'::regrole;
ERROR: invalid name syntax
LINE 1: select 'dot.net'::regrole;
^which is also reasonable. So you need to provide the quoting if
you want to interface these.regression=> select to_regrole(quote_ident(session_user));
to_regrole
------------
"dot.net"
(1 row)Is there other way (without extra type conversions) for getting oid of session_user?
select oid from pg_roles where rolname = session_user
regards, tom lane