Re: Can you help me with this query? - Mailing list pgsql-general

Joe, I'm sorry but I cannot create functions or anything like that, it's
all has to be a select statement (or series of select statements). Users
pull up our product and browse all the databases and scheme's and such, if
we asked them to create functions or if we did it through calls it would
be intrusive.

I tried to flatten out the function into a nested query but couldn't quite
get it right, is it possible to embed the select statement in the function
into the main select statement?

- Mike


> mike.griffin@mygenerationsoftware.com wrote:
>> The query below will return all of the foreign keys in the current
>> schema,
>> I get the ForeignKey name as FK_NAME and both the primary and foreign
>> table's name and schema, now I just need the columns involved in the
>> foreign key itself, there is a column called confkey in pg_constraint
>> and
>> it's an array?  It holds the column id
>
> It is an array because foreign keys can have more than one participating
> field -- how do you want that represented? Here's a way that you can get
> an array of the participating field names, assuming you're using
> Postgres 7.4.x:
>
> create or replace function getattnames(oid, smallint[])
> returns name[] as '
>    select array(select attname from pg_attribute
>                 where attrelid = $1
>                 and attnum = any ($2))
> ' language sql;
>
> SELECT cl.relname as TABLE_NAME,
>         cr.relname as FK_TABLE_NAME,
>         getattnames(ct.conrelid, ct.conkey) as TBL_ATTS,
>         getattnames(ct.confrelid, ct.confkey) as FK_TBL_ATTS
> FROM pg_constraint ct
> JOIN pg_class cl ON cl.oid=conrelid
> JOIN pg_namespace nl ON nl.oid=cl.relnamespace
> JOIN pg_class cr ON cr.oid=confrelid
> JOIN pg_namespace nr ON nr.oid=cr.relnamespace
> LEFT OUTER JOIN pg_description des ON des.objoid=ct.oid
> WHERE contype='f';
>
>       table_name     |   fk_table_name    |  tbl_atts   | fk_tbl_atts
> --------------------+--------------------+-------------+-------------
>   rule_and_refint_t3 | rule_and_refint_t1 | {id3a,id3b} | {id1a,id1b}
>   rule_and_refint_t3 | rule_and_refint_t2 | {id3a,id3c} | {id2a,id2c}
>   fktable            | pktable            | {fk}        | {id}
>   clstr_tst          | clstr_tst_s        | {b}         | {rf_a}
> (4 rows)
>
> HTH,
>
> Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>