Обсуждение: Function problem after alter table
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
Sean Dooley <srd1@st-andrews.ac.uk> writes: > FOR RESULTSET IN > SELECT * FROM item_date WHERE item_id = in_item_id > LOOP > RETURN NEXT resultset; > END LOOP; > 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 This example works for me in PG 8.3. In prior releases I think you'd need to use FOR ... IN EXECUTE to force the SELECT to get re-planned each time through. regards, tom lane
Hello, Please let me know if you can assist.... consider the following table via the following sql statement: SELECT ronum, jobnum, jobcode FROM slrojobs WHERE ronum = '56890' ORDER BY jobnum The above query retrieves the following results.... ronum jobnum jobcode 56890 1 FLPQ 56890 2 W My question is this. I want to list the 'jobcodes' as follows for each ronum in 1 field returned that is comma seperated. FLPQ, W Please let me know if anyone has a solution. Thanks!!!!! :)
SELECT array_to_string( array(SELECT jobcode FROM slrojobs WHERE ronum = '56890' ORDER BY jobnum) ), ', ')
My fault, there was an excess parenthesis. Should be SELECT array_to_string( array(SELECT jobcode FROM slrojobs WHERE ronum = '56890' ORDER BY jobnum) , ', ')