如何从PostgreSQL存储过程中获取结果集? [英] How to get result set from PostgreSQL stored procedure?

问题描述

我在PostgreSQL 11中创建了一个存储过程来执行CRUD操作，它可以很好地工作于1.创建2.更新3.删除，但是当我通过将 Condition = 4 传递给选择一个结果集，我得到以下错误.

I created a stored procedure in PostgreSQL 11 to perform CRUD operation, and it works fine for 1. Create 2. Update 3. Delete, but while I run read command by passing Condition = 4 to select a result set, I get below error.

我已经使用PostgreSQL函数来获取对我有用的结果集，但是我需要使用PostgreSQL存储过程来获取结果.

I have used PostgreSQL function to get result set it works for me, but I need to get result using the PostgreSQL stored procedure.

这是我的存储过程代码:

Here is my code for stored procedure:

CREATE OR REPLACE PROCEDURE public.testSpCrud(
    fnam text,
    lnam text,
    id integer,
    condition integer)
LANGUAGE 'plpgsql'

AS $BODY$
declare
    countOfDisc int; 
BEGIN
if condition=1 then

INSERT INTO public.employee(
    employeeid, lname, fname, securitylevel, employeepassword, hphonearea, hphone, cphonearea, cphone, street, city, state, zipcode, extzip, name, email, groomerid, type, commission, inactive, carrierid, notoallemployees, languageid, isdogwalker, ispetsitter, ismobilegroomer, ssma_timestamp)
    VALUES (4,  'Test', 'Test', 2, 2, 32, 32, 32, 32, 32, 32,32, 32, 32, 22, 22, 2, 2, 2, false, 223,true, 223, true, true, true, '2019-08-27');
end if;
 if condition =2 then
    delete from Employee where employeeid=id;
    end if;
     if condition =3 then
    update Employee set fname='Test' where employeeid=id;
    end if;
     if condition =4 then
         Select * from Employee;
    end if;
    END;
$BODY$;

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 testspcrud(text,text,integer,integer) line 22 at SQL statement
SQL state: 42601

推荐答案

从Postgres 13开始，从 过程 仍然非常有限.参见:

As of Postgres 13, returning from a PROCEDURE is still very limited. See:

• 如何退货存储过程(不是函数)中的值?

最有可能的是，您迷失了广泛使用的误称存储过程" ，并且确实希望使用

Most likely, you fell for the widespread misnomer "stored procedure" and really want a FUNCTION instead, which can return a value, a row or a set according to its declaration.

• 在PostgreSQL中，存储过程"和其他类型的函数有什么区别?
• 如何在PostgreSQL的函数中返回SELECT的结果?

将像这样工作:

CREATE OR REPLACE FUNCTION public.testSpCrud(
    fnam text,
    lnam text,
    id integer,
    condition integer)
  RETURNS SETOF Employee LANGUAGE plpgsql AS
$func$
BEGIN
   CASE condition
   WHEN 1 THEN
      INSERT INTO public.employee(
       employeeid, lname, fname, securitylevel, employeepassword, hphonearea, hphone, cphonearea, cphone, street, city, state, zipcode, extzip, name, email, groomerid, type, commission, inactive, carrierid, notoallemployees, languageid, isdogwalker, ispetsitter, ismobilegroomer, ssma_timestamp)
       VALUES (4,  'Test', 'Test', 2, 2, 32, 32, 32, 32, 32, 32,32, 32, 32, 22, 22, 2, 2, 2, false, 223,true, 223, true, true, true, '2019-08-27');

   WHEN 2 THEN
      DELETE FROM Employee WHERE employeeid=id;

   WHEN 3 THEN
      UPDATE Employee SET fname='Test' WHERE employeeid=id;

   WHEN 4 THEN
      RETURN QUERY
      SELECT * FROM Employee;

   ELSE
      RAISE EXCEPTION 'Unexpected condition value %!', condition;
   END CASE;
END
$func$;

用 简化> CASE 构造，并添加了 ELSE 子句.适应您的需求.

Simplified with a CASE construct while being at it, and added an ELSE clause. Adapt to your needs.

致电:

SELECT * FROM public.testSpCrud(...);

此外:在嵌套的SQL DML命令中，可以看到plpgsql块的所有变量名.名为 id 的变量是一个等待发生的问题.我建议使用更安全的命名约定，和/或对DML语句中的所有列名进行表限定.一种流行的命名约定是在变量名前加下划线.像: _id .

Aside: all variable names of a plpgsql block are visible inside nested SQL DML commands. A variable named id is a problem waiting to happen. I suggest a safer naming convention, and / or table-qualify all column names in DML statements. One popular naming convention is to prepend variable names with an underscore. Like: _id.

并考虑使用SQL 和 PL/pgSQL中的合法小写标识符.

And consider legal, lower-case identifiers in SQL and PL/pgSQL.

• PostgreSQL列名是否区分大小写?

这篇关于如何从PostgreSQL存储过程中获取结果集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！