error converting an Oracle procedure to a PostgreSql function in the pl / pgsql language

1

I have adapted the code, but the following error appears:

  

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

    
asked by anonymous 07.01.2015 / 17:26

1 answer

1

The problem is on the line:

CAMPOS1  CAMPOS%ROWTYPE;

You can not declare CAMPOS1 with data type CAMPOS%ROWTYPE because there is no table (relation) with name CAMPOS . Only declaring CAMPOS1 as RECORD is enough.

Note that you want to be using this variable, so it can be removed, unless you are using it in another code snippet other than the one posted.

    
07.01.2015 / 21:13