Thread: Function working in PostgreSQL 8.3.6 and not in 7.4.8
Hello, I've created this function: CREATE OR REPLACE FUNCTION subcategories(int) RETURNS setof int AS ' DECLARE -- declarations category ALIAS FOR $1; category_current record; BEGIN -- function body RETURN QUERY SELECT category as my_output; FOR category_current IN SELECT id FROM is_categories WHERE id_parent=category AND status=''1'' LOOP RETURN QUERY SELECT category_current.id as my_output UNION SELECT * FROM subcategories(category_current.id); END LOOP; END; ' LANGUAGE plpgsql; It works well on my development machine with PostgreSQL 8.3.6 (return subcategories, as its name suggests). This query on a server where there's 7.4.8 installed: SELECT id FROM is_categories WHERE id_parent=1 AND status='1'; does return two rows (as it should), but: SELECT * FROM subcategories(1); returns none and there's no error message either. It's my second function in pg/plsql I've created and I don't really know why there are no results being returned in 7.4.8. Any ideas are appreciated. Thanks in advance. :) Regards, Peter Slapansky
<slapo@centrum.sk> writes: > It's my second function in pg/plsql I've created and I don't really know why there are no results being returned in 7.4.8. RETURN QUERY doesn't exist in 7.4, and unfortunately the syntax checking in that ancient version isn't very tight. In a function returning set, it just ignores anything after RETURN unless it's RETURN NEXT. So it just sees a RETURN command telling it to exit. regards, tom lane
Thanks, that was it indeed :D I found it in the documentation only after your post. Here's the modified function working with 7.4.8, in case somebody would like to know: CREATE OR REPLACE FUNCTION subcategories(int) RETURNS setof int AS ' DECLARE -- declarations category ALIAS FOR $1; category_current record; sub_category_current record; BEGIN -- function body SELECT category as my_output INTO category_current; RETURN NEXT category_current.my_output; FOR category_current IN SELECT id FROM is_categories WHERE id_parent=category AND status=''1'' LOOP FOR sub_category_current IN SELECT subcategories FROM subcategories(category_current.id) LOOP RETURN NEXT sub_category_current.subcategories; END LOOP; END LOOP; RETURN; END; ' LANGUAGE plpgsql; Thanks again :) Regards, Peter Slapansky ______________________________________________________________ > Od: tgl@sss.pgh.pa.us > Komu: slapo@centrum.sk > CC: pgsql-novice@postgresql.org > Datum: 11.04.2009 22:57 > Předmět: Re: [NOVICE] Function working in PostgreSQL 8.3.6 and not in 7.4.8 > ><slapo@centrum.sk> writes: >> It's my second function in pg/plsql I've created and I don't really know why there are no results being returned in 7.4.8. > >RETURN QUERY doesn't exist in 7.4, and unfortunately the syntax checking >in that ancient version isn't very tight. In a function returning set, >it just ignores anything after RETURN unless it's RETURN NEXT. So it >just sees a RETURN command telling it to exit. > > regards, tom lane >