PL/pgSQL functions and RETURN NEXT

["Craig Bryden" <postgresql@xxxxxxxxxxxx>]

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. In the tb_Items table, there are two records
that meet the criteria, and if I run the query on it's own (ouside a
function), I do get two records in the results.

Any help with understanding the usage of RETURN NEXT will be greatly
appreciated.

Thanks
Craig


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)