pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SQL to retrieve foreign keys
2+ messages / 2 participants
[nested] [flat]

* SQL to retrieve foreign keys
@ 2000-10-11 15:56 Colleen Williams <colleen@digital-arts.co.uk>
  2000-10-11 16:29 ` Re: SQL to retrieve foreign keys Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Colleen Williams @ 2000-10-11 15:56 UTC (permalink / raw)
  To: pgsql-sql

Hi,

I would like to write some SQL to extend the phpPgAdmin tool where it only 
displays primary keys related to a table. I have looked at the programmers 
doco and searched the discussions on foreign keys but was not able to 
decipher how to write the SQL. It is probably really trivial for someone 
who knows how the PostgreSQL catalog tables are related. Can someone please 
help me. Many Thanks.

Colleen.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: SQL to retrieve foreign keys
  2000-10-11 15:56 SQL to retrieve foreign keys Colleen Williams <colleen@digital-arts.co.uk>
@ 2000-10-11 16:29 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Stephan Szabo @ 2000-10-11 16:29 UTC (permalink / raw)
  To: Colleen Williams <colleen@digital-arts.co.uk>; +Cc: pgsql-sql


Actually, right now it isn't trivial with the implementation.
You need to decode things out of the arguments to triggers which
are in a bytea and there aren't alot of reasonable sql level stuff
to decode it.  If you don't mind doing some of the work on the front end,
you should be able do it.

If you do select tgconstrname, proname, tgargs, tgdeferrable,
tginitdeferred, tgnargs from pg_trigger,pg_proc where tgname like
'RI_ConstraintTrigger%' and tgfoid=pg_proc.oid;

That will get you three rows per foreign key constraint.  
tgconstrname is the constraint name given to the constraint (currently, if
none is given, <unknown> is used).
proname tells you about what the constraint does.  One will be 
 RI_FKey_check_ins which just checks new values in the fk table.
 The other two are more interesting, one will be something like
 RI_FKey_<something>_del, and the other RI_FKey_<something>_upd.
 These tell the defined referential actions (no actions, cascade, etc)
 for delete and update.
tgdeferrable says whether or not the constraint is DEFERRABLE
tginitdeferred says whether or not the constraint is INITIALLY DEFERRED
tgnargs holds the number of arguments to the trigger
tgargs holds the arguments in a bytea separated by \000
 The arguments are as follows:
  constraint name
  fk table
  pk table
  match type
  fk col1
  pk col1
  ...
  fk coln
  pk coln

Stephan Szabo
sszabo@bigpanda.com

On Wed, 11 Oct 2000, Colleen Williams wrote:

> Hi,
> 
> I would like to write some SQL to extend the phpPgAdmin tool where it only 
> displays primary keys related to a table. I have looked at the programmers 
> doco and searched the discussions on foreign keys but was not able to 
> decipher how to write the SQL. It is probably really trivial for someone 
> who knows how the PostgreSQL catalog tables are related. Can someone please 
> help me. Many Thanks.
> 
> Colleen.
> 




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-10-11 16:29 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-11 15:56 SQL to retrieve foreign keys Colleen Williams <colleen@digital-arts.co.uk>
2000-10-11 16:29 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox