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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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