Re: refursor from dynamic query

hmm ok I've played a little more with this and I've discovered if I
run the following in psql:

    begin;
      select poptions(1);

      -- output --       <unnamed cursor 18>
      -- output --       1 row(s)

      fetch all in "<unnamed cursor 18>";
    commit;

it spits out the rows I was expecting it to have.  So obviously the
function is returning  a refcursor that contains the data I'd
expected.  My problem is I'm calling this function from a cold fusion
template and I'm receiving an empty query object (which I'm assuming
is the refcursor).  How can I easily get at the records within the
cursor?  Is there something I'm missing?

cheers,
Toby


> Hi all,

>    just subscribed to the list - I've been writing my first
>    function/stored procedure and I've hit a bit of a problem.  I'm
>    trying to build a dynamic query, then execute it but I need to
>    return a refcursor containing the records the dynamic query finds.
>    My code is below - I'd appreciate it if anyone could point me in
>    the right direction....

> CREATE FUNCTION poptions (INTEGER)
> 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';


> cheers,
> Toby

>  ---------------------------------------

>    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


> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

 ---------------------------------------

   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