Thread: Extracting data from the view to retrieve the foreign key is not declared

Given a view, I need to extract tables, the join columns (ON) . I need to do
this analysis because of the view (agreements with the join condition and
where) I can say that there is a foreign key

Example:

CREATE OR REPLACE VIEW x_customer AS
SELECT a.asset_id, a.client_id
FROM asset_d ad, asset a ON asset_id = ad.asset_id

Result:

NAMEVIEW |TABLE1    | COLUMN1  | TABLE2 | COLUMN2
x_customer| asset_d  | asset_id    | asset    | asset_id   |
x_customer| asset      | -                |  -           | -               |

OR

CREATE OR REPLACE VIEW x_customer AS
 SELECT a.asset_id, a.client_id, a.org_id, a.isactive, a.created,
a.createdby, a.updated, a.updatedby, a.value, a.name, a.description,
a.ad_user_id, ( SELECT count(*) AS count
           FROM asset_d ad
          WHERE a.asset_id = ad.asset_id) AS deliverycount
   FROM asset a
  WHERE a.bpartner_id IS NOT NULL;
Result:

NAMEVIEW |TABLE1    | COLUMN1  | TABLE2 | COLUMN2
x_customer| asset_d  | asset_id    | asset    | asset_id   |

Someone can help me?
Thanks




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Extracting-data-from-the-view-to-retrieve-the-foreign-key-is-not-declared-tp5795408.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

nill wrote:
> Given a view, I need to extract tables, the join columns (ON) . I need to do
> this analysis because of the view (agreements with the join condition and
> where) I can say that there is a foreign key

Do I understand right that you want to find out the tables involved in
a view definition?

Maybe querying pg_depend can help, since there are dependency relationships
between the view and its query rewrite rule, but also between the rule and
the columns of the underlying tables.

Yours,
Laurenz Albe

Laurenz Thanks for your reply, it is going to help me.

I use this query to get the dependencies of view with the objects in the db
SELECT *
FROM INFORMATION_SCHEMA.view_column_usage

I now have to explain the attributes of the join tree, that is JOIN
expressions e JOIN clauses.

Thanks




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Extracting-data-from-the-view-to-retrieve-the-foreign-key-is-not-declared-tp5795408p5795524.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.