2

Im trying to create a standard function to insert a product or add quantity of an existing product, so far I have this:

CREATE OR REPLACE FUNCTION insert_materials (IN _ASADAFK  VARCHAR(40), _material   VARCHAR(40), _stock INT)
RETURNS void AS
$$
BEGIN 
  IF EXISTS (SELECT material FROM materials WHERE material= _material)
  THEN
    update materials set stock= (stock + _stock) where material=_material

  ELSE 
    INSERT INTO "materials" VALUES(_ASADAFK,_material,_stock)
END IF;
END;
$$ 
LANGUAGE 'plpgsql';

Im getting a syntax error near the ELSE, Ive tried several things but not really experienced in PostGres. Any help please?

Juank Abarca
  • 21
  • 5

1 Answers1

1

There should be semicolons before 'ELSE' and before 'END IF' (semicolons are required after all statements except that which opens a block. See documentation). You still may wish to add exception handling, as INSERT may fail if unique index is used on material column and several sessions will try to add the same material at the same time.

There is a lot of similar questions here (look here for example) or google for 'postgres upsert'.

Community
  • 1
  • 1
Vasily Redkin
  • 584
  • 1
  • 3
  • 17