PL / pgSQL函数：如何使用execute语句返回表

发布时间：2020-12-13 16:28:21 所属栏目：百科来源：网络整理

导读：我有这个PL / pgSQL函数,它必须返回一些用户的信息. CREATE OR REPLACE FUNCTION my_function(user_id integer) RETURNS TABLE(id integer,firstname character varying,lastname character varying) AS$$DECLARE ids character varying;BEGIN ids := ''; --

我有这个PL / pgSQL函数,它必须返回一些用户的信息.

CREATE OR REPLACE FUNCTION my_function(user_id integer)
  RETURNS TABLE(id integer,firstname character varying,lastname  character varying) AS
$$
DECLARE
    ids character varying;
BEGIN
    ids := '';

    --Some code which build the ids string,not interesting for this issue

    RETURN QUERY EXECUTE 'SELECT users.id,users.firstname,users.lastname
    FROM public.users WHERE ids IN (' || ids || ')';
END;
$$LANGUAGE plpgsql;

我所面临的问题是函数的结果是单列表,如下所示：

╔═══╦═════════════════════╗
║   ║my_function          ║
╠═══╬═════════════════════╣
║ 1 ║ (106,Ned,STARK)     ║
║ 2 ║ (130,Rob,STARK)     ║
╚═══╩═════════════════════╝

虽然我预计：

╔═══╦════════════╦════════════╦═════════════╗
║   ║ id         ║ firstname  ║ lastname    ║
╠═══╬════════════╬════════════╬═════════════╣
║ 1 ║ 106        ║ Ned        ║ STARK       ║
║ 2 ║ 103        ║ Rob        ║ STARK       ║
╚═══╩════════════╩════════════╩═════════════╝

我想(但不确定)问题来自EXECUTE语句,但我看不到如何做到.

有任何想法吗？

你如何执行这个功能？它作为一个选择语句.

create table public.users (id int,firstname varchar,lastname varchar);

insert into public.users values (1,'aaa','bbb'),(2,'ccc','ddd');

CREATE OR REPLACE FUNCTION my_function(user_id integer) RETURNS TABLE(id integer,lastname character varying) AS $$
DECLARE
    ids INTEGER[];
BEGIN
     ids := ARRAY[1,2];
     RETURN QUERY
         SELECT users.id,users.lastname
         FROM public.users
         WHERE users.id = ANY(ids);
END;
$$LANGUAGE plpgsql;


select * from my_function(1);
 id | firstname | lastname 
----+-----------+----------
  1 | aaa       | bbb
  2 | ccc       | ddd

select id,firstname,lastname from my_function(1);
 id | firstname | lastname 
----+-----------+----------
  1 | aaa       | bbb
  2 | ccc       | ddd

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!