Thread: Giving error for function
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
Hello > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@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
Sachin Srivastava <ssr.teleatlas@gmail.com> writes: > 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 > 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; The error is pointing you here. I believe the problem is that c_company is a cursor, not a table, and as such can't be referenced with %rowtype. I think you could just use RECORD instead ... regards, tom lane