From: | Josh Kupershmidt <schmiddy(at)gmail(dot)com> |
---|---|
To: | Esmin Gracic <esmin(dot)gracic(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: get referential values |
Date: | 2011-01-20 03:18:17 |
Message-ID: | AANLkTikFgzhwz9OxirvUvaPHzzh3ZmUMpG7bh6MZHbEK@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Jan 19, 2011 at 12:08 PM, Esmin Gracic <esmin(dot)gracic(at)gmail(dot)com> wrote:
> Knowing only tablename (schema_name.table_name) how to retrieve foreign keys
> and related values. (using pg_catalog is preferred). I developing framework
> for adobe flex (on php and postgresql).
> Already figured out how to get primary key, not null columns and column
> types, but kinda stuck here.
> I can post these queries, if someone needs (it would be nice to have these
> in wiki - where I've seen the query for primary key retrieval).
>
> I need following format:
>
> select .... 'schema_name.table_name'::regclass....
>
> no | fk_name| table_column_name | foreign_table_name | foreign_column_name
>
> no is used for composite foreign keys.
If you fire up psql with the -E argument, it will spit out all the SQL
it uses to figure out such things, such as when you enter "\d
tablename". For instance, in psql:
test=# \d parent
-- lots of queries displayed to find the table's OID (24584, in this case),
-- indexes, constraints, etc.
********* QUERY **********
SELECT conname,
pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '24584' AND r.contype = 'f' ORDER BY 1
**************************
-- etc.
You should be able to adapt those queries (particularly the one shown
above, though you might have to parse out the "condef" column) to suit
your needs.
Josh
From | Date | Subject | |
---|---|---|---|
Next Message | Gregory Machin | 2011-01-20 06:49:04 | rubyrep breaks Silver Stripe (MVC frame work) and vis versa. |
Previous Message | Josh Kupershmidt | 2011-01-20 03:06:10 | Re: possible error in documentation for 9.1 |