CODEWITHSUNDEEP
postgresql
Werner
Werner

Reputation: 2096

Translating Firebird SQL trigger to PG trigger/function

I am trying to convert Firebird SQL trigger code to trigger/function in PG.

On the following I get a syntax error on ';' at "END;", I think I changed the rest correctly but can't figure out that syntax error.

CREATE OR REPLACE FUNCTION purchase_q_ai0()
  RETURNS void as $$

    declare
    CB_QUANTITY integer;
    CB_QUANTITYONHAND integer;
    CB_BOT_ID keys;
    CB_ID keys;
    BOT_QUANTITY integer;
    BOT_QUANTITYONHAND integer;
    BOT_ID keys;
    TEMP_QUANTITY integer;

BEGIN
    /* Update cellarbook and bottle after INSERT
    quantitypurchase, quantityonhand, purchasevalueonhand and
    currentvalueonhand will be updated */
    if (new.quantity is Null) then
        TEMP_QUANTITY := 0;
    else
        TEMP_QUANTITY := new.quantity;

    /* get corresponding cellarbook info */
    select cellarbook.quantitypurchased, cellarbook.quantityonhand, cellarbook.fk_bottle_id, cellarbook.id
            from cellarbook
            where new.fk_cellarbook_id = cellarbook.id
            into cb_quantity, cb_quantityonhand, cb_bot_id, cb_id;

    if (CB_QUANTITY is null) then
        CB_QUANTITY := 0;
    if (CB_QUANTITYONHAND is null) then
        CB_QUANTITYONHAND := 0;

    /* get corresponding bottle info */
    select bottle.quantitypurchased, bottle.quantityonhand, bottle.id from bottle
           where bottle.id = cb_bot_id
           into bot_quantity, bot_quantityonhand, bot_id;

    if (BOT_QUANTITY is null) then
        BOT_QUANTITY := 0;
    if (BOT_QUANTITYONHAND is null) then
        BOT_QUANTITYONHAND := 0;

    /* update with new info */
    update cellarbook set cellarbook.quantitypurchased = (cb_quantity + temp_quantity),
                          cellarbook.quantityonhand = (cb_quantityonhand + temp_quantity),
                          cellarbook.purchasevalueonhand = (cellarbook.lastpurchaseprice * cellarbook.quantityonhand),
                          cellarbook.currentvalueonhand = (cellarbook.currentvalue * cellarbook.quantityonhand)
            where cellarbook.id = cb_id;
    update bottle set bottle.quantitypurchased = (bot_quantity + temp_quantity),
                      bottle.quantityonhand = (bot_quantityonhand + temp_quantity),
                      bottle.purchasevalueonhand = (bottle.lastpurchaseprice * bottle.quantityonhand),
                      bottle.currentvalueonhand = (bottle.currentvalue * bottle.quantityonhand)
           where bottle.id = bot_id;
END;
$$
LANGUAGE plpgsql;

Upvotes: 0

Views: 143

Answers (2)

Patrick
Patrick

Reputation: 32199

A trigger function in plpgsql language always needs to return trigger and it needs a proper return statement as well. Otherwise you need to END IF your IF statements and restructure your SELECT INTO statements:

CREATE OR REPLACE FUNCTION purchase_q_ai0() RETURNS trigger AS $$
DECLARE
  CB_QUANTITY integer;
  CB_QUANTITYONHAND integer;
  CB_BOT_ID keys;
  CB_ID keys;
  BOT_QUANTITY integer;
  BOT_QUANTITYONHAND integer;
  BOT_ID keys;
  TEMP_QUANTITY integer;
BEGIN
  /* Update cellarbook and bottle after INSERT
     quantitypurchase, quantityonhand, purchasevalueonhand and
     currentvalueonhand will be updated */
  IF (NEW.quantity IS NULL) then
    TEMP_QUANTITY := 0;
  ELSE
    TEMP_QUANTITY := NEW.quantity;
  END IF;

  /* get corresponding cellarbook info */
  SELECT cb.quantitypurchased, cb.quantityonhand, cb.fk_bottle_id, cb.id
  INTO cb_quantity, cb_quantityonhand, cb_bot_id, cb_id
  FROM cellarbook cb
  WHERE NEW.fk_cellarbook_id = cb.id;

  IF (CB_QUANTITY IS NULL) THEN
    CB_QUANTITY := 0;
  END IF;
  IF (CB_QUANTITYONHAND IS NULL) THEN
    CB_QUANTITYONHAND := 0;
  END IF;

  /* get corresponding bottle info */
  SELECT b.quantitypurchased, b.quantityonhand, b.id
  INTO bot_quantity, bot_quantityonhand, bot_id
  FROM bottle b
  WHERE b.id = cb_bot_id;

  IF (BOT_QUANTITY IS NULL) THEN
    BOT_QUANTITY := 0;
  END IF;
  IF (BOT_QUANTITYONHAND IS NULL) THEN
    BOT_QUANTITYONHAND := 0;
  END IF;

  /* update with new info */
  UPDATE cellarbook SET
    quantitypurchased = cb_quantity + temp_quantity,
    quantityonhand = cb_quantityonhand + temp_quantity,
    purchasevalueonhand = lastpurchaseprice * quantityonhand,
    currentvalueonhand = currentvalue * quantityonhand
  WHERE id = cb_id;

  UPDATE bottle SET
    quantitypurchased = bot_quantity + temp_quantity,
    quantityonhand = bot_quantityonhand + temp_quantity,
    purchasevalueonhand = lastpurchaseprice * quantityonhand,
    currentvalueonhand = currentvalue * quantityonhand
  WHERE id = bot_id;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

I leave it to you to repair the case of the internally declared variables (use of lowercase is recommended in PostgreSQL).

Incidentally, PostgreSQL does not know the keys data type by default. Unless you define that data type the code won't work. Is this perhaps something specific to Firebird, for use as a PRIMARY KEY? In that case it is most likely a 32-bit integer and you would use the integer data type in PostgreSQL.

Upvotes: 1

Vivek S.
Vivek S.

Reputation: 21905

You need to properly use END IF in the following area

if (new.quantity is Null) then
    TEMP_QUANTITY := 0;
else
    TEMP_QUANTITY := new.quantity;
End if;

if (CB_QUANTITY is null) then
   CB_QUANTITY := 0;
if (CB_QUANTITYONHAND is null) then
   CB_QUANTITYONHAND := 0;
End if;
End if;

if (BOT_QUANTITY is null) then
    BOT_QUANTITY := 0;
if (BOT_QUANTITYONHAND is null) then
    BOT_QUANTITYONHAND := 0;
End if;
End if;

PLPGSQL-CONDITIONALS

Upvotes: 0

Related Questions