Craig Bryden wrote:
Hi Firstly, let me say that I am a newbie to PostgreSQL.
I have written a PL/pgSQL function that will return a set of results. I have included the code below
**************************************************************************** ******************************* CREATE TYPE pr_SomeFunction_ReturnType as (ID smallint, TypeID smallint, Name varchar(50), Description varchar(500), TypeName varchar(20));
CREATE OR REPLACE FUNCTION pr_SomeFunction (p_TypeID smallint) RETURNS setof pr_SomeFunction_ReturnType AS $$ DECLARE r_Return pr_SomeFunction_ReturnType; BEGIN
SELECT l.ID, l.TypeID, l.Name, l.Description, lt.Name as TypeName INTO r_Return FROM tb_Item l JOIN tb_ItemType lt ON l.TypeID = lt.TypeID;
RETURN NEXT r_Return; RETURN; END; $$ LANGUAGE 'plpgsql';
**************************************************************************** *******************************
When I run "select * from pr_SomeFunction(1::smallint);", I only get one
record back, instead of two.
You need a loop construct here:
FOR r_return IN SELECT l.ID, l.TypeID, l.Name, l.Description, lt.Name as TypeName FROM tb_Item l JOIN tb_ItemType lt USING (TypeID) LOOP
RETURN NEXT r_Return;
END LOOP;
RETURN;
HTH,
Sven
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
