subject:"\[GENERAL\] Giving error for function"

Re: [GENERAL] Giving error for function

2016-01-12 Thread Tom Lane

Sachin Srivastava  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Giving error for function

2016-01-12 Thread Charles Clavadetscher

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 
> 
> 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*/
> 
&g

[GENERAL] Giving error for function

2016-01-11 Thread Sachin Srivastava

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

Re: [GENERAL] Giving error for function

Re: [GENERAL] Giving error for function

[GENERAL] Giving error for function

3 matches

Site Navigation

Mail list logo

Footer information