Re: how to update a view from a 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