I've defined some views, built on other views/tables in Redshift, and would like to get info from the system tables regarding the dependencies at the column level.
Say, for example, I have these definitions:
CREATE TABLE t1 AS (SELECT 2 as a, 4 as b, 99 as c );
CREATE VIEW v1 AS (SELECT a, b FROM t1);
CREATE VIEW v2 AS (SELECT a*b/2 as x FROM v1);
What I'd like to do is create some sort of query on the system or catalog tables that will return something like:
target_column | target_table_or_view | source_column | source_table_or_view |
------------------------------------------------------------------------------
x | v2 | a | v1
x | v2 | b | v1
a | v1 | a | t1
b | v1 | b | t1
I've tried the solution given here: How to create a dependency list for an object in Redshift?. However, this query doesn't produce the "target column" column I'm looking for and I don't know how to adjust it.
Is this possible? Ideally I'd like to do this in Redshift, but if needed I can use a newer version of Postgres.
There is no dependency associated with the “target column” in PostgreSQL, so you cannot find it in the metadata.
It is the complete view (its query rewrite rule, to be exact) that has a dependency on the source table and column.