选择或者在PL / pgSQL函数PERFORM(SELECT or PERFORM in a PL

我在我的数据库这样的功能：

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" = $1 AND "idTipo"=$3 AND "idFamilia"=$4;
    IF NOT FOUND THEN
        INSERT INTO "Articulo" ("Nombre", "Descripcion", "idTipo", "idFamilia", "Stock", "MinStock", "MaxStock") Values ($1, $2, $3, $4, $5, $6, $7);
        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" = $8;
    IF NOT FOUND THEN
        Insert into "ArticuloMarca"("idArticulo", "idMarca", "PrecioReferencial", "Stock") Values (articulo."idArticulo", $8, $9, $10);
        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;

但只要我尝试使用它，它说我需要使用PERFORM如果我要放弃的结果！ 这里的问题是，我不想！ 我想他们在articulo我宣布行！

我用这样的说法：

SELECT "insertarNuevoArticulo"('Acetaminofen', 'caro', '1' , '1', '8', '1', '10', '1', '150.7', '10');

而我得到的错误是42601，一个语法错误！ 这怎么可能，如果我使用的IDE创建？ 有关该问题的任何想法？

Answer 1:

在PLPGSQL代码， SELECT没有目标触发一个错误。 但你显然不希望SELECT INTO ，你只是想设置的状态FOUND 。 你可以使用PERFORM了点。

• SELECT提出了在PL / pgSQL函数异常

更好的，但是 ，使用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" = $1 AND a."idTipo" = $3 AND a."idFamilia" = $4;

    IF NOT FOUND THEN
        INSERT INTO "Articulo"("Nombre", "Descripcion", "idTipo"
                             , "idFamilia", "Stock", "MinStock", "MaxStock")
        VALUES ($1, $2, $3, $4, $5, $6, $7)
        RETURNING "Articulo"."idArticulo" INTO _id_articulo;
    END IF;

   IF EXISTS (SELECT FROM "ArticuloMarca" a
              WHERE a."idArticulo" = _id_articulo AND a."idMarca" = $8) THEN
      RETURN FALSE;
   ELSE
      INSERT INTO "ArticuloMarca"("idArticulo", "idMarca", "PrecioReferencial", "Stock")
      VALUES (_id_articulo, $8, $9, $10);
      RETURN TRUE;
    END IF;
END
$func$  LANGUAGE plpgsql;

关于EXISTS ：

• PL / pgSQL的检查，如果一个行存在

其他主要点 ：

• 使用RETURNING子句中的INSERT语句而不是一个额外的SELECT 。

Postgres的9.5+

在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" = $1 AND "idTipo" = $3 AND "idFamilia" = $4;

      EXIT WHEN FOUND;

      INSERT INTO "Articulo"("Nombre", "Descripcion", "idTipo"
                           , "idFamilia", "Stock", "MinStock", "MaxStock")
      VALUES ($1, $2, $3, $4, $5, $6, $7)
      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, $8, $9, $10)
      ON     CONFLICT ("idArticulo", "idMarca") DO NOTHING;

      IF FOUND THEN
         RETURN TRUE;
      END IF;

      IF EXISTS (SELECT FROM "ArticuloMarca"
                 WHERE "idArticulo" = _id_articulo AND "idMarca" = $8) THEN
         RETURN FALSE;
      END IF;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

这是更快，更简单，更可靠。 增加的循环排除与并发写入任何剩余的比赛条件（虽然不能增加任何费用）。 如果没有并发写入，可以简化。 详细说明：

• 在容易出现竞争情况的功能SELECT或INSERT？
• 如何使用与PostgreSQL的ON RETURNING冲突？

旁白：用法律，小写标识，以避免所有的丑陋的双引号 。

Answer 2:

该行看起来可疑对我可能是什么原因造成你的悲伤：

SELECT * FROM "ArticuloMarca" AS am WHERE am."idArticulo" = articulo."idArticulo" and am."idMarca" = $8;

你是你的函数中执行SELECT，但不这样做的结果什么。 你需要一个像你刚才在你的函数没有执行SELECT INTO。

文章来源: SELECT or PERFORM in a PL/pgSQL function