问题描述
我在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:
最有可能的是,您迷失了广泛使用的误称存储过程" ,并且确实希望使用 FUNCTION
代替,它可以根据其声明返回值,行或集合.
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.
将像这样工作:
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存储过程中获取结果集?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!