Reputation: 181
I'm trying to create a plpgsql function in Postgres 8.4 that uses a CTE inside but it's giving me a syntax error. Are they not allowed?
(Something like this, bear in mind I'm writing this without my code.)
With foo as (SELECT id,a as alias FROM foo);
UPDATE zoo SET b = alias FROM foo WHERE id = foo.id;
^Error here
Upvotes: 1
Views: 1112
Reputation: 659367
A data modifying CTE (that's what it is) is not available in PostgreSQL 8.4.
Your statement would work with minor patches as @wildplasser demonstrates in PostgreSQL 9.1 or later, where data modifying CTE were introduced.
A very simple replacement for 8.4 would be a subquery:
UPDATE zoo z
SET b = f.alias
FROM (SELECT id, a as alias FROM foo) f
WHERE z.id = f.id;
The example can be further simplified (but maybe the real world case is more complex):
UPDATE zoo z
SET b = f.a
FROM foo f
WHERE z.id = f.id;
Remember to table-qualify the otherwise ambiguous column name id
in the WHERE
clause.
Upvotes: 2
Reputation: 44250
WITH foo as (
SELECT id
, a AS zalias
FROM footable
)
UPDATE zoo z
SET b = f.zalias
FROM foo f
WHERE z.id = f.id
;
NOTE: "alias" is a reserved word.
UPDATE as of per comment by Erwin Brandstetter: the CTE is not valid in a UPDATE statement in 8.4. You'll need 9.1 or higher. Since a CTE is actually a kind of instant view, you could put the CTE body inside a view, and refer to that.
CREATE VIEW foo as (
SELECT id
, a AS zalias
FROM footable
);
UPDATE zoo z
SET b = f.zalias
FROM foo f
WHERE z.id = f.id
;
DROP VIEW foo;
Upvotes: 3