postgresqlfunctionplpgsqlselect-into

SELECT or PERFORM in a PL/pgSQL function


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?


Solution

  • 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.

    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
      LANGUAGE plpgsql 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$;
    

    About EXISTS:

    The other major point:

    Postgres 9.5+

    In Postgres 9.5 or later use INSERT ... ON CONFLICT DO NOTHING (a.k.a. "UPSERT") instead.
    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
      LANGUAGE plpgsql 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$;
    

    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:

    Aside: use legal, lower-case identifiers to avoid all the noisy double-quotes.