PostgreSQL trigger function syntax error help request

Question

I haven't the slightest idea why postgres is complaining about the word 'INTO' after 'INSERT'. Does anyone know? FWIW I only created the body of the code, pgAdmin created the header and footer.

CREATE FUNCTION public.tf_test()
    RETURNS trigger
    LANGUAGE 'plpgsql'
     NOT LEAKPROOF
AS $BODY$BEGIN
  DECLARE targetid orderpayment.id%TYPE;

  INSERT INTO
    orderpayment(currencycode,tableorderid,payment,tip,paymentprocessor)
  VALUES
    (NEW.currency,NEW.orderid,NEW.amount,NEW.tip,1)
  RETURNING id INTO targetid;
  
  NEW.orderpayment = targetid;

  RETURN NEW;
END$BODY$;

returns the error

ERROR: syntax error at or near "INTO"
LINE 8: INSERT INTO
^
CONTEXT: invalid type name "INTO
orderpayment(currencycode,tableorderid,payment,tip,paymentprocessor)
VALUES
(NEW.currency,NEW.orderid,NEW.amount,NEW.tip,1)
RETURNING id INTO targetid"

Thanks, do you want to answer this so you can get your internet points? — Rick DeBay, Aug 19 '20 at 16:23

score 0 · Accepted Answer · answered Aug 19 '20 at 16:43

Declare statement should be before begin. So the correct definition will be like below:

CREATE or REPLACE FUNCTION public.tf_test()
    RETURNS trigger

    NOT LEAKPROOF AS 

$BODY$ 
    DECLARE 
     targetid orderpayment.id%TYPE;
   
    BEGIN
  

    INSERT INTO
       orderpayment(currencycode, tableorderid, payment, tip, paymentprocessor)
    VALUES
       (NEW.currency,NEW.orderid,NEW.amount,NEW.tip,1)
    RETURNING id INTO targetid;
  
    NEW.orderpayment = targetid;

    RETURN NEW;
   END;
$BODY$
LANGUAGE plpgsql;

PostgreSQL trigger function syntax error help request

1 Answers1