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
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 ? |