PostgreSQL 函数选择*
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/741616/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL functions select*
提问by Markus
How can I use:
我该如何使用：
select * from <some_table>;
in a FUNCTIONin Postgres?
在FUNCTIONPostgres里？
回答by Luc M
CREATE OR REPLACE FUNCTION my_function() RETURNS INTEGER AS '
DECLARE
your_record your_table%ROWTYPE;
BEGIN
FOR your_record IN SELECT * FROM your_table
LOOP
--
-- You can access fields of your table using .
-- your_record.your_field
...
END LOOP;
END;
' LANGUAGE 'plpgsql'
STABLE;
or
或者
CREATE OR REPLACE FUNCTION my_function() RETURNS INTEGER AS '
DECLARE
your_record your_table%ROWTYPE;
BEGIN
SELECT * INTO your_record FROM your_table;
--
-- You can access fields of your table using .
-- your_record.your_field
END;
' LANGUAGE 'plpgsql'
STABLE;
EDIT:
编辑：
With join returning a record:
使用 join 返回记录：
CREATE OR REPLACE FUNCTION my_function() RETURNS SETOF record AS '
DECLARE
your_record record;
BEGIN
--
-- You should specify a list of fields instead of *
--
FOR your_record IN SELECT * FROM your_table INNER JOIN ...
RETURN NEXT your_record;
END LOOP;
END;
' LANGUAGE 'plpgsql'
STABLE;
To use my_function(), you have to specify fields and datatypes: See details here
要使用 my_function()，您必须指定字段和数据类型： 在此处查看详细信息
回答by jperelli
There is a smipler method, if you want to use SQL in the function, use SQL language in the function:
有一个更简单的方法，如果你想在函数中使用SQL，在函数中使用SQL语言：
CREATE FUNCTION getallzipcodes()
RETURNS SETOF zip AS
$BODY$
SELECT * FROM zip;
$BODY$
LANGUAGE 'sql';
And this might be useful too (how to call the function)
这也可能很有用（如何调用函数）
SELECT function_returning_setof(); -- Wrong!
SELECT * FROM function_returning_setof(); -- OK!
SELECT function_returning_scalar(); -- OK
