Re: Wrong record type - caused by SELECT order ???

On Fri, 27 Oct 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);
> ------------------------------------------------------------
> 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);