here 'tis:
CREATE FUNCTION poptions (INTEGER,REFCURSOR)
RETURNS REFCURSOR AS '
DECLARE
-- parameters
p_author_id ALIAS FOR $1;
-- local variables
rc REFCURSOR;
row_data poem_option_def%ROWTYPE;
qString varchar(4000);
BEGIN
qString := ''SELECT p.poem_id,p.poem_title'';
FOR row_data IN SELECT option_id,option_name FROM poem_option_def LOOP
qString := qString || '', (SELECT po.setting FROM poem_option po WHERE
po.poem_id = p.poem_id and po.option_id = '' || row_data.option_id || '') AS '' ||
row_data.option_name;
END LOOP;
qString := qString || '' FROM poem p WHERE p.author_id = '' ||
p_author_id;
OPEN rc FOR EXECUTE qString;
RETURN rc;
end;
' LANGUAGE 'plpgsql';
---------------------------------------
Life is poetry - write it in your own words.
---------------------------------------
Toby Tremayne
Senior Technical Consultant
Code Poet and Zen Master of the Heavy Sleep
Lyricist Software
www.lyricist.com.au
0416 048 090
ICQ: 13107913
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=14
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=14
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.14
