Common table expression,WITH clause in PostgreSQL

问题:

for example the following is my query

WITH stkpos as (
select * from mytbl
),
updt as (
update stkpos set field=(select sum(fieldn) from stkpos)
)
select * from stkpos

ERROR: relation "stkpos" does not exist

回答1:

Unlike MS-SQL and some other DBs, PostgreSQL's CTE terms are not treated like a view. They're more like an implicit temp table - they get materialized, the planner can't push filters down into them or pull filters up out of them, etc.

One consequence of this is that you can't update them, because they're a copy of the original data, not just a view of it. You'll need to find another way to do what you want.

If you want help with that you'll need to post a new question that has a clear and self contained example (with create table statements, etc) showing a cut down version of your real problem. Enough to actually let us understand what you're trying to accomplish and why.