Function problem after alter table
От | Sean Dooley |
---|---|
Тема | Function problem after alter table |
Дата | |
Msg-id | 47CBEBCC.8060207@st-andrews.ac.uk обсуждение исходный текст |
Ответы |
Re: Function problem after alter table
|
Список | 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
В списке pgsql-novice по дате отправления: