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