Thread: assign the row count of a query to a variable

Hi,

I'm trying to assign the row count of a query to a variable in a function
but I'm not having any luck.

Could someone tell me the syntax? I've been looking in the docs and
googling for a long time but just can't find the answer.

I've tried:
CREATE OR REPLACE FUNCTION ret1() RETURNS int4 AS '
BEGIN
declare
var int4;
begin     --select var count(*) from T;     --select var (count(*)) from T;     --select var = count(*) from T;     var
=select count(*) from T;  return   var;
 
END;
END;
'
LANGUAGE 'plpgsql';

       var := count(*) from T;

or :
       SELECT INTO var count(*) from T;

On Fri, Jan 14, 2005 at 03:43:04PM -0500, Kevin B. wrote:

> I'm trying to assign the row count of a query to a variable in a function
> but I'm not having any luck.

Please be more specific than "not having any luck."  What are you
expecting to happen and what actually does happen?

> Could someone tell me the syntax? I've been looking in the docs and
> googling for a long time but just can't find the answer.

See the "Basic Statements" and "Expressions" sections of the PL/pgSQL
documentation.  Either of the following should work:

var := count(*) FROM T;
SELECT INTO var count(*) FROM T;

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/