<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<font size="+1"><font face="Arial">I think i may be the way the
function is being called??<br>
<br>
if you are doing Select fnvs.docrelatedassociatedetails()<br>
<br>
it will not return any records, it needs to be<br>
<br>
</font></font><font size="+1"><font face="Arial">Select * From
fnvs.docrelatedassociatedetails()</font></font><br>
<br>
c k wrote:
<blockquote
cite="mid:d8e7a1e30904040229v693ee682o7543293b42b43f11(at)mail(dot)gmail(dot)com"
type="cite">Hi all,<br>
I am facing a small but strange problem when using a plpgsql function
as below.<br>
<br>
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)<br>
RETURNS SETOF record AS<br>
$BODY$<br>
begin<br>
return query SELECT docid, associateid, addressline1, addressline2,
addressline3, city, state, country, postalcode, addtype from
docrelatedassociates where docid=p_docid and addtype=p_addtype;<br>
return;<br>
end;<br>
$BODY$<br>
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER<br>
COST 1<br>
ROWS 10;<br>
<br>
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. <br>
<br>
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)<br>
RETURNS SETOF record AS<br>
$BODY$<br>
SELECT docid, associateid, addressline1, addressline2,
addressline3, city, state, country, postalcode, addtype from
docrelatedassociates where docid=$1 and addtype=$2::smallint;<br>
$BODY$<br>
LANGUAGE 'sql' VOLATILE SECURITY DEFINER<br>
COST 1<br>
ROWS 10;<br>
<br>
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.<br>
<br>
Thanks,<br>
<br>
CPK<br>
<br>
</blockquote>
</body>
</html>