I have a view view_a
in my database on which several other views depend (view_b
, view_c
, etc.)
I need to convert view_a
into a table, because I no longer want the information in this relation to be dynamic and I need the capability to edit rows manually.
Can I replace view_a
with a table without doing a DROP CASCADE
and redefining all views that reference view_a
?
Clarification: I want view_b
and view_c
to continue to reference view_a
(now a table). I want to replace a view with a table, not have a table in addition to a view.
ALTER VIEW changes various auxiliary properties of a view. (If you want to modify the view's defining query, use CREATE OR REPLACE VIEW .) You must own the view to use ALTER VIEW . To change a view's schema, you must also have CREATE privilege on the new schema.
Creating Views The PostgreSQL views are created using the CREATE VIEW statement. The PostgreSQL views can be created from a single table, multiple tables, or another view. CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];
Updating the created view We can also update the created view using the following syntax : SYNTAX: UPDATE view_name SET column = "New Value"; EXAMPLE: UPDATE my_view SET dept = "Health"; OUTPUT: UPDATE VIEW Query returned successfully in 180 msec.
A view is a database object that is of a stored query. A view can be accessed as a virtual table in PostgreSQL. In other words, a PostgreSQL view is a logical table that represents data of one or more underlying tables through a SELECT statement.
I was able to resolve this without tracking down and redefining all objects that depend on view_a
, at the expense of adding one level of useless redirection.
-- create a copy of the result of view_a
CREATE TABLE table_a AS SELECT * FROM view_a;
-- redefine view_a to its own result
CREATE OR REPLACE VIEW view_a AS SELECT * FROM table_a;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With