Postgresql 存储过程返回表所有列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42579099/
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 stored procedure return table all columns
提问by Fatih
A function is created. The function has a input parameter. I can return a column but I want to return all table columns. Also I want to do if result is zero the function return just 0. How can I do it? Here the error result.
创建了一个函数。该函数有一个输入参数。我可以返回一列，但我想返回所有表列。如果结果为零，我也想做，函数只返回 0。我该怎么做？这里的错误结果。
ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function dwgcould.returnallcolumns(character varying) line 3 at SQL statement ********** Error ********** ERROR: query has no destination for result data SQL state: 42601 Hint: If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function dwgcould.returnallcolumns(character varying) line 3 at SQL statement
错误：查询没有结果数据的目的地 提示：如果您想放弃 SELECT 的结果，请改用 PERFORM。上下文：PL/pgSQL 函数 dwgcould.returnallcolumns(character variables) 第 3 行在 SQL 语句 ********** 错误 ********** 错误：查询没有结果数据 SQL 状态的目标: 42601 提示：如果要丢弃 SELECT 的结果，请改用 PERFORM。上下文：PL/pgSQL 函数 dwgcould.returnallcolumns(character variables) 第 3 行在 SQL 语句
CREATE OR REPLACE FUNCTION dwgcould.returnallcolumns(IN sessionId character varying)
RETURNS SETOF public.mytable AS
$BODY$
BEGIN
SELECT * FROM public.mytable WHERE session_id=returnallcolumns.sessionId ORDER BY pro_id DESC LIMIT 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
回答by a_horse_with_no_name
If you want to return a result, you need to use return queryin PL/pgSQL as documented in the manual
如果要返回结果，则需要按照手册中的说明在return queryPL/pgSQL中使用
CREATE OR REPLACE FUNCTION dwgcould.returnallcolumns(IN sessionId character varying)
RETURNS SETOF public.mytable AS
$BODY$
BEGIN
return query --<< this was missing
SELECT *
FROM public.mytable
WHERE session_id = returnallcolumns.sessionId
ORDER BY pro_id DESC LIMIT 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
But you don't need PL/pgSQL for this, a simple SQL function will be more efficient:
但是您不需要为此使用 PL/pgSQL，一个简单的 SQL 函数会更有效：
CREATE OR REPLACE FUNCTION dwgcould.returnallcolumns(IN sessionId character varying)
RETURNS SETOF public.mytable AS
$BODY$
SELECT *
FROM public.mytable
WHERE session_id = returnallcolumns.sessionId
ORDER BY pro_id DESC LIMIT 1;
$BODY$
LANGUAGE sql;
