Postgres Pro
Downloads
Home   >   mailing lists

Thread: Catch multiple records when doing Select Into

Catch multiple records when doing Select Into

From
"SunWuKung"
Date:
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

Re: Catch multiple records when doing Select Into

From
"Ketema"
Date:
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

Re: Catch multiple records when doing Select Into

From
"SunWuKung"
Date:
Hugh, that's a long way to do that.
Thanks for the help.
SWK

Ketema wrote:
> 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