Search Postgresql Archives

Re: strange behavior of plpgsql function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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:
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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux