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
