Postgres Pro
Downloads
Home   >   mailing lists

Thread: Dynamic SQL in Function

Dynamic SQL in Function

From
rwade@uci.edu
Date:
If I have built a dynamic sql statement in a function, how do i return it
as a ref cursor?

Re: Dynamic SQL in Function

From
Merlin Moncure
Date:
On Wed, Apr 22, 2009 at 12:29 PM,  <rwade@uci.edu> wrote:
> If I have built a dynamic sql statement in a function, how do i return it
> as a ref cursor?

CREATE FUNCTION reffunc(_ref) RETURNS refcursor AS
$$
BEGIN
    OPEN _ref FOR execute 'SELECT * from foo';
    RETURN _ref;
END;
$$ LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

Re: Dynamic SQL in Function

From
Merlin Moncure
Date:
n Wed, Apr 22, 2009 at 2:54 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Wed, Apr 22, 2009 at 12:29 PM,  <rwade@uci.edu> wrote:
>> If I have built a dynamic sql statement in a function, how do i return it
>> as a ref cursor?
>
> CREATE FUNCTION reffunc(_ref) RETURNS refcursor AS

oops

CREATE FUNCTION reffunc(_ref refcursor) RETURNS refcursor AS

merlin

Re: Dynamic SQL in Function

From
rwade@uci.edu
Date:
Is this possible without having to pass in the _ref parameter?

Thanks

Ryan

> On Wed, Apr 22, 2009 at 12:29 PM,  <rwade@uci.edu> wrote:
>> If I have built a dynamic sql statement in a function, how do i return
>> it
>> as a ref cursor?
>
> CREATE FUNCTION reffunc(_ref) RETURNS refcursor AS
> $$
> BEGIN
>     OPEN _ref FOR execute 'SELECT * from foo';
>     RETURN _ref;
> END;
> $$ LANGUAGE plpgsql;
>
> BEGIN;
> SELECT reffunc('funccursor');
> FETCH ALL IN funccursor;
> COMMIT;
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>

Re: Dynamic SQL in Function

From
Merlin Moncure
Date:
On Thu, Apr 23, 2009 at 11:36 AM,  <rwade@uci.edu> wrote:
>> On Wed, Apr 22, 2009 at 12:29 PM,  <rwade@uci.edu> wrote:
>>> If I have built a dynamic sql statement in a function, how do i return
>>> it
>>> as a ref cursor?
>>
>> CREATE FUNCTION reffunc(_ref) RETURNS refcursor AS
>> $$
>> BEGIN
>>     OPEN _ref FOR execute 'SELECT * from foo';
>>     RETURN _ref;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> BEGIN;
>> SELECT reffunc('funccursor');
>> FETCH ALL IN funccursor;
>> COMMIT;
>>
> Is this possible without having to pass in the _ref parameter?

sure:
CREATE FUNCTION reffunc() RETURNS refcursor AS
$$
  DECLARE
    _ref REFCURSOR default 'merlin';
  BEGIN
    OPEN _ref FOR execute 'SELECT * from foo';
    RETURN _ref;
  END;
$$ LANGUAGE plpgsql;

one thing I also forgot: refcursors are limited to transaction
lifetime...make sure to wrap the function call with begin...end.

merlin

Re: Dynamic SQL in Function

From
Martin Gainty
Date:

_________________________________________________________________
Rediscover Hotmail®: Get e-mail storage that grows with you.
http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage2_042009

function in pgAdmin

From
rwade@uci.edu
Date:
How do I view the result set of a function that returns a refcursor in
pgAdmin?

I am trying to test it in pgadmin my calling it like this, but I can't see
the result set, it only says:

Query result with 1 rows discarded.
Query result with 328 rows discarded.

Query returned successfully with no result in 32 ms.

How I'm calling in pgAdmin:

begin;

select select_movies_by_web_search('Test', 2008, '', null, null, null);
fetch all in moviecursor;
commit;

Here is my function:

CREATE OR REPLACE FUNCTION select_movies_by_web_search(title character
varying(100),
   movieyear integer, director_rest_of_name character varying(50),
   director_last_name character varying(50), star_first_name character
varying(50),
   star_last_name character varying(50))
  RETURNS refcursor AS
$BODY$
   DECLARE
      rc refcursor default 'moviecursor';
      sql character varying(2000);
   BEGIN
     sql = 'SELECT id, title, "year", director_rest_of_name,
        director_last_name, banner_url, trailer_url
    FROM movies';
     open rc for EXECUTE sql;

     RETURN rc;
   END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION select_movies_by_web_search(character varying, integer,
   character varying, character varying, character varying,
   character varying) OWNER TO testuser;

Thanks

Ryan