CODEWITHSUNDEEP
postgresqlplpgsqlcommon-table-expression
jpp1jpp1
jpp1jpp1

Reputation: 181

Using CTEs in plpgsql

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

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

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

wildplasser
wildplasser

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

Related Questions