Postgresql Syntax Error for Trigger

Question

here is my Trigger and when i try to execute this I am getting error

Trigger:

CREATE OR REPLACE FUNCTION vqm_minoutline_cp_trg()
  RETURNS trigger AS
$BODY$ DECLARE 
   --TYPE RECORD IS REFCURSOR;
  Cur_Defaults                RECORD;
  v_M_Product_ID              VARCHAR(32); --OBTG:varchar2--
  v_VQM_Parameter_ID          VARCHAR(32); --OBTG:varchar2--
  vqm_minoutline_parameter_id    VARCHAR(32);
  v_count             NUMERIC;
BEGIN

    IF AD_isTriggerEnabled()='N' THEN IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; 
    END IF;

    --  Default Quality Parameter for Product
    IF (TG_OP = 'INSERT') THEN

    FOR Cur_Defaults IN
(
    SELECT VQM_Parameter_ID, Description, Criteria, MinValue, MaxValue, TextValue
        FROM  VQM_Product_Parameter VPP
    WHERE VPP.M_PRODUCT_ID=new.M_PRODUCT_ID

)
LOOP
    /*
        Creating quality lines for Purchase Order Line
    */

     SELECT * INTO  vqm_minoutline_parameter_id FROM Ad_Sequence_Next('vqm_minoutline_parameter', Cur_Defaults.VQM_Parameter_ID);

          INSERT INTO vqm_minoutline_parameter    //here line 65 and so on
          (vqm_minoutline_parameter_id, m_inoutline_id, VQM_PARAMETER_ID, AD_Client_ID, AD_Org_ID, 
          IsActive, Created, CreatedBy, Updated, UpdatedBy, Criteria, MaxValue, MinValue, 
          TextValue, Description)
          VALUES
          (vqm_minoutline_parameter_id, new.m_inoutline_id, Cur_Defaults.VQM_PARAMETER_ID, 
          new.AD_Client_ID, new.AD_Org_ID, 'Y', TO_DATE(NOW()), new.CreatedBy, TO_DATE(NOW()), new.UpdatedBy,
          Cur_Defaults.Criteria, Cur_Defaults.MaxValue, Cur_Defaults.MinValue, Cur_Defaults.TextValue, 
          Cur_Defaults.Description);

END LOOP;


ELSIF (TG_OP = 'UPDATE') THEN

    IF new.M_PRODUCT_ID != old.M_PRODUCT_ID THEN

        DELETE FROM vqm_minoutline_parameter WHERE m_inoutline_id = new.m_inoutline_id;

        FOR Cur_Defaults IN
        (
        SELECT VQM_Parameter_ID, Description, Criteria, MinValue, MaxValue, TextValue
        FROM  VQM_Product_Parameter VPP
        WHERE VPP.M_PRODUCT_ID=new.M_PRODUCT_ID
        )
        LOOP
        /*
        Creating quality lines for Purchase Order Line
        */

        SELECT * INTO  vqm_minoutline_parameter_id FROM Ad_Sequence_Next('vqm_minoutline_parameter', Cur_Defaults.VQM_Parameter_ID);

        INSERT INTO vqm_minoutline_parameter
        (
        vqm_minoutline_parameter_id, m_inoutline_id, VQM_PARAMETER_ID, AD_Client_ID, AD_Org_ID, 
        IsActive, Created, CreatedBy, Updated, UpdatedBy, Criteria, MaxValue, MinValue, 
        TextValue, Description
        )

        VALUES
        (
        vqm_minoutline_parameter_id, new.m_inoutline_id, Cur_Defaults.VQM_PARAMETER_ID, 
        new.AD_Client_ID, new.AD_Org_ID, 'Y', TO_DATE(NOW()), new.CreatedBy, TO_DATE(NOW()), new.UpdatedBy,
        Cur_Defaults.Criteria, Cur_Defaults.MaxValue, Cur_Defaults.MinValue, Cur_Defaults.TextValue, 
        Cur_Defaults.Description
        );

        END LOOP;

    END IF; 

END IF;

    IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; 

END 

; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION vqm_minoutline_cp_trg()
  OWNER TO saksham27;

This is error:

ERROR:  syntax error at or near "$1"
LINE 1: INSERT INTO vqm_minoutline_parameter ( $1 , m_inoutline_id, ...
                                           ^
QUERY:  INSERT INTO vqm_minoutline_parameter ( $1 , m_inoutline_id, VQM_PARAMETER_ID, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, Criteria, MaxValue, MinValue, TextValue, Description) VALUES ( $1 ,  $2 ,  $3 ,  $4 ,  $5 , 'Y', TO_DATE(NOW()),  $6 , TO_DATE(NOW()),  $7 ,  $8 ,  $9 ,  $10 ,  $11 ,  $12 )
CONTEXT:  SQL statement in PL/PgSQL function "vqm_minoutline_cp_trg" near line 66


********** Error **********

ERROR: syntax error at or near "$1"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "vqm_minoutline_cp_trg" near line 66

Answer 1

In this query:

  INSERT INTO vqm_minoutline_parameter    //here line 65 and so on
  (vqm_minoutline_parameter_id, m_inoutline_id, VQM_PARAMETER_ID, AD_Client_ID, AD_Org_ID, 
  IsActive, Created, CreatedBy, Updated, UpdatedBy, Criteria, MaxValue, MinValue, 
  TextValue, Description)
  VALUES
  (vqm_minoutline_parameter_id, new.m_inoutline_id, Cur_Defaults.VQM_PARAMETER_ID, 
  new.AD_Client_ID, new.AD_Org_ID, 'Y', TO_DATE(NOW()), new.CreatedBy, TO_DATE(NOW()), new.UpdatedBy,
  Cur_Defaults.Criteria, Cur_Defaults.MaxValue, Cur_Defaults.MinValue, Cur_Defaults.TextValue, 
  Cur_Defaults.Description);

You're using vqm_minoutline_parameter_id both as a column name and a plpgsql variable name. As a result, plpgsql replaces the column name with $1 which leads to an incorrect query.

The workaround is to change the variable name to something that does not clash with any column name.

The documentation mentions the problem in Variable Substitution, with this note:

Note: PostgreSQL versions before 9.0 would try to substitute the variable in all three cases, leading to syntax errors.

Presumably you're using a pre-9.0 version otherwise the error wouldn't occur. In theory column names are no longer substituted with more recent versions.