Hello > -----Original Message----- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sachin Srivastava > Sent: Dienstag, 12. Januar 2016 08:40 > To: pgsql-general@postgresql.org >> PG-General Mailing List > <pgsql-general@postgresql.org> > Subject: [GENERAL] Giving error for function > > 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
Probably you need to schema qualify the table name. > > 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
