strange behavior of plpgsql function

Started by c kabout 17 years ago3 messagesgeneral

shreeseva.learning@gmail.com

about 17 years ago

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

Justin

justin@emproshunts.com

about 17 years ago

In reply to: c k (#1)

Re: strange behavior of plpgsql function

<!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">
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 
 
</blockquote>
</body>
</html>

Merlin Moncure

mmoncure@gmail.com

about 17 years ago

In reply to: Justin (#2)

Re: strange behavior of plpgsql function

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