Perl: Is there a DBI function similar to statistics_info, to retrieve FK references and constraints?

Question

When performing a \d+ on a table within Postgres, it lists the table schema along with the indexes, as well as other tables that reference it as a FK. Example:

    Table "public.foo_table"
   Column   | Type |   Modifiers   | Storage  | Description 
------------+------+---------------+----------+-------------
 id         | text |               | extended | 
 foo        | text |               | extended | 
 bar        | text |               | extended | 
Indexes:
    "foo_table_id_idx" btree (id)
    "foo_table_foobar_idx" btree (foo,bar)
Foreign-key constraints:
    "foo_table_bar_fk" FOREIGN KEY (bar) REFERENCES public.bar_table(id)
Referenced by:
    TABLE "public.bar_table" CONSTRAINT "bar_table_foo_fk" FOREIGN KEY (foo) REFERENCES public.foo_table(foo)
Has OIDs: no

You can do something $dbh->statistics_info(...) to retrieve the index information. Is there something similar to retrieve the FK info (references and referenced-by)?

It seems like my next option is to either issue a ->do() command, or query the system tables.

found `foreign_key_info`, I'll check to see how its used, since it takes 6 arguments (odd) — vol7ron, Apr 16 '12 at 13:35
@stackoverflow: I really wish you didn't delete your answer, I really do think it was helpful. — vol7ron, Apr 16 '12 at 15:42

vol7ron · Answer 1 · 2012-04-16T14:00:23.860

What I've found so far:

$dbh->foreign_key_info( pk_cat, pk_schema, pk_tbl
                      , fk_cat, fk_schema, fk_tbl      );

# FK References
$dbh->foreign_key_info( undef , undef    , undef   
                      , undef , undef    , $table_name );

# FK Referenced By
$dbh->foreign_key_info( undef , undef    , $table_name 
                      , undef , undef    , undef       );

## Putting the schema/catalog info only ensures you are hitting the intended 
##    table. If you have dupicate tables, or your table is not in the public 
##    schema it's probably a good idea to include the schema.
## Catalog is generally unnecessary for Postgres

score 0 · Answer 2 · answered Apr 16 '12 at 14:14

0

If you run psql with the -E option it will show all the queries it runs to respond to \d (and other) metadata requests. It's pretty easy to copy/paste from that to get the queries you want.

answered Apr 16 '12 at 14:14

kgrittn

18,113
3
39
47

That could be a good alternative. I do have queries already to retrieve the info, I was looking for something a little cleaner, though – vol7ron Apr 16 '12 at 14:25

Perl: Is there a DBI function similar to statistics_info, to retrieve FK references and constraints?

2 Answers2