如何在PostgreSQL 9中从函数返回表格、行或记录？

Question

我有一个名为person的表，其中包含id、name、status，我想通过一个带有1个参数（name）的函数返回行作为结果。 有人可以帮助我吗？请简单说明，因为我在PostgreSQL中非常菜鸟。

这是我的普通函数代码

create or replace function fn_list(vname varchar) returns void as $$
begin
    SELECT id,name,status from usuario WHERE name= vname;
end;
$$ language plpgsql;

我知道我正在返回一个空函数，但如果我想要一行的列表，该怎么办？

- MitoCode

Answer 1

使用循环返回查询结果是缓慢和低效的。PL/pgSQL 的开销甚至不是必需的。

最好的解决方案是:

create or replace function fn_list(vname varchar) 
  returns table(id integer, name text, status text) 
as $$
  SELECT id,name,status 
  from usuario 
  WHERE name= vname;
$$ language sql;

如果需要使用PL/pgSQL是因为其他过程性代码需要在查询之前运行，那么应该使用return query而不是循环。

create or replace function fn_list(vname varchar) 
  returns table(id integer, name text, status text) 
as $$
begin
  -- do some work....
  return query
    SELECT id,name,status 
    from usuario 
    WHERE name= vname;
end;
$$ language plpgsql;

然后使用以下方式进行调用：

select *
from fn_list('Arthur');

- user330315

---

很棒的回答。同样重要的是在FROM子句中调用表函数，就像上面展示的那样，而不是在SELECT子句中。以下代码可以编译通过，但可能会产生不可预测的结果：SELECT fn_list('Arthur') FROM some_table - undefined

Answer 2

我知道在Oracle中，使用流水线返回是这样做的，因此我用它来查找plpgsql中的'RETURN NEXT'语句。这个链接(http://www.postgresql.org/message-id/007b01c6dc31$ae395920$[e-mail])和这个网站(http://grokbase.com/t/postgresql/pgsql-performance/069kcttrfr/pipelined-functions-in-postgres)上也有相关信息。 （编辑以添加官方文档）：http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html。 很棒，我自己将不得不利用这一点。 再次编辑以添加一些演示代码(直接摘自postgresql.org文档)：

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN SELECT * FROM foo
    WHERE fooid > 0
    LOOP
        -- can do some processing here
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;

SELECT * FROM getallfoo();

- Horus

---

我得到的结果是(1，name1，status1)，这个值是文本吗？ - MitoCode

我想要的结果是这样的：第一列：id，第二列：name，第三列：status……不是一个包含所有值用“,”分隔的单列。请帮帮我。 - MitoCode

抱歉，我没有看到最后一行 select * from getAllfoo(); 谢谢，它可以工作。 - MitoCode

抱歉，我错过了你所有的回复，我正在床上。 - Horus

Answer 3

许多答案在使用函数方面省略了重要部分。以下是一种在Postgres中使用函数的更新方法（包括声明、变量、参数、返回值和运行）。下面是一个过度精简的示例，演示如何将右下角“blurb”推文更新为“hello world”。

id (serial)	pub_id (text)	tweet (text)
1	abc	hello world
2	def	blurb

-- Optional drop if replace fails below.
drop function if exists sync_tweets(text, text);

create or replace function sync_tweets(
    src_pub_id text, -- function arguments
    dst_pub_id text
) returns setof tweets as -- i.e. rows. int, text work too
$$
declare
    src_id    int; -- temp function variables (not args)
    dest_id   int;
    src_tweet text;
begin
    -- query result into a temp variable
    src_id := (select id from tweets where pub_id = src_pub_id);

    -- query result into a temp variable (another way)
    select tweet into src_tweet from tweets where id = src_id;

    dest_id := (select id from tweets where pub_id = dst_pub_id);
    update tweets set tweet=src_tweet where id = dest_id;

    return query -- i.e. rows, return 0 with return int above works too
        select * from tweets where pub_id in (src_pub_id, dst_pub_id);
end
$$ language plpgsql; -- need the language to avoid ERROR 42P13

-- Run it!
select * from sync_tweets('abc', 'def');

/*
  Outputs
   __________________________________________________ 
  |  id (serial)  |  pub_id (text)  |  tweet (text)  |
  |---------------|-----------------|----------------|
  |  1            |  abc            |  hello world   |
  |  2            |  def            |  blurb         |
  --------------------------------------------------
*/

- Josh Hibschman