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
Newbie
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
Newbie
source
to share