Thread: Returning 0 rows from a PL/PGSQL

I have the following plpgsql function:

CREATE OR REPLACE FUNCTION public."temp"(int4)
  RETURNS public.books AS
$BODY$DECLARE
    old_book books%rowtype;
BEGIN
    select * into old_book from books
    where book_id = var_book_id;

    IF FOUND = false THEN
        return null;
    ELSE
        return old_book;
    END IF;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

If the function finds a book with the given ID, it returns its row, if
it doesn't, it should return no rows at all (naturally it is
simplified version of what I need). In practice, however, it returns
either a regular row, or a regular row with all fields set to NULL.

So if in my PHP code I have:

$rs = pg_query("select * from temp(-60)");
pg_num_rows($rs);

It keeps printing 1 even if the ID I pass doesn't exist. What's my remedy?

Thanks

--
 ICQ: 1912453
 AIM: VitalyB1984
 MSN: tmdagent@hotmail.com
 Yahoo!: VitalyBe

On Sun, 20 Feb 2005, Vitaly Belman wrote:

> I have the following plpgsql function:
>
> CREATE OR REPLACE FUNCTION public."temp"(int4)
>   RETURNS public.books AS
> $BODY$DECLARE
>     old_book books%rowtype;
> BEGIN
>     select * into old_book from books
>     where book_id = var_book_id;
>
>     IF FOUND = false THEN
>         return null;
>     ELSE
>         return old_book;
>     END IF;
> END;$BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
>
> If the function finds a book with the given ID, it returns its row, if
> it doesn't, it should return no rows at all (naturally it is
> simplified version of what I need). In practice, however, it returns
> either a regular row, or a regular row with all fields set to NULL.

I think you'd need to make the function a set returning one in order to
potentially return no rows (which I think would involve making it returns
setof public.books, doing a return next old_book when found=true and
nothing in the false case and putting a return at the end).

Good thinking, it works :)
Thanks.

On Sat, 19 Feb 2005 16:53:52 -0800 (PST), Stephan Szabo
<sszabo@megazone.bigpanda.com> wrote:
> On Sun, 20 Feb 2005, Vitaly Belman wrote:
>
> > I have the following plpgsql function:
> >
> > CREATE OR REPLACE FUNCTION public."temp"(int4)
> >   RETURNS public.books AS
> > $BODY$DECLARE
> >       old_book books%rowtype;
> > BEGIN
> >       select * into old_book from books
> >       where book_id = var_book_id;
> >
> >       IF FOUND = false THEN
> >               return null;
> >       ELSE
> >               return old_book;
> >       END IF;
> > END;$BODY$
> >   LANGUAGE 'plpgsql' VOLATILE;
> >
> > If the function finds a book with the given ID, it returns its row, if
> > it doesn't, it should return no rows at all (naturally it is
> > simplified version of what I need). In practice, however, it returns
> > either a regular row, or a regular row with all fields set to NULL.
>
> I think you'd need to make the function a set returning one in order to
> potentially return no rows (which I think would involve making it returns
> setof public.books, doing a return next old_book when found=true and
> nothing in the false case and putting a return at the end).
>
>


--
 ICQ: 1912453
 AIM: VitalyB1984
 MSN: tmdagent@hotmail.com
 Yahoo!: VitalyBe