From: Christoph Haller <ch@rodos.fzk.de>
Message-Id: <200404151458.QAA03227@rodos>
Subject: Re: Row count after SELECT INTO?
To: fduch@antar.bryansk.ru (Alexander M. Pravking)
Date: Thu, 15 Apr 2004 16:58:48 METDST
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20040407190426.GI41029@dyatel.antar.bryansk.ru>;
	from "Alexander M. Pravking" at Apr 7, 104 10:17 pm

> 
> Dear SQL and plPgSQL gurus, I seek for your wisdom.
> 
> I have a variable assignment via SELECT INTO in my function, and I want
> to separate 3 cases:
> 1) no rows found;
> 2) one row found;
> 3) many rows found (ambiguous select).
> 
> The first case is simple, I use FOUND variable for it.
> But how about the third? I tried to GET DIAGNOSTICS nrows = ROW_COUNT,
> bit it seems to get only value of 0 or 1. Is this how it supposed to be
> when SELECTing INTO a single variable?
> 
> The only way I see now is a FOR ... IN SELECT loop, and I woner if
> there is a simpler solution. Could you please help me?
> 
> 
> -- 
> Fduch M. Pravking
> 
I think ROW_COUNT values of 0/1 when SELECTing INTO a single variable are correct. 
Because you cannot see more than one row anyway. 
If you simply want to know about the number of result rows and don't care about 
the data, you may change your SELECT ... into a SELECT COUNT(... 
and separate your 3 cases from there. 
Otherwise you'll have to go to a FOR ... IN SELECT loop. 

Regards, Christoph