error when converting an Oracle precedent into a Postgresql function in pl/pgsql

Question

I adapted the code, but there is the following error :

ERROR: relation "fields" does not exist CONTEXT: compilation of PL/pgsql function "p_grava_log"

CREATE OR REPLACE FUNCTION F_GRAVA_LOG (
  TIPO character varying,  
  C_TABELA  character varying,  
  C_CAMPOS_CHAVE  character varying,  
  C_CHAVES  character varying, 
  --VALOR DO CAMPO CHAVE  C_USUARIO NUMERIC,  
  C_MODULO  character varying)  RETURNS TEXT AS    

    $BODY$

  DECLARE 
    V_CAMPOS  character varying(6000);    
    V_SQL  character varying(32000); 
    CAMPOS RECORD;
    CAMPOS1  CAMPOS%ROWTYPE;


  BEGIN 

    FOR CAMPOS IN 
    SELECT column_name 
      FROM information_schema.columns 
     WHERE table_name = C_TABELA
     ORDER BY ordinal_position LOOP      
      V_CAMPOS := V_CAMPOS || CAMPOS.COLUMN_NAME || ','; 
    END LOOP; 
    V_SQL := 'INSERT INTO ' || 
             ' l_'||C_TABELA||' (' || V_CAMPOS || ') ' || 
             '  SELECT ' || V_CAMPOS  || 
             '    FROM ' || C_TABELA || 
             '   WHERE ' || C_CAMPOS_CHAVE || ' = ' || C_CHAVES; 

     EXECUTE  V_SQL; 
       Return V_SQL;     

  END;  $BODY$

LANGUAGE plpgsql

Answer 1

The problem is on the line:

CAMPOS1  CAMPOS%ROWTYPE;

You can’t declare CAMPOS1 with the data type CAMPOS%ROWTYPE because there is no table (relation) with the name CAMPOS. Just declare CAMPOS1 as RECORD is enough.

Note that you want to use this variable, so it can be removed, unless you are using it in another code snippet that is not posted.