[SQL] pl/pgsql problem with return types

[Juris Zeltins]

Hello!
i have problem with pl/pgsql function;

===
ERROR:  wrong record type supplied in RETURN NEXT
CONTEXT:  PL/pgSQL function "tests" line 6 at return next
===
-- Function: tests(int8)
-- DROP FUNCTION tests(int8);
CREATE OR REPLACE FUNCTION tests(int8)
 RETURNS SETOF pages AS
$BODY$DECLARE
   PRECORD;
BEGIN
   FOR P IN select pageid from pages
   LOOP
   RETURN NEXT P;
   END LOOP;
   RETURN;
END;$BODY$
 LANGUAGE 'plpgsql' STABLE;
ALTER FUNCTION tests(int8) OWNER TO diglat_web;

-- Table: pages
-- DROP TABLE pages;
CREATE TABLE pages
(
 pageid int8 NOT NULL,
 ppageid int8 NOT NULL DEFAULT 0,
 name varchar(100),
 status int4 DEFAULT 0,
 CONSTRAINT pages_pkey PRIMARY KEY (pageid),
 CONSTRAINT pages_in_pages_fkey FOREIGN KEY (ppageid) REFERENCES pages 
(pageid) ON UPDATE RESTRICT ON DELETE RESTRICT,
 CONSTRAINT pages_uniq UNIQUE (pageid, ppageid)
)
WITH OIDS;


Actualy function is correct... and the same code run successfully on 
other table.. there is the problem ?

Thanks
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] pl/pgsql problem with return types

[Juris Zeltins]

Yep.. i have solved this problem by specifying
the correct return type and variable type (should be the same)
but as said, in some cases pl_exec executes with type conversion.
as real example - i have :
return type = SETOF new type  "category_node(catid, pcatid)"
variable = R, SR -> RECORD
and
FOR R IN select * from ...
LOOP
  RETURN NEXT R;  /* add  RECORD to SETOF_of_CATEGORY_NODE */

and works...
the problem seems is teh same - on altered tables there is some porblem 
with this :)

Actualy - return type & variable in "RETURN NEXT" must be the same type.
// Solved
John DeSoi wrote:
On Mar 11, 2005, at 5:54 AM, Juris Zeltins wrote:
   FOR P IN select pageid from pages
This way you are only getting the pageid column. I think what you want is
FOR P in select * from pages
so that P contains the complete pages record.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]