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);
------------------------------------------------------------
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "list_categories" line 11 at return next
--------------------------------------------------------------
CREATE OR REPLACE FUNCTION list_categories(int4, int4)
RETURNS SETOF category_list AS
$BODY$
DECLARE
foo category_list;
rec RECORD;
BEGIN
FOR rec IN
SELECT c.id, c.category_name FROM categories c WHERE user_id = pUser_id
LOOP
foo.Oid := rec.id;
foo.Ocategory_name := rec.category_name;
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
------------------------------------------------------------
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 OR REPLACE FUNCTION list_categories(int4, int4)
RETURNS SETOF category_list AS
$BODY$
DECLARE
foo category_list;
rec RECORD;
BEGIN
FOR rec IN
SELECT c.category_name, c.id FROM categories c WHERE user_id = pUser_id
LOOP
foo.Oid := rec.id;
foo.Ocategory_name := rec.category_name;
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
----------------------------------------------
The table:
CREATE TABLE categories
(
id int4 NOT NULL DEFAULT nextval('categories_id_seq'::regclass),
user_id int4 NOT NULL,
category_name varchar(45) NOT NULL,
CONSTRAINT "categoriesPK" PRIMARY KEY (id),
CONSTRAINT "categories_userFK" FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;
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
