Function problem after alter table - Mailing list pgsql-novice

From Sean Dooley
Subject Function problem after alter table
Date
Msg-id 47CBEBCC.8060207@st-andrews.ac.uk
Whole thread Raw
Responses Re: Function problem after alter table  (Tom Lane <tgl@sss.pgh.pa.us>)
List 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 by date:

Previous
From: "A. Kretschmer"
Date:
Subject: Re: Triggers | rules for column updates
Next
From: Tom Lane
Date:
Subject: Re: Function problem after alter table