Catch multiple records when doing Select Into

On Oct 31, 4:15 am, "SunWuKung" <Balazs.Kl...@t-online.hu> wrote:

I have a SELECT INTO varname columname FROM ... statement and I would
like to raise an exception when that returns more than one record.
GET DIAGNOSTICS rowcount = ROW_COUNT seems to return always one for
this. Currently I find no other way to do that than to run this
statement twice - first to see how many records it returns, which seems
very inefficient.

I can see that this will be handled for 8.2, but for 8.1 what is the
best practice to do this?

Thanks.
SWK

It would be easier if we could see the context in which you are doing
the select into. However I think this may help. Try putting the
select into in a loop:

declare
_result record;
_rows integer;
begin
_rows := 0;
for _result in select statement here loop
in here put logic to raise your exception if you get more
than one result
_rows := _rows + 1;
if _rows > 1 then raise exception 'Hey too many rows';
return next _result;
end loop;
end;

On Oct 31, 4:15 am, "SunWuKung" <Balazs.Kl...@t-online.hu> wrote:

I have a SELECT INTO varname columname FROM ... statement and I would
like to raise an exception when that returns more than one record.
GET DIAGNOSTICS rowcount = ROW_COUNT seems to return always one for
this. Currently I find no other way to do that than to run this
statement twice - first to see how many records it returns, which seems
very inefficient.

I can see that this will be handled for 8.2, but for 8.1 what is the
best practice to do this?

Thanks.
SWK