Data cycling issues in postgres function using% ROWTYPE

I am trying to create a function in postgres that fetches data from one table and injects it into another. I am using% ROWTYPE to store temporary data from a select statement and then iterate over it with an insert statement, but no success! Below is my code:

CREATE OR REPLACE FUNCTION rm_stock_take_add (icompany character varying, idate character varying) 
  RETURNS character varying AS

$BODY$

DECLARE

    loc_result    CHAR(50);

    -- Declaring a counter to increment for the index
    counter INTEGER;

    -- Declare a variable to hold rows from the stock table.
    row_data rm_stock%ROWTYPE;


BEGIN

     -- Iterate through the results of a query.
    FOR row_data IN 
    SELECT *
    FROM rm_stock 
    --WHERE company = icompany 
    ORDER BY company, rm_sto_code, rm_col_code  

        LOOP

      counter := counter + 1;
/*       
          INSERT INTO rm_stock_take
          ( 
           "stock_ind", "company", "rm_stock_code", "rm_col_code", "rm_dye_lot_num", "rm_take_date", "rm_quantity_theo"
          )
          VALUES 
          ( 
           counter, icompany, row_data.rm_sto_code, row_data.rm_col_code, row_data.rm_dye_lot_num, idate,
               row_data.rm_sto_on_hand_excl
          );

*/      
       END LOOP;



   RETURN counter :: character varying;

END;
$BODY$
 LANGUAGE 'plpgsql'VOLATILE;
ALTER FUNCTION rm_stock_take_add(icompany character varying, idate character varying) OWNER TO postgres;

      

Ok, so at the moment I'm just trying to check if the function is looping, using a counter to count the number of loops and return that number, but so far nothing is returning. I haven't been able to find much information on the internet or anywhere regarding this kind of procedure, and if anyone can help or guide me in the right direction, we'd be very grateful!

thank

+1


source to share


1 answer


Got the job ...

here is the code for future reference in case anyone else is facing the same problem!



CREATE OR REPLACE FUNCTION rm_stock_take_add(icompany character varying, idate character varying)
  RETURNS character varying AS
$BODY$


DECLARE
    loc_result CHAR(50);
    counter INTEGER = 1;
    row_data RECORD;

BEGIN
    FOR row_data IN SELECT rm_sto_code, rm_col_code, rm_dye_lot_num, rm_sto_on_hand_excl
    FROM rm_stock 
    WHERE company = icompany 
    ORDER BY company, rm_sto_code, rm_col_code  

    LOOP         
          INSERT INTO rm_stock_take
          ( 
               "stock_ind", "company", "rm_stock_code", "rm_col_code", "rm_dye_lot_num", "rm_take_date", "rm_quantity_theo"
          )
          VALUES 
          ( 
               counter, icompany, row_data.rm_sto_code, row_data.rm_col_code, row_data.rm_dye_lot_num, idate,
               row_data.rm_sto_on_hand_excl
          );   
          counter := counter + 1;

    END LOOP;

    loc_result := 'success';  
    RETURN loc_result ;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION rm_stock_take_add(icompany character varying, idate character varying) OWNER TO postgres;

      

+6


source







All Articles