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