Giving error for function

Поиск
Список
Период
Сортировка
От Sachin Srivastava
Тема Giving error for function
Дата
Msg-id CAFzqEhJiKZtbQvEM9fkZc9KrzfqytnJrx8Q5rS33qxB0Wr3G=w@mail.gmail.com
обсуждение исходный текст
Ответы Re: Giving error for function  ("Charles Clavadetscher" <clavadetscher@swisspug.org>)
Re: Giving error for function  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: WIP: CoC
Следующее
От: "Regina Obe"
Дата:
Сообщение: Re: WIP: CoC