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