Обсуждение: Giving error for function

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