Thread: strange behavior of plpgsql function
Hi all,
I am facing a small but strange problem when using a plpgsql function as below.
CREATE OR REPLACE FUNCTION fnvs.docrelatedassociatedetails(p_docid integer, p_addtype smallint, p_associateid integer, OUT docid integer, OUT associateid integer, OUT addressline1 varchar,OUT addressline2 varchar,OUT addressline3 varchar,OUT city varchar,OUT state varchar,OUT country varchar,OUT postalcode varchar, OUT addtype smallint)
RETURNS SETOF record AS
$BODY$
begin
return query SELECT docid, associateid, addressline1, addressline2, addressline3, city, state, country, postalcode, addtype from docrelatedassociates where docid=p_docid and addtype=p_addtype;
return;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
COST 1
ROWS 10;
Above function was first written to return a set of records as same as a table, but it didn't worked. Then I written above code and it works but does written any data. When the same function is written using SQL as language it works well. even from above function, it I call the newly written sql langauge function it returns data correctly. What will be the problem.
CREATE OR REPLACE FUNCTION software.docrelatedassociatedetails(p_docid integer, p_addtype smallint, p_associateid integer, OUT docid integer, OUT associateid integer, OUT addressline1 varchar,OUT addressline2 varchar,OUT addressline3 varchar,OUT city varchar,OUT state varchar,OUT country varchar,OUT postalcode varchar, OUT addtype smallint)
RETURNS SETOF record AS
$BODY$
SELECT docid, associateid, addressline1, addressline2, addressline3, city, state, country, postalcode, addtype from docrelatedassociates where docid=$1 and addtype=$2::smallint;
$BODY$
LANGUAGE 'sql' VOLATILE SECURITY DEFINER
COST 1
ROWS 10;
Above function was created few days ago and then I changed the structure of the table which is used to return setof records. After that this problem is occuring.
Thanks,
CPK
I am facing a small but strange problem when using a plpgsql function as below.
CREATE OR REPLACE FUNCTION fnvs.docrelatedassociatedetails(p_docid integer, p_addtype smallint, p_associateid integer, OUT docid integer, OUT associateid integer, OUT addressline1 varchar,OUT addressline2 varchar,OUT addressline3 varchar,OUT city varchar,OUT state varchar,OUT country varchar,OUT postalcode varchar, OUT addtype smallint)
RETURNS SETOF record AS
$BODY$
begin
return query SELECT docid, associateid, addressline1, addressline2, addressline3, city, state, country, postalcode, addtype from docrelatedassociates where docid=p_docid and addtype=p_addtype;
return;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
COST 1
ROWS 10;
Above function was first written to return a set of records as same as a table, but it didn't worked. Then I written above code and it works but does written any data. When the same function is written using SQL as language it works well. even from above function, it I call the newly written sql langauge function it returns data correctly. What will be the problem.
CREATE OR REPLACE FUNCTION software.docrelatedassociatedetails(p_docid integer, p_addtype smallint, p_associateid integer, OUT docid integer, OUT associateid integer, OUT addressline1 varchar,OUT addressline2 varchar,OUT addressline3 varchar,OUT city varchar,OUT state varchar,OUT country varchar,OUT postalcode varchar, OUT addtype smallint)
RETURNS SETOF record AS
$BODY$
SELECT docid, associateid, addressline1, addressline2, addressline3, city, state, country, postalcode, addtype from docrelatedassociates where docid=$1 and addtype=$2::smallint;
$BODY$
LANGUAGE 'sql' VOLATILE SECURITY DEFINER
COST 1
ROWS 10;
Above function was created few days ago and then I changed the structure of the table which is used to return setof records. After that this problem is occuring.
Thanks,
CPK
I think i may be the way the function is being called?? if you are doing Select fnvs.docrelatedassociatedetails() it will not return any records, it needs to be Select * From fnvs.docrelatedassociatedetails() c k wrote: <blockquote cite="mid:d8e7a1e30904040229v693ee682o7543293b42b43f11@mail.gmail.com" type="cite">Hi all, I am facing a small but strange problem when using a plpgsql function as below. CREATE OR REPLACE FUNCTION fnvs.docrelatedassociatedetails(p_docid integer, p_addtype smallint, p_associateid integer, OUT docid integer, OUT associateid integer, OUT addressline1 varchar,OUT addressline2 varchar,OUT addressline3 varchar,OUT city varchar,OUT state varchar,OUT country varchar,OUT postalcode varchar, OUT addtype smallint) RETURNS SETOF record AS $BODY$ begin return query SELECT docid, associateid, addressline1, addressline2, addressline3, city, state, country, postalcode, addtype from docrelatedassociates where docid=p_docid and addtype=p_addtype; return; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER COST 1 ROWS 10; Above function was first written to return a set of records as same as a table, but it didn't worked. Then I written above code and it works but does written any data. When the same function is written using SQL as language it works well. even from above function, it I call the newly written sql langauge function it returns data correctly. What will be the problem. CREATE OR REPLACE FUNCTION software.docrelatedassociatedetails(p_docid integer, p_addtype smallint, p_associateid integer, OUT docid integer, OUT associateid integer, OUT addressline1 varchar,OUT addressline2 varchar,OUT addressline3 varchar,OUT city varchar,OUT state varchar,OUT country varchar,OUT postalcode varchar, OUT addtype smallint) RETURNS SETOF record AS $BODY$ SELECT docid, associateid, addressline1, addressline2, addressline3, city, state, country, postalcode, addtype from docrelatedassociates where docid=$1 and addtype=$2::smallint; $BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER COST 1 ROWS 10; Above function was created few days ago and then I changed the structure of the table which is used to return setof records. After that this problem is occuring. Thanks, CPK
On Sat, Apr 4, 2009 at 10:07 AM, Justin <justin@emproshunts.com> wrote: > I think i may be the way the function is being called?? > > if you are doing Select fnvs.docrelatedassociatedetails() > > it will not return any records, it needs to be > > Select * From fnvs.docrelatedassociatedetails() select func(); will return a set of composite type for sql functions, and error for plpgsql functions < 8.4. Starting with 8.4, plpgsql functions will work as sql functions do. merlin