PL/pgSQL functions and RETURN NEXT

From: "Craig Bryden" <postgresql(at)bryden(dot)co(dot)za>
To: "pgsql" <pgsql-general(at)postgresql(dot)org>
Subject: PL/pgSQL functions and RETURN NEXT
Date: 2005-01-30 08:54:21
Message-ID: 000c01c506a9$4b862790$10d217c4@amd2800
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Neil Conway 2005-01-30 09:23:15 Re: MySQL worm attacks Windows servers
Previous Message Chris 2005-01-30 03:38:18 Re: Splitting queries across servers