CODEWITHSUNDEEP
amazon-redshift
Mihai Tache
Mihai Tache

Reputation: 171

Is it possible to rename a table in Redshift without propagating the change to dependent views?

I noticed that if you rename a table in Redshift the new name is automatically progagated to all the views that are based on that table.

Is there a way to prevent this behavior so that the view definition would contain the old table name?

Upvotes: 4

Views: 5710

Answers (2)

segfault.py
segfault.py

Reputation: 133

Looks like this feature was introduced to Redshift. See WITH NO SCHEMA BINDING in the Redshift docs.

CREATE VIEW i_am_view_are_you AS
SELECT good_column
FROM public.i_am_table
WITH NO SCHEMA BINDING

Upvotes: 5

BigDataKid
BigDataKid

Reputation: 1227

Mihai,

That's an excellent question.

The feature that you are searching would be very useful when you are manually doing table maintenance and you want to move/create/rename tables without impacting existing dependent objects.

The short answer to your question is "NO". There is NOT a way to prevent this behavior in Redshift.

There is nothing you can do either on your "ALTER TABLE" statement or "CREATE VIEW" statement that would prevent a dependent view from being changed when the underlying table changes.

BUT

You can easily obtain the DDL for the views before changing the name, and you can re-run the DDL when you are ready, so your views would point the the right place.

The following SQL would show all the dependent views for a particular table and produce to re-create each corresponding view.

SELECT DISTINCT 
 srcobj.oid AS src_oid
,srcnsp.nspname AS src_schemaname
,srcobj.relname AS src_objectname
,tgtobj.oid AS dependent_viewoid
,tgtnsp.nspname AS dependent_schemaname
,tgtobj.relname AS dependent_objectname
,'--DROP VIEW ' + tgtnsp.nspname + '.' + tgtobj.relname + ';\nCREATE OR REPLACE VIEW ' + tgtnsp.nspname+ '.' + tgtobj.relname  + ' AS\n' + COALESCE(pg_get_viewdef(tgtobj.oid, TRUE), '') AS ddl
FROM
pg_catalog.pg_class AS srcobj
INNER JOIN
pg_catalog.pg_depend AS srcdep
ON srcobj.oid = srcdep.refobjid
INNER JOIN
pg_catalog.pg_depend AS tgtdep
ON srcdep.objid = tgtdep.objid
JOIN
pg_catalog.pg_class AS tgtobj
ON tgtdep.refobjid = tgtobj.oid
AND srcobj.oid <> tgtobj.oid
LEFT OUTER JOIN
pg_catalog.pg_namespace AS srcnsp
ON srcobj.relnamespace = srcnsp.oid
LEFT OUTER JOIN
pg_catalog.pg_namespace tgtnsp
ON tgtobj.relnamespace = tgtnsp.oid
WHERE tgtdep.deptype = 'i' --dependency_internal
AND tgtobj.relkind = 'v' 
AND srcobj.relname = '<your-table-name>'
AND srcnsp.nspname = ' <your-schema-name>' ;

Substitute and for your corresponding values.

Upvotes: 2

Related Questions