Search Postgresql Archives

Re: Seems to be impossible to set a NULL search_path

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



david.g.johnston@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

  role_name  
-------------
 Bllewell
 ...
 "Bllewell"

...Are there really two distinct roles with those two names?...

Is this another one of your mistakes in presenting a self-contained test case?

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;

         role_name         
---------------------------
 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-owner

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:

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;


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux