Re: function return update count

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