From: pascal+postgres@ensieve.org
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Subject: Update Returning as subquery
Message-Id: <F1134784-45F6-48C9-BA79-98F9BBE00051@ensieve.org>
Date: Sun, 10 Aug 2014 17:15:20 +0100
To: pgsql-sql@postgresql.org
Mime-Version: 1.0 (Mac OS X Mail 8.0 \(1971.5\))
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Hi,

I want to update some values in a table, and need to count the number of va=
lues actually changed; but ROW_COUNT returns the number of total rows touch=
ed.

But this gives a syntax error:

SELECT count(*) INTO my_count
FROM (
  UPDATE stuff
  SET value =3D maybe_null(key)
--^
  WHERE value IS NULL
  RETURNING value ) AS t
WHERE value IS NOT NULL;

Why is that forbidden? Isn't the purpose of a RETURNING clause to return va=
lues like a SELECT statement would, and shouldn't it therefore be allowed t=
o occur in the same places?


I switched it around using a CTE in this case:

WITH new_values AS (
  SELECT key, maybe_null(key) AS value
  FROM stuff WHERE value IS NULL)
UPDATE stuff AS s
SET value =3D n.value
FROM new_values AS n
WHERE n.key =3D s.key
AND n.value IS NOT NULL;

Which only touches rows that will be changed and returns a useful ROW_COUNT=
, but needs a join.=20

Cheers,

--=20
Pascal Germroth


--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql