PL/pgSQL functions and RETURN NEXT

Поиск
Список
Период
Сортировка
От Craig Bryden
Тема PL/pgSQL functions and RETURN NEXT
Дата
Msg-id 000c01c506a9$4b862790$10d217c4@amd2800
обсуждение исходный текст
Ответы Re: PL/pgSQL functions and RETURN NEXT
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Chris
Дата:
Сообщение: Re: Splitting queries across servers
Следующее
От: Neil Conway
Дата:
Сообщение: Re: [pgsql-advocacy] MySQL worm attacks Windows servers