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

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

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

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