我有很多记录时Postgres函数返回一条记录吗? [英] 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 integer , name varchar(30));

我的功能是

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;

在此函数中，我返回的是my_type，该类型仅返回第一行，然后返回多一行，

in this function i am returning my_type which return only first row how to return more then one row,

推荐答案

要从plpgsql函数返回复合类型集，您应该:

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

• 将函数的返回类型声明为setof composite_type，
• 使用return query(或return next)指令(文档).

• 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):

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;

这篇关于我有很多记录时Postgres函数返回一条记录吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！