Re: Giving error for function

Hello

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Sachin Srivastava
> Sent: Dienstag, 12. Januar 2016 08:40
> To: pgsql-general(at)postgresql(dot)org >> PG-General Mailing List <pgsql-general(at)postgresql(dot)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