Foreign keys and pg_user table

[C G]

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Stephan Szabo]

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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[dj trombley]

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?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org