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

前端之家收集整理的这篇文章主要介绍了PL / pgSQL函数:如何使用execute语句返回表前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有这个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

猜你在找的Postgre SQL相关文章