strange behavior of plpgsql function

From: c k <shreeseva(dot)learning(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: strange behavior of plpgsql function
Date: 2009-04-04 09:29:03
Message-ID: d8e7a1e30904040229v693ee682o7543293b42b43f11@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2009-04-04 11:16:39 Re: high load on server
Previous Message Piotr Buczek 2009-04-04 08:55:27 deadlock in trigger before insert