Postgres Function Returning One Record While I Have Many Records?

I have many records which my simple query returning but when i use function it just gives me first record, firstly i create my own data type using, CREATE TYPE my_type (usr_id inte

Solution 1:

To return set of composite type from plpgsql function you should:

• declare function's return type as setof composite_type,
• use return query (or return next) instruction (documentation).

I have edited your code only in context of changing return type (it is an example only):

DROPfunction test();   -- to change the return type one must drop the functionCREATEOR 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 ISNOTNULLthen
        radiophone_clause ='and pp.radio_phone = '|| quote_literal(rd);
    END IF;

    IF personphone ISNOTNULLthen      
        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;

Solution 2:

You need to return setof my_type and if I understand what you want you don't need dynamic SQL

createor replace function test() returns setof my_type as $$
declare
    rd varchar :='21';
    personphone varchar :=NULL;
beginreturn query
    select pt.id, pt.name
    from
        product_template pt
        innerjoin
        product_product pp using(id)
    where
        (pp.radio_phone = rd or rd isnull)
        and
        (pp.person_phone = personphone or personphone isnull)
    ;
end;
$$ language plpgsql;

And if you pass the parameters it can be plain sql

createor replace function test(rd varchar, personphone varchar)
returns setof my_type as $$
    select pt.id, pt.name
    from
        product_template pt
        innerjoin
        product_product pp using(id)
    where
        (pp.radio_phone = rd or rd isnull)
        and
        (pp.person_phone = personphone or personphone isnull)
    ;
$$ languagesql;