Hi, 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