Postgres function returns one record, but I have many records?

Question

I have many records that my simple query returns, but when I use the function, it just gives me the first record,

firstly i create my own data type using

CREATE TYPE my_type (usr_id integer , name varchar(30));

and my function

CREATE OR REPLACE function test() returns my_type as $$
    declare rd varchar := '21';
    declare personphone varchar := NULL;
    declare result my_type;
    declare SQL VARCHAR(300):=null; 
DECLARE
    radiophone_clause text = '';

BEGIN        
    IF rd IS NOT NULL then
        radiophone_clause = 'and pp.radio_phone = '|| quote_literal(rd);
    END IF;

    IF personphone IS NOT NULL then      
        radiophone_clause = radiophone_clause|| 'and pp.person_phone = '|| quote_literal(personphone);
    END IF;
    radiophone_clause = substr(radiophone_clause, 5, length(radiophone_clause)- 4);

     EXECUTE format('select pt.id,pt.name from product_template pt inner join product_product pp on pt.id=pp.id where %s ;', radiophone_clause) into result.id,result.name ;
    return result;
END;
$$ LANGUAGE plpgsql;

in this function, I return my_type, which returns only the first row, how to return more than one row,

Answer 1

To return a composite type set from the plpgsql function, you must:

• declare the return type of the function as setof composite_type,
• return query ( return next) ().

( ):

DROP function test();   -- to change the return type one must drop the function

CREATE OR REPLACE function test() 
-- returns my_type as $$
returns setof my_type as $$                    -- (+)
    declare rd varchar := '21';
    declare personphone varchar := NULL;
--    declare result my_type;
--    declare SQL VARCHAR(300):=null; 
DECLARE
    radiophone_clause text = '';

BEGIN        
    IF rd IS NOT NULL then
        radiophone_clause = 'and pp.radio_phone = '|| quote_literal(rd);
    END IF;

    IF personphone IS NOT NULL then      
        radiophone_clause = radiophone_clause|| 'and pp.person_phone = '|| quote_literal(personphone);
    END IF;
    radiophone_clause = substr(radiophone_clause, 5, length(radiophone_clause)- 4);

    RETURN QUERY                               -- (+)
    EXECUTE format('select pt.id,pt.name from product_template pt inner join product_product pp on pt.id=pp.id where %s ;', radiophone_clause)
    ;                                          -- (+)
--    into result.id,result.name;
--    return result;
END;
$$ LANGUAGE plpgsql;

Answer 2

setof my_type, , , SQL

create or replace function test() returns setof my_type as $$
declare
    rd varchar := '21';
    personphone varchar := NULL;
begin
    return query
    select pt.id, pt.name
    from
        product_template pt
        inner join
        product_product pp using(id)
    where
        (pp.radio_phone = rd or rd is null)
        and
        (pp.person_phone = personphone or personphone is null)
    ;
end;
$$ language plpgsql;

, sql

create or replace function test(rd varchar, personphone varchar)
returns setof my_type as $$
    select pt.id, pt.name
    from
        product_template pt
        inner join
        product_product pp using(id)
    where
        (pp.radio_phone = rd or rd is null)
        and
        (pp.person_phone = personphone or personphone is null)
    ;
$$ language sql;