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
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 |