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

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
theplace of double precision columns. But in database those values are set. Simple SELECT (select * from service) from
thistable works propertly - double precision columns have their proper values.
 

I am working with 7.3.2 version of Postgresql.

Sample Code
CREATE 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';


No file was uploaded with this report

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