Re: Seems to be impossible to set a NULL search_path - Mailing list pgsql-general

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"):

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: 

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:

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

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:

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