Good morning.
I would be grateful if someone could help with the following issue I am having on PostgreSQL v15.2 on AWS RDS.
This is an AWS managed instance, in the dev environment, we have a number of users creating tables and views in about 10 different schemas in a number of databases.
All users are a member of the rds_superuser role as this is a dev box, we trust them and they need to load and run tests on lots of data.
As part of the load process, we need to recreate some views so that they point to the schema as opposed to .
I have run the following query to see if there are any views in the schema which reference a table called (for example) "sweets":
select u.view_name
from information_schema.view_table_usage u
join information_schema.views v on u.view_schema = v.table_schema
and u.view_name = v.table_name
where u.table_schema not in ('information_schema', 'pg_catalog')
and u.table_name = 'sweets'
order by u.view_schema, u.view_name
but this returns nothing. The owner of the view which references the table "sweets" is the user who created it (user.1), they, and I, both belong to the rds_superuser role.
However, the script above returns nothing for me at all when I run it.
The documentation here : https://www.postgresql.org/docs/current/infoschema-view-table-usage.html
Says "A table is only included if that table is owned by a currently enabled role.", I have tried altering the view to be owned my myself, but still no results are returned.
The view I am interested in has the following definition:
CREATE OR REPLACE VIEW dev.vw_sweets
AS
SELECT id,
information_type,
expiry_date,
status,
ai_ddd,
ai_redd
FROM dev.sweets
WHERE status = 'Valid'::text AND ai_ddd IS NOT NULL;
ALTER TABLE dev.vw_sweets
OWNER TO "user.1";
I just need a way to find any views in the current database/schema which reference a certain table, i.e. "sweets", regardless of who owns the table or the view. Is this possible in Postgres? I have spent many hours searching google and trying various methods to not use the view_table_usage/views information_schema tables, but to get the names of the tables referenced in a view without parsing the view definition seems challenging, so view_table_usage seems my only bet.
Any help on this would be gratefully appreciated.
thank you
Duncan.
I have tried setting the default privileges on the schemas and databases to be the rds_superuser role that I, and the user who owns the view which references the table I am interested in, to allow all access, but still the view_table_usage returns nothing for me.
I have changed the owner of the view which references the table to me myself, then re-run the query to see if the view is picked up, as it does reference the table "sweets" but, again, nothing is returned.
