agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedfind all views depend on a schema/table
4+ messages / 3 participants
[nested] [flat]
* find all views depend on a schema/table
@ 2014-01-24 15:55 Emi Lu <emilu@encs.concordia.ca>
0 siblings, 1 reply; 4+ messages in thread
From: Emi Lu @ 2014-01-24 15:55 UTC (permalink / raw)
To: pgsql-sql
Hello,
Is there a simple way to query all views depend on a schema or table?
E.g.,
view_schema| view_name | depends on schema_name | depends on t1
===========|===========|========================|================
v_schema |v1 | test | t1
"v_schema.v1" is defined as select .... from test.t1... where;
Thanks a lot!
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: find all views depend on a schema/table
@ 2014-01-24 16:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Emi Lu <emilu@encs.concordia.ca>
0 siblings, 2 replies; 4+ messages in thread
From: Tom Lane @ 2014-01-24 16:23 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
Emi Lu <emilu@encs.concordia.ca> writes:
> Is there a simple way to query all views depend on a schema or table?
Well, you could build something that examines pg_depend, or you could
try this:
begin;
drop table some_table restrict;
... note what it complains about ...
rollback;
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: find all views depend on a schema/table
@ 2014-01-24 17:04 Emi Lu <emilu@encs.concordia.ca>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Emi Lu @ 2014-01-24 17:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
>> Is there a simple way to query all views depend on a schema or table?
> Well, you could build something that examines pg_depend, or you could
> try this:
Thank you. I will try to find mapped results for pg_depend.
> begin;
> drop table some_table restrict;
> ... note what it complains about ...
> rollback;
No... to find all views(not in schema1) depend on any schema1.objects.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: find all views depend on a schema/table
@ 2014-01-27 04:42 Tim Landscheidt <tim@tim-landscheidt.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tim Landscheidt @ 2014-01-27 04:42 UTC (permalink / raw)
To: pgsql-sql
Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Is there a simple way to query all views depend on a schema or table?
> Well, you could build something that examines pg_depend, or you could
> try this:
> begin;
> drop table some_table restrict;
> ... note what it complains about ...
> rollback;
Note that neither show dependencies that are "hidden" in
functions, i. e.:
| tim=# CREATE TABLE T (ID INT PRIMARY KEY);
| NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
| CREATE TABLE
| tim=# CREATE FUNCTION F() RETURNS INT AS 'SELECT MIN(ID) FROM T;' LANGUAGE SQL;
| CREATE FUNCTION
| tim=# CREATE VIEW V AS SELECT F();
| CREATE VIEW
| tim=# DROP TABLE T;
| DROP TABLE
| tim=# SELECT * FROM V;
| ERROR: relation "t" does not exist
| LINE 1: SELECT MIN(ID) FROM T;
| ^
| QUERY: SELECT MIN(ID) FROM T;
| CONTEXT: SQL function "f" during inlining
| tim=#
Tim
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-01-27 04:42 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-01-24 15:55 find all views depend on a schema/table Emi Lu <emilu@encs.concordia.ca>
2014-01-24 16:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-01-24 17:04 ` Emi Lu <emilu@encs.concordia.ca>
2014-01-27 04:42 ` Tim Landscheidt <tim@tim-landscheidt.de>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox