1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
user3856970
  • 319
  • 1
  • 3
  • 11

1 Answers1

1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Is this true though? In Postgres 11.2, using the above example, this command works: `ALTER TABLE t1 DROP COLUMN c` (`c` doesn't have dependents); but `ALTER TABLE t1 DROP COLUMN a` fails with a message that I should use `CASCADE`. So Postgres 11.2 does 'know' that it's OK to delete `c` but not `a`. – user3856970 Apr 16 '19 at 19:23
  • That's what you call the "source column" right? That is tracked, yes. – Laurenz Albe Apr 16 '19 at 19:37
  • Oops, you're right :( I'll accept this as the answer, do you happen to know if this is possible in another SQL engine? – user3856970 Apr 16 '19 at 19:42
  • 1
    I know that Oracle doesn't track such dependencies at all, and you can just modify or drop the base table, rendering the view "invalid". I have no idea about other RDBMS. – Laurenz Albe Apr 16 '19 at 19:43