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

pgsql-bugs@postgresql.org writes:

SELECT INTO l_service service* FROM service
WHERE service.service_id = l_service_id;

Shouldn't that just be

SELECT INTO l_service * FROM service
WHERE service.service_id = l_service_id;

Or possibly

SELECT INTO l_service service.* FROM service
WHERE service.service_id = l_service_id;

but what you wrote looks like a syntax error to me.

regards, tom lane

pgsql-bugs@postgresql.org wrote:

Daniel Brozek (d.brozek@adv.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