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  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: "A. Kretschmer"
Дата:
Сообщение: Re: Triggers | rules for column updates
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Function problem after alter table