Skip site navigation (1) Skip section navigation (2)

Wrong record type - caused by SELECT order ???

From: John Cobo <johnecobo(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Wrong record type - caused by SELECT order ???
Date: 2006-10-27 21:35:06
Message-ID: 20061027213506.29671.qmail@web52611.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-general
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 

Responses

pgsql-general by date

Next:From: Alvaro HerreraDate: 2006-10-27 21:44:52
Subject: Re: Mailing list problem?
Previous:From: Richard Broersma JrDate: 2006-10-27 21:28:08
Subject: Re: pg_dumpall failing from possible corrupted shared memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group