postgresql PL/pgSQL 函数中的 SELECT 或 PERFORM
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10115806/
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
SELECT or PERFORM in a PL/pgSQL function
提问by dbncourt
I have this function in my database:
我的数据库中有这个功能：
CREATE OR REPLACE FUNCTION "insertarNuevoArticulo"(nombrearticulo character varying, descripcion text, idtipo integer, idfamilia bigint, artstock integer, minstock integer, maxstock integer, idmarca bigint, precio real, marcastock integer)
RETURNS boolean AS
$BODY$
DECLARE
articulo "Articulo"%ROWTYPE;
BEGIN
SELECT * INTO articulo FROM "Articulo" WHERE "Nombre" = AND "idTipo"= AND "idFamilia"=;
IF NOT FOUND THEN
INSERT INTO "Articulo" ("Nombre", "Descripcion", "idTipo", "idFamilia", "Stock", "MinStock", "MaxStock") Values (, , , , , , );
SELECT last_value
INTO articulo."idArticulo"
FROM "public"."Articulo_idArticulo_seq";
END IF;
SELECT * FROM "ArticuloMarca" AS am WHERE am."idArticulo" = articulo."idArticulo" and am."idMarca" = ;
IF NOT FOUND THEN
Insert into "ArticuloMarca"("idArticulo", "idMarca", "PrecioReferencial", "Stock") Values (articulo."idArticulo", , , );
RETURN TRUE;
END IF;
RETURN FALSE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION "insertarNuevoArticulo"(character varying, text, integer, bigint, integer, integer, integer, bigint, real, integer)
OWNER TO postgres;
But as soon as I try to use it, it says I need to use PERFORMif I want to discard the results! The problem here is that I don't want to! I want them in the articulorow I declared!
但是一旦我尝试使用它，它就会说PERFORM如果我想丢弃结果，我需要使用它！这里的问题是我不想！我希望他们在articulo我声明的那一行！
I'm using this statement:
我正在使用这个语句：
SELECT "insertarNuevoArticulo"('Acetaminofen', 'caro', '1' , '1', '8', '1', '10', '1', '150.7', '10');
And the error i get is 42601, a syntax error! How could it be if I'm using the IDE to create it? Any idea about the problem?
我得到的错误是 42601，一个语法错误！如果我使用 IDE 来创建它会怎样？对这个问题有什么想法吗？
回答by Erwin Brandstetter
In plpgsql code, SELECTwithout a target triggers an error. But you obviously do notwant SELECT INTO, you just want to set the status of FOUND. You would use PERFORMfor that.
在 plpgsql 代码中，SELECT没有目标会触发错误。但你显然不希望SELECT INTO，你只是想设置的状态FOUND。你会用PERFORM它。
Better, yet, use IF EXISTS .... Consider this rewrite of your function:
更好的是，使用IF EXISTS .... 考虑一下你的函数的重写：
CREATE OR REPLACE FUNCTION "insertarNuevoArticulo"
(nombrearticulo text, descripcion text, idtipo int, idfamilia bigint, artstock int
, minstock int, maxstock int, idmarca bigint, precio real, marcastock int)
RETURNS boolean AS
$func$
DECLARE
_id_articulo "Articulo"."idArticulo"%TYPE;
BEGIN
SELECT a."idArticulo" INTO _id_articulo
FROM "Articulo" a
WHERE a."Nombre" = AND a."idTipo" = AND a."idFamilia" = ;
IF NOT FOUND THEN
INSERT INTO "Articulo"("Nombre", "Descripcion", "idTipo"
, "idFamilia", "Stock", "MinStock", "MaxStock")
VALUES (, , , , , , )
RETURNING "Articulo"."idArticulo" INTO _id_articulo;
END IF;
IF EXISTS (SELECT FROM "ArticuloMarca" a
WHERE a."idArticulo" = _id_articulo AND a."idMarca" = ) THEN
RETURN FALSE;
ELSE
INSERT INTO "ArticuloMarca"("idArticulo", "idMarca", "PrecioReferencial", "Stock")
VALUES (_id_articulo, , , );
RETURN TRUE;
END IF;
END
$func$ LANGUAGE plpgsql;
About EXISTS:
关于EXISTS：
The other major point:
在其他主要点：
- Use the
RETURNINGclauseof theINSERTstatement instead of an additionalSELECT.
- 使用
RETURNING条款中的INSERT语句而不是额外的SELECT。
Postgres 9.5+
Postgres 9.5+
In Postgres 9.5 or later rather use an UPSERT instead: (INSERT ... ON CONFLICT DO NOTHING).
You would have UNIQUEconstraints on "Articulo"("Nombre", "idTipo", "idFamilia")and "ArticuloMarca"("idArticulo", "idMarca")and then:
在 Postgres 9.5 或更高版本中，而是使用 UPSERT 代替：( INSERT ... ON CONFLICT DO NOTHING)。
你将不得不UNIQUE对限制"Articulo"("Nombre", "idTipo", "idFamilia")和"ArticuloMarca"("idArticulo", "idMarca")再：
CREATE OR REPLACE FUNCTION insert_new_articulo
(nombrearticulo text, descripcion text, idtipo int, idfamilia bigint, artstock int
, minstock int, maxstock int, idmarca bigint, precio real, marcastock int)
RETURNS boolean AS
$func$
DECLARE
_id_articulo "Articulo"."idArticulo"%TYPE;
BEGIN
LOOP
SELECT "idArticulo" INTO _id_articulo
FROM "Articulo"
WHERE "Nombre" = AND "idTipo" = AND "idFamilia" = ;
EXIT WHEN FOUND;
INSERT INTO "Articulo"("Nombre", "Descripcion", "idTipo"
, "idFamilia", "Stock", "MinStock", "MaxStock")
VALUES (, , , , , , )
ON CONFLICT (tag) DO NOTHING
RETURNING "idArticulo" INTO _id_articulo;
EXIT WHEN FOUND;
END LOOP;
LOOP
INSERT INTO "ArticuloMarca"("idArticulo", "idMarca", "PrecioReferencial", "Stock")
VALUES (_id_articulo, , , )
ON CONFLICT ("idArticulo", "idMarca") DO NOTHING;
IF FOUND THEN
RETURN TRUE;
END IF;
IF EXISTS (SELECT FROM "ArticuloMarca"
WHERE "idArticulo" = _id_articulo AND "idMarca" = ) THEN
RETURN FALSE;
END IF;
END LOOP;
END
$func$ LANGUAGE plpgsql;
This is faster, simpler and more reliable. The added loops rule out any remaining race conditions with concurrent writes (while adding hardly any cost). Without concurrent writes, you can simplify. Detailed explanation:
这更快、更简单、更可靠。添加的循环排除了并发写入的任何剩余竞争条件（同时几乎不增加任何成本）。没有并发写入，您可以简化。详细解释：
- Is SELECT or INSERT in a function prone to race conditions?
- How to use RETURNING with ON CONFLICT in PostgreSQL?
Aside: use legal, lower-case identifiers to avoid all the ugly double-quoting.
旁白：使用合法的小写标识符来避免所有丑陋的双引号。
回答by Glenn
This line looks suspicious to me and is probably what is causing your grief:
这条线在我看来很可疑，可能是导致您悲伤的原因：
SELECT * FROM "ArticuloMarca" AS am WHERE am."idArticulo" = articulo."idArticulo" and am."idMarca" = ;
You are executing a SELECT within your function, but not doing anything with the results. You need to perform a SELECT INTO like you did earlier in your function.
您正在函数中执行 SELECT，但未对结果执行任何操作。您需要像之前在函数中所做的那样执行 SELECT INTO。
