convert function from oracle to postgres

Question

I have an old system where the database is Oracle and the central business logic is in the DB, and now I need to move to Postgres DB.

I have a function that works to return an updated statement in Oracle.

I moved to Postgres, so I converted my Oracle schema to Postgres using Ora2Pg

When I run the function in Postgres, it returns an error as Statment not recognized.

My Code:

Function Create_Update_Statement(pMId Number)
    return Clob is
    
    Cursor Cursor_Salary_Formula(pId Number) is
        Select s.*
        from   Salary_Formula_d s
        where  s.mid = pId ;
        
    Update_Statement Clob; 
    
    type Salary_Formula_D_tabletype is table of Salary_Formula_d%rowtype index by varchar2(200); 
    Salary_Formula_table Salary_Formula_D_tabletype;
    
Begin
    Update_Statement := null;
    
    for Salary_Formula_D_Rec in Cursor_Salary_Formula(pMId) loop
      
        if Update_Statement is null then
            Update_Statement := 'Update Salary Set ' ||  Salary_Formula_D_Rec.Fieldname ||  ' = ';
        else
            Update_Statement := Update_Statement ||  ' , ' ||  Salary_Formula_D_Rec.Fieldname ||  ' = ';
        end if;
        
        if upper(Salary_Formula_D_Rec.Data_Type) =
              'DATE' THEN
            Update_Statement := Update_Statement ||
                                                    'TO_DATE(''' ||
                                                    Salary_Formula_Table(pMId)(Salary_Formula_D_Rec.fieldname).val_string ||
                                                    ''',''DD/MM/YYYY'')';
        elsif upper(Salary_Formula_D_Rec.Data_Type) =
                    'VARCHAR2' THEN
            Update_Statement := Update_Statement || '''' ||
                                                    Salary_Formula_Table(pMId)(Salary_Formula_D_Rec.fieldname).val_string || '''';
     
        END IF;
    end loop;
  
    return(Update_Statement);
end;

The error is the statement:

Salary_Formula_Table(pMId)(Salary_Formula_D_Rec.fieldname).val_string

The structure of the table SALARY_FORMULA_D is

create table SALARY_FORMULA_D
    (
      id               NUMBER not null,
      mid              NUMBER,
      fieldname        VARCHAR2(200),
      formula          VARCHAR2(4000),
      val_number       NUMBER,
      val_string       VARCHAR2(500),
      val_date         DATE,
      fieldname_ar     VARCHAR2(400),
      fieldname_en     VARCHAR2(400),
      data_type        VARCHAR2(100)
    )

Any help will be appreciated

Answer 1

Rewrite the function in a way that works with Postgres and utilizes its array capabilities. Try the following code;

CREATE OR REPLACE FUNCTION Create_Update_Statement(pMId INTEGER)
    RETURNS TEXT
AS
$$
DECLARE
    Update_Statement TEXT;
    Field_Names TEXT[];
    Data_Types TEXT[];
    Val_Strings TEXT[];
BEGIN
    Update_Statement := 'UPDATE Salary SET ';
    
    -- Collect data from the Salary_Formula_d table into arrays
    SELECT array_agg(fieldname), array_agg(data_type), array_agg(val_string)
    INTO Field_Names, Data_Types, Val_Strings
    FROM Salary_Formula_d
    WHERE mid = pMId;
    
    FOR i IN 1 .. array_length(Field_Names, 1) LOOP
        IF i > 1 THEN
            Update_Statement := Update_Statement || ', ';
        END IF;

        Update_Statement := Update_Statement || Field_Names[i] || ' = ';

        IF Data_Types[i] = 'DATE' THEN
            Update_Statement := Update_Statement || 'TO_DATE(''' || Val_Strings[i] || ''',''DD/MM/YYYY'')';
        ELSIF Data_Types[i] = 'VARCHAR2' THEN
            Update_Statement := Update_Statement || '''' || Val_Strings[i] || '''';
        -- Add more cases for other data types as needed
        END IF;
    END LOOP;

    -- Add the WHERE clause to specify the record to update
    Update_Statement := Update_Statement || ' WHERE id = ' || pMId;

    RETURN Update_Statement;
END;
$$
LANGUAGE plpgsql;