Thread: Error in declaring %rowtype for recurrence
Dear Friends,
Postgres 7.3.4 on RH Linux 7.2
I am having an table name recurrence under the schema crm. While I refer it as %rowtype I have error msgs.
CREATE OR REPLACE FUNCTION crm.fn_rowtypetest() RETURNS varchar AS
'DECLARE
rec_recurrence crm.recurrences%rowtype;
BEGIN
FOR rec_recurrence IN SELECT * FROM crm.recurrences LOOP
RAISE NOTICE 'Hai';
END LOOP;
RETURN 'DONE';
END;'
LANGUAGE 'plpgsql' VOLATILE;
'DECLARE
rec_recurrence crm.recurrences%rowtype;
BEGIN
FOR rec_recurrence IN SELECT * FROM crm.recurrences LOOP
RAISE NOTICE 'Hai';
END LOOP;
RETURN 'DONE';
END;'
LANGUAGE 'plpgsql' VOLATILE;
while I execute
select crm.fn_rowtypetest()
WARNING: plpgsql: ERROR during compile of fn_rowtypetest near line 0
ERROR: cache lookup for type 0 of recurrences.........pg.dropped.16........ failed
The same function works if I declare that via an record type
CREATE OR REPLACE FUNCTION crm.fn_rowtypetest() RETURNS varchar AS
'DECLARE
rec_recurrence record; ------------------------------------------------------------------------------------------ Note the change here
BEGIN
FOR rec_recurrence IN SELECT * FROM crm.recurrences LOOP
RAISE NOTICE 'Hai';
END LOOP;
RETURN 'DONE';
END;'
LANGUAGE 'plpgsql' VOLATILE;
'DECLARE
rec_recurrence record; ------------------------------------------------------------------------------------------ Note the change here
BEGIN
FOR rec_recurrence IN SELECT * FROM crm.recurrences LOOP
RAISE NOTICE 'Hai';
END LOOP;
RETURN 'DONE';
END;'
LANGUAGE 'plpgsql' VOLATILE;
Why is that so? Anyone can please clear me.
Regards
Kumar
"Kumar" <sgnerd@yahoo.com.sg> writes:
> WARNING: plpgsql: ERROR during compile of fn_rowtypetest near line 0
> ERROR: cache lookup for type 0 of recurrences.........pg.dropped.16.......=
> . failed
PG 7.4 is a little better at dealing with dropped columns in plpgsql
rowtypes.
regards, tom lane
I am sorry. I didn't get you. Can u pls tell me more Thanks kumar ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Kumar" <sgnerd@yahoo.com.sg> Cc: "psql" <pgsql-sql@postgresql.org> Sent: Tuesday, February 10, 2004 2:02 PM Subject: Re: [SQL] Error in declaring %rowtype for recurrence > "Kumar" <sgnerd@yahoo.com.sg> writes: > > WARNING: plpgsql: ERROR during compile of fn_rowtypetest near line 0 > > ERROR: cache lookup for type 0 of recurrences.........pg.dropped.16.......= > > . failed > > PG 7.4 is a little better at dealing with dropped columns in plpgsql > rowtypes. > > regards, tom lane