CODEWITHSUNDEEP
postgresql
user2301515
user2301515

Reputation: 5107

Metadata from a postgresql-database

I want to get data about all relationships from a PostgreSql-database with query like

SELECT
    [creatorUser].[primTableName].[primColumnName] AS primary_column,
    [creatorUser].[foreignTableName].[foreignColumnName] AS foreign_column,
FROM
    sys.sysidx    si,
    sysidxcol     sic,
    sys.SYSFKCOL  sfc,
    sysusers      su,
    sys.systable  st,
    sys.syscolumn sc
WHERE
    si.table_id=sic.table_id AND
    si.index_id=sic.index_id AND
    sfc.primary_column_id=sic.primary_column_id AND
    sfc.foreign_table_id=sic.table_id AND
    st.table_id=sfc.foreign_table_id AND
    su.uid=st.creator AND
    sc.column_name=si.index_name

Can anybody help, how to get that data? How to be a correct query

owner1.table1.primCol1 owner1.table1.foreignCol1
owner1.table1.primCol2 owner1.table1.foreignCol2
 ..

?

Upvotes: 1

Views: 2676

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324275

PostgreSQL has no sys tables, so it's not clear how you could possibly expect that query to work. Given your comments, I'm guessing that's the query you use on Sybase, and you want to translate it for PostgreSQL.

Well, assuming that the Sybase syntax:

[creatorUser].[primTableName].[primColumnName] AS primary_column,
[creatorUser].[foreignTableName].[foreignColumnName] AS foreign_column,

means "Concatenate the user who created the relationship, the table name, and the column name" I'd probably do something in PostgreSQL like:

SELECT 
  format('%I.%I.%I', fk.table_schema, fk.table_name, fk.column_name) AS foreign_side,
  format('%I.%I.%I', pk.table_schema, pk.table_name, pk.column_name) AS target_side
FROM 
  information_schema.referential_constraints rc 
  INNER JOIN information_schema.key_column_usage fk 
    ON (rc.constraint_catalog = fk.constraint_catalog 
        AND rc.constraint_schema = fk.constraint_schema 
        AND rc.constraint_name = fk.constraint_name) 
  INNER JOIN information_schema.constraint_column_usage pk 
    ON (rc.unique_constraint_catalog = pk.constraint_catalog 
        AND rc.unique_constraint_schema = rc.constraint_schema 
        AND rc.unique_constraint_name = pk.constraint_name);

Except for the format(...) that'll work on Sybase too, with some luck.

Output like:

    foreign_side     |           target_side           
---------------------+---------------------------------
 public.users.id_cat | public.usercategories.id_numcat
(1 row)

That's schema.table.column . I'm guessing Sybase's "creatorUser" is really "schema" in this case.

Upvotes: 3

mys
mys

Reputation: 2473

You should query appropriate tables under pg_catalog or information_schema schemas. The easiest way to figure out the correct query is to run (... = other command-line options like user, database, ...)

psql -E ...

and then psql displays meta-data queries used in internal commands like \d, \dt, \dv, ... From manual:

-E
--echo-hidden
Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN from within psql.

information_schema is same as pg_catalog but more portalbe (it is defined in SQL standard). If your app uses postgres only then I would use pg_catalog instead of information_schema

Upvotes: 1

Related Questions