Hello,
I am trying to create some functions which return many rows using plpgsql. This example could be done with SQL, but later I will need plpglsql. I was constantly getting the wrong record type error with a couple different functions. Finally I found that if I changed the order of columns in the SELECT statement then this simple example would work.
Any suggestions as to why this is happening or what I can do to consistently get such functions to work ? Is there an easier way to do all this ? (
select * from list_categories(1,200608);
ERRR: wrong record type supplied in RETURN NEXT
CNTEXT: PL/pgSQL function "list_categories" line 11 at return next
CREATE R REPLACE FUNCTIN list_categories(int4, int4)
RETURNS SETF category_list AS
$BDY$
DECLARE
foo category_list;
rec RECRD;
BEGIN
FR rec IN
SELECT c.id, c.category_name FRM categories c WHERE user_id = pUser_id
LP
:= rec.id;
:= rec.category_name;
RETURN NEXT rec;
END LP;
RETURN;
END;
$BDY$
LANGUAGE 'plpgsql' VLATILE;
However, if I change the order of columns in the SELECT and run the same:
select * from list_categories(1,200608);
Then the function works fine
CREATE R REPLACE FUNCTIN list_categories(int4, int4)
RETURNS SETF category_list AS
$BDY$
DECLARE
foo category_list;
rec RECRD;
BEGIN
FR rec IN
SELECT c.category_name, c.id FRM categories c WHERE user_id = pUser_id
LP
:= rec.id;
:= rec.category_name;
RETURN NEXT rec;
END LP;
RETURN;
END;
$BDY$
LANGUAGE 'plpgsql' VLATILE;
The table:
CREATE TABLE categories
(
id int4 NT NULL DEFAULT nextval('categories_id_seq'::regclass),
user_id int4 NT NULL,
category_name varchar(45) NT NULL,
CNSTRAINT "categoriesPK" PRIMARY KEY (id),
CNSTRAINT "categories_userFK" FREIGN KEY (user_id) REFERENCES users (id) N UPDATE N ACTIN N DELETE N ACTIN
)
WITHUT IDS;
and TYPE
CREATE TYPE category_list AS
(ocategory_name varchar(60),
oid int4);
Send instant messages to your online friends http://uk.messenger.yahoo.com
(end of broadcast)
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Fri, 27 2006, John Cobo wrote:

I am trying to create some functions which return many rows using
plpgsql. This example could be done with SQL, but later I will need
plpglsql. I was constantly getting the wrong record type error with a
couple different functions. Finally I found that if I changed the order
of columns in the SELECT statement then this simple example would work.

Any suggestions as to why this is happening or what I can do to
consistently get such functions to work ? Is there an easier way to do
all this ? (

Well, I think the simple answer is to return next foo rather than rec in
the function.

The longer answer is that in the first case you're returning a record with
an int first and a varchar second and in the second you're return a record
with a varchar first and an int second and category_list is compatible
with the latter and not the former.

select * from list_categories(1,200608);

ERRR: wrong record type supplied in RETURN NEXT
CNTEXT: PL/pgSQL function "list_categories" line 11 at return next

CREATE R REPLACE FUNCTIN list_categories(int4, int4)
RETURNS SETF category_list AS
$BDY$
DECLARE
foo category_list;
rec RECRD;
BEGIN
FR rec IN
SELECT c.id, c.category_name FRM categories c WHERE user_id = pUser_id
LP
:= rec.id;
:= rec.category_name;
RETURN NEXT rec;
END LP;
RETURN;
END;
$BDY$
LANGUAGE 'plpgsql' VLATILE;

However, if I change the order of columns in the SELECT and run the same:
select * from list_categories(1,200608);

Then the function works fine

CREATE R REPLACE FUNCTIN list_categories(int4, int4)
RETURNS SETF category_list AS
$BDY$
DECLARE
foo category_list;
rec RECRD;
BEGIN
FR rec IN
SELECT c.category_name, c.id FRM categories c WHERE user_id = pUser_id
LP
:= rec.id;
:= rec.category_name;
RETURN NEXT rec;
END LP;
RETURN;
END;
$BDY$
LANGUAGE 'plpgsql' VLATILE;

The table:
CREATE TABLE categories
(
id int4 NT NULL DEFAULT nextval('categories_id_seq'::regclass),
user_id int4 NT NULL,
category_name varchar(45) NT NULL,
CNSTRAINT "categoriesPK" PRIMARY KEY (id),
CNSTRAINT "categories_userFK" FREIGN KEY (user_id) REFERENCES users (id) N UPDATE N ACTIN N DELETE N ACTIN
)
WITHUT IDS;

and TYPE
CREATE TYPE category_list AS
(ocategory_name varchar(60),
oid int4);

(end of broadcast)
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly