Re: Catch multiple records when doing Select Into

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(dot)Kl(dot)(dot)(dot)(at)t-online(dot)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