david.g.johnston@xxxxxxxxx wrote:bryn@xxxxxxxxxxxx wrote: I was far, far too elliptical in what I wrote. Sorry, David. Yes, that was a mistake. These are the two fundamental observations that got me started on this: Observation #1 (the native content of "pg_roles"): select rolname as role_name from pg_roles order by 1; --------------------------- Bllewell pg_database_owner ... postgres Observation #2 (the native content of "pg_proc"): select proowner::regrole as role_name, proname from pg_proc order by 1, 2 limit 3; role_name | proname ------------+------------------------ "Bllewell" | RI_FKey_cascade_del "Bllewell" | RI_FKey_cascade_upd "Bllewell" | RI_FKey_check_ins The clue to the different spellings is the ::regrole typecast. I knew all along that I could get the name of the owner of a function from: pg_proc p inner join pg_roles r on p.proowner = r.oid But I wondered if I could find an operator (using the word loosely) to save me some typing. Google took me to the "Postgres get function owner" post on Stack Exchange: https://dba.stackexchange.com/questions/259001/postgres-get-function-ownerLaurenz Albe answered the question thus: « The complicated way is to join with pg_roles and get the user name from there. But since PostgreSQL hackers don't want to type more than necessary, they came up with something else... Internally, every object is... identified.. by its “object ID”. This is the number that is for example used in the proowner column of pg_proc... but the type output function, which is used for display of the type, renders it as the object's name. Put that together with PostgreSQL's type cast operator ::, and you can do... ::regrole. » This was exactly what I wanted! I took Lawrence's « ::regrole renders proowner as the [owner]'s name » at face value. But when the name happens to be exotic, as is Joe, you see that ::regrole in fact renders proowner as the *identifier* for the function's owner's name. The testcase at the end makes the point more explicitly. It does some set-up that lets me do this: select r.rolname as "The owner name",p.proowner::regrole as "The *identifier* for the owner name" from pg_roles r inner join pg_proc p on r.oid = p.proowner where p.proname = 'something_strange'; The owner name | The *identifier* for the owner name ----------------+------------------------------------- Joe | "Joe" This is what I've been banging on about all the time. It seems that I'm the only person in the pgsql-general community who wants some proper terms of art (as I used them in my column aliases) that let me say simply and clearly, why pg_proc.proowner::regrole gives a differently spelled answer than does pg_roles.role_name. B.t.w., I looked at "8.19. Object Identifier Types". It uses the term "identifier" in the more ordinary sense of "unique identifier" (as are the values in a table's PK column that might be numeric, text, uuid, or whatever. It mentions quote_ident(). I tried it thus: select quote_ident('Joe') as joe, quote_ident('"Dog"') as dog; joe | dog -------+----------- "Joe" | """Dog""" (so that's right). And it goes on to say "...names that require quoting". So the difference is in the air and is hinted at with "ident" and "name". But the wording doesn't tie things down. I'll shut up on this now. ---------------------------------------------------------------------- -- Testcase setup \c postgres postgres create role "Joe" login password 'p'; create database tmp; grant connect on database tmp to "Joe"; grant create on database tmp to "Joe"; \c tmp "Joe" create schema s; create procedure s.something_strange() language plpgsql as $body$ begin null; end; |
