Thread: Foreign keys and pg_user table

Dear All,

I'm trying to create a table where the username and email can only be
inserted into the table if the username is already in pg_user. The method
I'm trying is:

CREATE user(
  usename name,
  email text,
  FOREIGN KEY usename REFERENCES (pg_user)
);

but I get told that "...pg_user is not a table."

Is there another way of doing what I want?

Many thanks

Colin

_________________________________________________________________
Stay in touch with absent friends - get MSN Messenger
http://www.msn.co.uk/messenger

On Thu, 11 Dec 2003, C G wrote:

> I'm trying to create a table where the username and email can only be
> inserted into the table if the username is already in pg_user. The method
> I'm trying is:
>
> CREATE user(
>   usename name,
>   email text,
>   FOREIGN KEY usename REFERENCES (pg_user)
> );
>
> but I get told that "...pg_user is not a table."

Which is true, it's a view.  However, even were it a table,
it's a system table and references to them are not allowed (in recent
versions it'll fail to make the constraint, in older versions it would
make the constraint but it wouldn't work).

> Is there another way of doing what I want?

You could potentially do the insert/update on user time check in a custom
trigger.  That wouldn't prevent you from dropping a user later that was
referenced, however.

    Well, it's not a table!  It's a view:

dbs=# \d pg_user
     View "pg_catalog.pg_user"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 usename     | name    |
 usesysid    | integer |
 usecreatedb | boolean |
 usesuper    | boolean |
 usecatupd   | boolean |
 passwd      | text    |
 valuntil    | abstime |
 useconfig   | text[]  |
View definition:
 SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb,
pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd,
pg_shadow.valuntil, pg_shadow.useconfig
   FROM pg_shadow;

So, you really want to use the pg_shadow table.

C G wrote:

>
> but I get told that "...pg_user is not a table."
>
> Is there another way of doing what I want?