Postgresql 存储过程返回表所有列 [英] Postgresql stored procedure return table all columns

问题描述

创建了一个函数.该函数有一个输入参数.我可以返回一列，但我想返回所有表列.另外我想做的是，如果结果为零，函数只返回 0.我该怎么做?这里是错误结果.

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.

错误:查询没有结果数据的目的地提示:如果您想丢弃 SELECT 的结果，请改用 PERFORM.上下文:PL/pgSQL 函数 dwgcould.returnallcolumns(character variables) 在 SQL 语句的第 3 行********** 错误 **********错误:查询没有结果数据的目的地SQL 状态:42601提示:如果您想丢弃 SELECT 的结果，请改用 PERFORM.上下文:PL/pgSQL 函数 dwgcould.returnallcolumns(character variables) 在 SQL 语句的第 3 行

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

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;

推荐答案

如果要返回结果，需要在PL/pgSQL中使用return query如手册中所述

If you want to return a result, you need to use return query in PL/pgSQL as documented in the manual

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;

但是您不需要为此使用 PL/pgSQL，一个简单的 SQL 函数会更高效:

But you don't need PL/pgSQL for this, a simple SQL function will be more efficient:

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;

这篇关于Postgresql 存储过程返回表所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！