Postgresql存储过程返回表所有列

发布时间：2020-12-13 18:07:37 所属栏目：百科来源：网络整理

导读：创建一个函数.该函数有一个输入参数.我可以返回一列,但我想返回所有表列.另外我想做的是如果结果为零,则函数返回0.我怎么能这样做？这里是错误结果. ERROR: query has no destination for result data HINT: If you want to discard the results of a SELEC

创建一个函数.该函数有一个输入参数.我可以返回一列,但我想返回所有表列.另外我想做的是如果结果为零,则函数返回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

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 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函数会更有效：

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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!