| From: | Richard Huxton <dev(at)archonet(dot)com> |
|---|---|
| To: | David Johnston <polobo(at)yahoo(dot)com> |
| Cc: | Andreas Kretschmer <andreas(at)a-kretschmer(dot)de>, Kevin Duffy <kevind0718(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: function return update count |
| Date: | 2012-01-06 17:36:53 |
| Message-ID: | 4F073135.7020900@archonet.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On 06/01/12 16:33, David Johnston wrote:
> In 9.1 you could use and updatable CTE and in the main query perform
> and return a count. I would think plpgsql would be the better option
> though.
For the SQL option, it would be this (9.1 only though - I think David's
right there).
CREATE FUNCTION f1() RETURNS int AS $$
WITH rows AS (
UPDATE t1 SET ... WHERE ... RETURNING 1
)
SELECT count(*)::int FROM rows
$$ LANGUAGE SQL;
Unfortunately you can't do UPDATE ... RETURNING count(*) directly so you
need to go through this business with the CTE (WITH clause).
Oh - the cast to int is because count() returns bigint.
--
Richard Huxton
Archonet Ltd
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Bosco Rama | 2012-01-06 17:53:36 | Re: URGENT: temporary table not recognized? |
| Previous Message | Tom Lane | 2012-01-06 16:46:23 | Re: URGENT: temporary table not recognized? |