BUG #5753: Existing Functions No Longer Work - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5753
Logged by:          Vince Maxey
Email address:      vamax27@yahoo.com
PostgreSQL version: 9.0
Operating system:   Windows
Description:        Existing Functions No Longer Work
Details:

Hi,

Recently I upgraded a personal application built a number of years ago,
including java, eclipse, struts and postgresql and now face an issue with
postgresql in that application functions no longer work, specfically as
related to refcursors.  The original application was based on postgresql 8.4
I believe.

I have pgAdminIII installed and have been searching hi and low on the
Internet for others who may have faced similar issues; and I cannot find any
documentation which indicates specifically how to write a function (if the
syntax has changed for 9.0).

Here is a test set up I have created:

CREATE TABLE test_user
(
userid int4 NOT NULL,
userlogin varchar(20) NOT NULL,
userlevel int2 DEFAULT 0,
activeflag bool DEFAULT true,
username varchar(50),
enteredby int4,
CONSTRAINT test_user_pk PRIMARY KEY (userid)
)
WITH OIDS;
ALTER TABLE test_user OWNER TO postgres;
GRANT ALL ON TABLE test_user TO postgres;
GRANT ALL ON TABLE test_user TO public;


insert into test_user values (1,'A',1,'f','test1',1);
insert into test_user values (2,'B',1,'t','test2',1);
insert into test_user values (3,'C',1,'t','test3',2);
insert into test_user values (4,'Ad',1,'f','test4',1);

CREATE OR REPLACE FUNCTION test_proc(bigint)
RETURNS refcursor AS
$BODY$
DECLARE

userinfo refcursor;

BEGIN

open userinfo for

select * from test_user where userid = $1;

return userinfo;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION test_proc(bigint) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION test_proc(bigint) TO public;
GRANT EXECUTE ON FUNCTION test_proc(bigint) TO postgres;

This SQL works fine: select * from test_user where userid = 2;

But when I try to call the function: select test_proc(2); I get a column
header: test_proc refcursor and the value in this column is simply: <unnamed
portal n>, where n seems to indicate how many times I have run a cursor from
the SQL window.

I also created this function:

CREATE OR REPLACE FUNCTION test_proc1(bigint)
RETURNS int4 AS
$BODY$
DECLARE

rec int4;

BEGIN

select enteredby into rec from test_user where userid = $1;
return rec;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION test_proc1(bigint) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION test_proc1(bigint) TO public;
GRANT EXECUTE ON FUNCTION test_proc1(bigint) TO postgres;

These statements return correct values: select test_proc1(2); or select
test_proc1(3);

I used refcursors throughout my application and was surprised to be having
issues with them simply for moving to 9.0

Has this issue been encountered and already addressed? and/or is there
documentation in regard to modifying functions in relation to this release?

I did see something in regard to %rowtype, but I have numerous queries based
on multiple table joins and it doesn't make sense that I should have to
define datatypes for every individual query...

Any help or direction to existing documentation would be greatly
appreciated.

thank you very much!