PostgreSQL returns only one row when using custom types

Question

I have a plpgsql function that returns a custom type (pseudocode):

CREATE OR REPLACE FUNCTION my_function(entity_id integer)
  RETURNS "CustomType" AS
$BODY$

DECLARE 
    result "CustomType";        
BEGIN

    SELECT 
    INTO result
        T."Column1" AS "Column1", 
        T."Column2" AS "Column2"                        
    FROM "Table1" T 
    WHERE T."EntityId" = entity_id 

--do other stuff here before returning

RETURN QUERY 
SELECT 
        result."Column1",
        result."Column2"
END;

$BODY$
LANGUAGE plpgsql VOLATILE

The first problem is that the function returns an empty row (all values empty) even if the select statement return nothing (entity_id doesn't exist). I use Dapper to map the result to an object and I need to know if the object is found or not (NULL or not).

The second problem is that this function always returns only one row even if for example I remove the WHERE clause. If I change the function signature to directly return query from select and remove the local "CustomType" variable then multiple rows are returned, so it works as expected.

Michael Buen Michael Buen · Accepted Answer · 2015-10-16T01:17:13

Use SETOF and % ROWTYPE combination:

/*


drop function my_function(int);

drop table "Table1";

drop type "CustomType";
*/

create type "CustomType" as ("Column1" int, "Column2" int);

create table "Table1"(a int, b int);

insert into "Table1"(a,b) values
(1,2),
(3,4),
(5,6);


CREATE OR REPLACE FUNCTION my_function(entity_id integer)
  RETURNS SETOF "CustomType" as
$$
DECLARE 
    result "CustomType" % rowtype;

    singleRow "CustomType";
BEGIN



    FOR RESULT IN EXECUTE 'SELECT 
        t.a,
        t.b
    FROM "Table1" t
    where t.a>= ' || entity_id  LOOP

        RETURN NEXT RESULT;

    END LOOP;

--do other stuff here before returning

    singleRow."Column1" := 7;
    singleRow."Column2" := 6;

    return next singleRow;


    RETURN;

END
$$
LANGUAGE plpgsql VOLATILE;


select * from my_function(3)

PostgreSQL returns only one row when using custom types

1 Answers