X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id BFA805285C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Jun 2005 06:55:52 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 17310-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Jun 2005 09:55:51 +0000 (GMT)
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2])
	by svr1.postgresql.org (Postfix) with ESMTP id B5DA75293D
	for <pgsql-sql@postgresql.org>; Tue, 21 Jun 2005 06:55:40 -0300 (ADT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id BD6FC30959; Tue, 21 Jun 2005 11:56:42 +0200 (MET DST)
From: Zac <zaccheob@inwind.it>
X-Newsgroups: pgsql.sql
Subject: SELECT INTO returning more than one row
Date: Tue, 21 Jun 2005 11:54:41 +0200
Organization: Hub.Org Networking Services
Lines: 28
Message-ID: <d98o6p$i1p$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: usenet@news.hub.org
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200506/327
X-Sequence-Number: 21928

Hi.

I have this problem in a plpgsql function:

SELECT INTO myvar col FROM table WHERE ...;

IF <query returns more than one row> THEN
	do something
ELSE IF <query returns no rows> THEN
	do something else
ELSE
	do other things

If <query returns no rows> I know that myvar IS NULL OR GET DIAGNOSTICS 
ROW_COUNT is zero.

Is there a way to know if the query returned more than one row?
In Oracle PL/SQL I can catch the TOO_MANY_ROWS exception but in Postgres 
I found no way for doing it:

- myvar is correctly valued with the first occurrence of the result
- FOUND is TRUE
- GET DIAGNOSTICS ROW_COUNT is 1 (it counts only fetched rows)

I tried to use a cursor but the only way to know how many rows it 
returned is to fetch them all. (And I don't like this solution)

Thanks in advance.