I am getting the below error while I am running the below function "add_po_doc_hist", for "c_company" cursor, please suggest what is wrong with code.
ERROR: relation "c_company" does not exist
CONTEXT: compilation of PL/pgSQL function "add_po_doc_hist" near line 11
********** Error **********
ERROR: relation "c_company" does not exist
SQL state: 42P01
Context: compilation of PL/pgSQL function "add_po_doc_hist" near line 11
---------------------------------------------------------------------------------------------------------------------
-- Function: add_po_doc_hist(bigint)
-- DROP FUNCTION add_po_doc_hist(bigint);
CREATE OR REPLACE FUNCTION add_po_doc_hist(subscriberid bigint)
RETURNS void AS
$BODY$
DECLARE
--Select All Companies for the subscriber entered
c_company CURSOR FOR
SELECT company_id
from PSM_COMPANY_PROFILE
where is_BUYER = 1
and subscriber_id=subscriberID;
v_company c_company%ROWTYPE;
counter bigint :=1;
BEGIN
open c_company;
loop
fetch c_company into v_company;
IF NOT FOUND THEN EXIT; END IF; -- apply on c_company
--insert in PDOC_CHANGE_HIST_HEADER
insert into PDOC_CHANGE_HIST_HEADER
(SUBSCRIBER_ID, COMPANY_ID, DOCUMENT_ID,
DESCRIPTION, COMMENTS,
CREATION_DATE, CREATION_USER, UPDATE_DATE, UPDATE_USER )
values(subscriberID, v_company.company_id, 15197,
'Buyer PO History', '',
LOCALTIMESTAMP, 'AppAdmin', LOCALTIMESTAMP, 'AppAdmin' );
--First Row insert in PDOC_CHANGE_HIST_DETAIL
insert into PDOC_CHANGE_HIST_DETAIL
(SUBSCRIBER_ID, COMPANY_ID, DOCUMENT_ID, ROW_NUMBER,
TABLE_NAME, TABLE_COLUMN_NAME, FIELD_ID,
CREATION_DATE, CREATION_USER, UPDATE_DATE, UPDATE_USER )
values(subscriberID, v_company.company_id, 15197, 1,
'PPO_MASTER_HEADER', 'SUPPLIER_CONTACT_ID', 15385,
LOCALTIMESTAMP, 'AppAdmin', LOCALTIMESTAMP, 'AppAdmin' );
--Second Row insert in PDOC_CHANGE_HIST_DETAIL
insert into PDOC_CHANGE_HIST_DETAIL
(SUBSCRIBER_ID, COMPANY_ID, DOCUMENT_ID, ROW_NUMBER,
TABLE_NAME, TABLE_COLUMN_NAME, FIELD_ID,
CREATION_DATE, CREATION_USER, UPDATE_DATE, UPDATE_USER )
values(subscriberID, v_company.company_id, 15197, 2,
'PPO_MASTER_HEADER', 'STATUS', 15192,
LOCALTIMESTAMP, 'AppAdmin', LOCALTIMESTAMP, 'AppAdmin' );
--Third Row insert in PDOC_CHANGE_HIST_DETAIL
insert into PDOC_CHANGE_HIST_DETAIL
(SUBSCRIBER_ID, COMPANY_ID, DOCUMENT_ID, ROW_NUMBER,
TABLE_NAME, TABLE_COLUMN_NAME, FIELD_ID,
CREATION_DATE, CREATION_USER, UPDATE_DATE, UPDATE_USER )
values(subscriberID, v_company.company_id, 15197, 3,
'PPO_MASTER_HEADER', 'APPROVAL_PERSON_ID', 20883,
LOCALTIMESTAMP, 'AppAdmin', LOCALTIMESTAMP, 'AppAdmin' );
counter := counter +1;
/*if it is more than 1000 record then commit and reset the counter value*/
if( counter = 1000) then
commit;
counter :=1;
end if;
end loop;
commit;
close c_company;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION add_po_doc_hist(bigint)
OWNER TO postgres;
--------------------------------------------------------------------------------------------------------------
Regards,
Sachin
