Skip site navigation (1) Skip section navigation (2)

Function problem after alter table

From: Sean Dooley <srd1(at)st-andrews(dot)ac(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Subject: Function problem after alter table
Date: 2008-03-03 12:15:08
Message-ID: 47CBEBCC.8060207@st-andrews.ac.uk (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,
I have a function as defined as so...

===========

CREATE OR REPLACE FUNCTION getitemdates(in_item_id integer) RETURNS 
SETOF item_date
     AS $$
DECLARE
	resultset RECORD;
	old_path text;
BEGIN
	old_path := pg_catalog.current_setting('search_path');
	PERFORM pg_catalog.set_config('search_path', 'public, pg_temp', true);
	FOR RESULTSET IN	
		SELECT * FROM item_date WHERE item_id = in_item_id
	LOOP
		RETURN NEXT resultset;
	END LOOP;

	PERFORM pg_catalog.set_config('search_path', old_path, true);

END;
$$
     LANGUAGE plpgsql STABLE SECURITY DEFINER;

===========


This works fine, returns all the matching data from item_date

However, if I alter the table item_date, for example

ALTER TABLE item_date ADD COLUMN foo text;

When I run the function now, I get the error

ERROR:  wrong record type supplied in RETURN NEXT
CONTEXT:  PL/pgSQL function "getitemdates" line 10 at return next

It seems the item_date rowtype isn't being updated when the item_date 
table is altered. If I then drop the added column, I still get the error 
message. The procedure has completely broken.

Any ideas where I am going wrong?
Thanks,
Sean

Responses

pgsql-novice by date

Next:From: Szentpali JanosDate: 2008-03-03 13:47:58
Subject: Triggers | rules for column updates
Previous:From: Ennio-SrDate: 2008-03-02 18:38:36
Subject: Re: pidof not working for postgresql-8.3 ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group