Re: Bug #915: problem with returning setof with double precision

pgsql-bugs(at)postgresql(dot)org wrote:
> Daniel Brozek (d(dot)brozek(at)adv(dot)pl) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description problem with returning setof with double precision
> values
>
> Long Description I have got the table and the function (look at
> example code). After executing this function I have got NULL values
> in the place of double precision columns. But in database those
> values are set. Simple SELECT (select * from service) from this table
> works propertly - double precision columns have their proper values.
>
> I am working with 7.3.2 version of Postgresql.

Can you send a complete example? We need minimal table definition and
sample data that reliably reproduces the problem. Also what OS and compiler?

I'm not able to reproduce the problem here on 7.3.2 or cvs tip (see below):

CREATE TABLE service(
service_id int,
val float8
);

insert into service values(1,1.23);
insert into service values(2,2.34);

CREATE OR REPLACE FUNCTION get_krd_info(INTEGER) RETURNS SETOF service AS '
DECLARE
l_service_id ALIAS FOR $1;
l_service service%ROWTYPE;
BEGIN
SELECT INTO l_service service.* FROM service
WHERE service.service_id = l_service_id;
RETURN NEXT l_service;

RETURN;
END;
' LANGUAGE 'plpgsql';

regression=# select * from get_krd_info(1);
service_id | val
------------+------
1 | 1.23
(1 row)

regression=# select * from get_krd_info(2);
service_id | val
------------+------
2 | 2.34
(1 row)

BTW, there's no reason to declare that function to return SETOF unless
you loop through the results. As declared, it will never return more
than one row.

test=# insert into service values(1,3.45);
INSERT 14266713 1
test=# select * from service;
service_id | val
------------+------
1 | 1.23
2 | 2.34
1 | 3.45
(3 rows)

test=# select * from get_krd_info(1);
service_id | val
------------+------
1 | 1.23
(1 row)

You may as well define it as:

CREATE OR REPLACE FUNCTION get_krd_info(INTEGER) RETURNS service AS '
DECLARE
l_service_id ALIAS FOR $1;
l_service service%ROWTYPE;
BEGIN
SELECT INTO l_service service.* FROM service
WHERE service.service_id = l_service_id;

RETURN l_service;
END;
' LANGUAGE 'plpgsql';

Joe