Обсуждение: how to update a view from a table

How can i update all the fields of a view from a table(this is table different from the table which has created the view)?
I don't want to specify the name of the columns in the commands
e.g i want to do something like this

UPDATE any_view SET (SELECT * FROM any_view) = (SELECT  *  FROM another_table);

but this query doesn't work.

Whats the way around without specifying the colum name in the SET. In that sense i want to make this query a general query which can update any view from any other table.

Vijay Sharma wrote:
> How can i update all the fields of a view from a table(this is table
> different from the table which has created the view)?
> I don't want to specify the name of the columns in the commands
> e.g i want to do something like this
>
> UPDATE any_view SET (SELECT * FROM any_view) = (SELECT  *  FROM
> another_table);
>
> but this query doesn't work.

It can't for quite a few reasons.


If you're just trying to replace the contents of `any_view' with the
contents of `another_table', rather than UPDATE the records in any_view
with the values of the records in `another_table', just:

DELETE FROM any_view;
INSERT INTO any_view SELECT * FROM another_table;


If you're trying to update the rows, not just replace the whole
contents, something like this would have more of a chance of working:

UPDATE any_view
SET any_view = another_table
FROM another_table
WHERE (any_view.primary_key = another_table.primary_key);


... but PostgreSQL doesn't currently understand the use of the table
name as a row reference here, and will complain:

ERROR:  column "any_view" of relation "any_view" does not exist


Personally I don't know of any way to do what you want without building
queries in PL/PgSQL and EXECUTEing them.



--
Craig Ringer