Обсуждение: pl/pgsql problem with return types
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 P RECORD; 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_pkeyPRIMARY 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
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
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 > >