SELECT或PERFORM在PL / pgSQL函数中 [英] SELECT or PERFORM in a PL/pgSQL function

问题描述

  CREATE OR REPLACE FUNCTIONinsertarNuevoArticulo（nombrearticulo character varying，descripcion text， idtipo整数，idfamilia bigint，art​​stock整数，minstock整数，maxstock整数，idmarca bigint，precio real，marcastock整数）
 RETURNS布尔值AS 
 $ BODY $ 
 DECLARE 
 articuloArticulo ％ROWTYPE; 
 BEGIN 
 SELECT * INTO从ArticuloWHERENombre= $ 1 ANDidTipo= $ 3和idFamilia= $ 4; 
如果没有找到
插入Articulo（Nombre，Descripcion，idTipo，idFamilia，Stock，MinStock，MaxStock）价值（1美元， 2美元，3美元，4美元，5美元，6美元，7美元）; 
 SELECT last_value 
 INTO articulo。idArticulo
 FROMpublic。Articulo_idArticulo_seq; 
 END IF; 
 SELECT * FROMArticuloMarcaAS am where。idArticulo= articulo。idArticuloand am。idMarca= $ 8; 
如果没有找到，则
插入ArticuloMarca（idArticulo，idMarca，PrecioReferencial，Stock）价值观（关键词idArticulo，$ 8，$ 9，$ 10）; 
 RETURN TRUE; 
 END IF; 
 RETURN FALSE; 
 END; 
 $ BODY $ 
语言plpgsql VOLATILE 
 COST 100; 
 ALTER FUNCTIONinsertarNuevoArticulo（字符变化，文本，整数，bigint，整数，整数，整数，bigint，实数，整数）
 OWNER TO postgres; 
  

但是当我尝试使用它时，它说我需要使用 PERFORM 如果我想放弃结果！这里的问题是我不想！我希望他们在我声明的 articulo 行中！

我使用以下语句：

$ b'b

  SELECTinsertarNuevoArticulo（'Acetaminofen'，'caro'，'1'，'1'，'8'，'1'，'10 '，'1'，'150.7'，'10'）; 
  

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

解决方案

在plpgsql代码中， SELECT without目标触发错误。但你显然不想 SELECT INTO ，你只需要设置 FOUND 的状态>。您可以使用 PERFORM 。

• SELECT在PL / pgSQL函数中引发异常

使用 IF EXISTS ... 更好，但。考虑这个重写你的函数：

pre $ CREATE OR REPLACE FUNCTIONinsertarNuevoArticulo
（nombrearticulo text，descripcion text，idtipo int，idfamilia bigint，art​​stock int
，minstock int，maxstock int，idmarca bigint，precio real，marcastock int）
RETURNS boolean AS
$ func
DECLARE
_id_articuloArticulo。idArticulo％TYPE;
BEGIN
SELECT a。idArticuloINTO _id_articulo
从Articuloa
WHERE a。Nombre= $ 1 AND a。idTipo= $ 3 AND a。 idFamilia= $ 4;

如果没有找到
插入Articulo（Nombre，Descripcion，idTipo
，idFamilia，Stock，MinStock， MaxStock）
价值（$ 1，$ 2，$ 3，$ 4，$ 5，$ 6，$ 7）
退货Articulo。idArticuloINTO _id_articulo;
END IF;

IF EXISTS（SELECT FROMArticuloMarcaa
WHERE a。idArticulo= _id_articulo AND a。idMarca= $ 8）then
RETURN FALSE;
ELSE
INSERT INTOArticuloMarca（idArticulo，idMarca，PrecioReferencial，Stock）
VALUES（_id_articulo，$ 8，$ 9，$ 10）;
RETURN TRUE;
END IF;
END
$ func $ LANGUAGE plpgsql;


关于 EXISTS ：

• PL / pgSQL检查行是否存在
  

其他专业点：

• 使用 INSERT的/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROWrel =nofollow noreferrer> RETURNING 子句 语句而不是额外的 SELECT 。

Postgres 9.5 +

在Postgres 9.5或更高版本中，应该使用UPSERT：（ INSERT ... ON CONFLICT DO NOTHING ）。
您将在Articulo（Nombre，idTipo，idFamilia）上有 UNIQUE ）和ArticuloMarca（idArticulo，idMarca）然后：



  CREA TE或REPLACE FUNCTION insert_new_articulo 
（nombrearticulo text，descripcion text，idtipo int，idfamilia bigint，art​​stock int 
，minstock int，maxstock int，idmarca bigint，precio real，marcastock int）
 RETURNS boolean AS 
 $ func $ 
 DECLARE 
 _id_articuloArticulo。idArticulo％TYPE; 
 BEGIN 
 LOOP 
 SELECTidArticuloINTO _id_articulo 
从Articulo
 WHERENombre= $ 1 ANDidTipo= $ 3 ANDidFamilia= $ 4 ; 
 
找到时退出; 
 
插入Articulo（Nombre，Descripcion，idTipo
，idFamilia，Stock，MinStock，MaxStock）
价值（$ 1，$ 2，$ 3，$ 4，$ 5，$ 6，$ 7）
冲突（标记）不做任何
返回idArticuloINTO _id_articulo; 
 
找到时退出; 
 END LOOP; 
 
 LOOP 
插入ArticuloMarca（idArticulo，idMarca，PrecioReferencial，Stock）
 VALUES（_id_articulo，$ 8，$ 9，$ 10）
 ON CONFLICT（idArticulo，idMarca）不做任何事; 
 
如果发现则
 RETURN TRUE; 
 END IF; 
 
 IF EXISTS（SELECT fromArticuloMarca
 WHEREidArticulo= _id_articulo ANDidMarca= $ 8）然后
 RETURN FALSE; 
 END IF; 
 END LOOP; 
 END 
 $ func $ LANGUAGE plpgsql; 
  

这更快，更简单，更可靠。添加的循环排除了并发写入时的任何剩余竞争条件（同时几乎不增加任何成本）。没有并发写入，你可以简化。详细解释：



• 是SELECT或INSERT在一个容易出现竞争条件的函数？


• 如何使用PostgreSQL中的ON CONFLICT返回？除此之外：使用合法的小写标识符来避免所有丑陋的 org / docs / current / static / sql-syntax-lexical.html＃SQL-SYNTAX-IDENTIFIERSrel =nofollow noreferrer>双引号。
  
  

  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" = $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;
  

  But as soon as I try to use it, it says I need to use PERFORM if I want to discard the results! The problem here is that I don't want to! I want them in the articulo row I declared!

  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?

  解决方案

  In plpgsql code, SELECT without a target triggers an error. But you obviously do not want SELECT INTO, you just want to set the status of FOUND. You would use PERFORM for that.

  • SELECT raises exception in PL/pgSQL function

  Better, yet, use IF EXISTS .... Consider this rewrite of your function:

  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;
  

  About EXISTS:

  • PL/pgSQL checking if a row exists

  The other major point:

  • Use the RETURNING clause of the INSERT statement instead of an additional SELECT.

  Postgres 9.5+

  In Postgres 9.5 or later rather use an UPSERT instead: (INSERT ... ON CONFLICT DO NOTHING).
  You would have UNIQUE constraints on "Articulo"("Nombre", "idTipo", "idFamilia") and "ArticuloMarca"("idArticulo", "idMarca") and then:

  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;
  

  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.

  这篇关于SELECT或PERFORM在PL / pgSQL函数中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！