Lists: | pgsql-general |
---|
From: | Martín Marqués <martin(dot)marques(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | passing cursors from one PL function to another |
Date: | 2011-08-26 00:46:30 |
Message-ID: | CABeG9LtoB148j=nH-i+fcC9VoZV-9cYzijmgtCr7kh5nfu1dhA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
CREATE OR REPLACE FUNCTION prueba_cursor(codigo integer, curCursor refcursor)
RETURNS SETOF refcursor AS
$BODY$
DECLARE
cur alias for $2;
BEGIN
PERFORM mpf.ConstruyeCursorDesdeQuery('cur' ,'SELECT * from
tab1 WHERE field < 11000');
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
CREATE OR REPLACE FUNCTION construyecursordesdequery(refcursor, query text)
RETURNS SETOF refcursor AS
$BODY$
BEGIN
OPEN $1 FOR Select * from tab1 where field < 11000;
RAISE NOTICE '%', $1;
RETURN NEXT $1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
begin;
select * from prueba_cursor4(1, 'h');
end;
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
From: | Martín Marqués <martin(dot)marques(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: passing cursors from one PL function to another |
Date: | 2011-08-26 00:49:59 |
Message-ID: | CABeG9LtaV8MQJm2Y6cV8LuZNLfiGybx5VBKRkuvxP=CEUMLE_g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Sorry, pressed send. :-(
I have, as the functions show below, 2 functions that call one
another, passing a cursor (AFAIK). The problem is that it doesn't
work, giving errors when executing the last SELECT.
Is it posible to pass a cursor from one function to another?
El día 25 de agosto de 2011 21:46, Martín Marqués
<martin(dot)marques(at)gmail(dot)com> escribió:
> CREATE OR REPLACE FUNCTION prueba_cursor(codigo integer, curCursor refcursor)
> RETURNS SETOF refcursor AS
> $BODY$
> DECLARE
> cur alias for $2;
> BEGIN
> PERFORM mpf.ConstruyeCursorDesdeQuery('cur' ,'SELECT * from
> tab1 WHERE field < 11000');
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
> COST 100
> ROWS 1000;
>
> CREATE OR REPLACE FUNCTION construyecursordesdequery(refcursor, query text)
> RETURNS SETOF refcursor AS
> $BODY$
> BEGIN
> OPEN $1 FOR Select * from tab1 where field < 11000;
> RAISE NOTICE '%', $1;
> RETURN NEXT $1;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
> COST 100
> ROWS 1000;
>
> begin;
> select * from prueba_cursor4(1, 'h');
> end;
>
>
>
> --
> Martín Marqués
> select 'martin.marques' || '@' || 'gmail.com'
> DBA, Programador, Administrador
>
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Martín Marqués <martin(dot)marques(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: passing cursors from one PL function to another |
Date: | 2011-08-26 03:04:19 |
Message-ID: | CAHyXU0xu=J3o+JejAT8HzPwdorRu3g+q1JSmtgS2-K1zC6Ma1w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2011/8/25 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
> CREATE OR REPLACE FUNCTION prueba_cursor(codigo integer, curCursor refcursor)
> RETURNS SETOF refcursor AS
> $BODY$
> DECLARE
> cur alias for $2;
> BEGIN
> PERFORM mpf.ConstruyeCursorDesdeQuery('cur' ,'SELECT * from
> tab1 WHERE field < 11000');
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
> COST 100
> ROWS 1000;
>
> CREATE OR REPLACE FUNCTION construyecursordesdequery(refcursor, query text)
> RETURNS SETOF refcursor AS
> $BODY$
> BEGIN
> OPEN $1 FOR Select * from tab1 where field < 11000;
> RAISE NOTICE '%', $1;
> RETURN NEXT $1;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
> COST 100
> ROWS 1000;
>
> begin;
> select * from prueba_cursor4(1, 'h');
> end;
you pretty much had it.
> select * from prueba_cursor4(1, 'h');
should be
> select * from prueba_cursor(1, 'h');
after that, but inside the transaction, you can just do:
fetch all from 'cur';
note neither of your functions need to return setof fwict. you are
returning one cursor, not a set of them.
merlin
From: | Martín Marqués <martin(dot)marques(at)gmail(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: passing cursors from one PL function to another |
Date: | 2011-08-26 10:40:01 |
Message-ID: | CABeG9LsAfEXCBta-RSM2JwALNsbTou3YQMcPu8kMjr37=ss+uA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
El día 26 de agosto de 2011 00:04, Merlin Moncure <mmoncure(at)gmail(dot)com> escribió:
> 2011/8/25 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>> CREATE OR REPLACE FUNCTION prueba_cursor(codigo integer, curCursor refcursor)
>> RETURNS SETOF refcursor AS
>> $BODY$
>> DECLARE
>> cur alias for $2;
>> BEGIN
>> PERFORM mpf.ConstruyeCursorDesdeQuery('cur' ,'SELECT * from
>> tab1 WHERE field < 11000');
>> END;
>> $BODY$
>> LANGUAGE 'plpgsql' VOLATILE
>> COST 100
>> ROWS 1000;>
>> CREATE OR REPLACE FUNCTION construyecursordesdequery(refcursor, query text)
>> RETURNS SETOF refcursor AS
>> $BODY$
>> BEGIN
>> OPEN $1 FOR Select * from tab1 where field < 11000;
>> RAISE NOTICE '%', $1;
>> RETURN NEXT $1;
>> END;
>> $BODY$
>> LANGUAGE 'plpgsql' VOLATILE
>> COST 100
>> ROWS 1000;
>>
>> begin;
>> select * from prueba_cursor4(1, 'h');
>> end;
>
> you pretty much had it.
>> select * from prueba_cursor4(1, 'h');
> should be
>> select * from prueba_cursor(1, 'h');
>
> after that, but inside the transaction, you can just do:
> fetch all from 'cur';
That was a typo related with copy & paste. Sorry.
> note neither of your functions need to return setof fwict. you are
> returning one cursor, not a set of them.
That's because originally I was trying to get more then one cursor.
Anyway, I was getting an annoying error on a windows server, and now
that I test it on my Linux installation it works like a charm.
Could it be that I was making changes to the functions and not
dropping them before recreating?
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Martín Marqués <martin(dot)marques(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: passing cursors from one PL function to another |
Date: | 2011-08-26 12:15:59 |
Message-ID: | CAHyXU0wdBURgRbQWfZAM8Lxfoc4WYYpi-HAA-TzymiD9YiUphw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2011/8/26 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
> El día 26 de agosto de 2011 00:04, Merlin Moncure <mmoncure(at)gmail(dot)com> escribió:
>> 2011/8/25 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>>> CREATE OR REPLACE FUNCTION prueba_cursor(codigo integer, curCursor refcursor)
>>> RETURNS SETOF refcursor AS
>>> $BODY$
>>> DECLARE
>>> cur alias for $2;
>>> BEGIN
>>> PERFORM mpf.ConstruyeCursorDesdeQuery('cur' ,'SELECT * from
>>> tab1 WHERE field < 11000');
>>> END;
>>> $BODY$
>>> LANGUAGE 'plpgsql' VOLATILE
>>> COST 100
>>> ROWS 1000;>
>>> CREATE OR REPLACE FUNCTION construyecursordesdequery(refcursor, query text)
>>> RETURNS SETOF refcursor AS
>>> $BODY$
>>> BEGIN
>>> OPEN $1 FOR Select * from tab1 where field < 11000;
>>> RAISE NOTICE '%', $1;
>>> RETURN NEXT $1;
>>> END;
>>> $BODY$
>>> LANGUAGE 'plpgsql' VOLATILE
>>> COST 100
>>> ROWS 1000;
>>>
>>> begin;
>>> select * from prueba_cursor4(1, 'h');
>>> end;
>>
>> you pretty much had it.
>>> select * from prueba_cursor4(1, 'h');
>> should be
>>> select * from prueba_cursor(1, 'h');
>>
>> after that, but inside the transaction, you can just do:
>> fetch all from 'cur';
>
> That was a typo related with copy & paste. Sorry.
>
>> note neither of your functions need to return setof fwict. you are
>> returning one cursor, not a set of them.
>
> That's because originally I was trying to get more then one cursor.
>
> Anyway, I was getting an annoying error on a windows server, and now
> that I test it on my Linux installation it works like a charm.
>
> Could it be that I was making changes to the functions and not
> dropping them before recreating?
not likely -- got the error text?.
merlin
From: | Martín Marqués <martin(dot)marques(at)gmail(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: passing cursors from one PL function to another |
Date: | 2011-08-29 16:48:48 |
Message-ID: | CABeG9LsUO_B+NrrCOCEoVu417zVRAO7b7_j=dZ8A=OqYWVXKyg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
El día 26 de agosto de 2011 09:15, Merlin Moncure <mmoncure(at)gmail(dot)com> escribió:
> 2011/8/26 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>> El día 26 de agosto de 2011 00:04, Merlin Moncure <mmoncure(at)gmail(dot)com> escribió:
>>> 2011/8/25 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>>>> CREATE OR REPLACE FUNCTION prueba_cursor(codigo integer, curCursor refcursor)
>>>> RETURNS SETOF refcursor AS
>>>> $BODY$
>>>> DECLARE
>>>> cur alias for $2;
>>>> BEGIN
>>>> PERFORM mpf.ConstruyeCursorDesdeQuery('cur' ,'SELECT * from
>>>> tab1 WHERE field < 11000');
>>>> END;
>>>> $BODY$
>>>> LANGUAGE 'plpgsql' VOLATILE
>>>> COST 100
>>>> ROWS 1000;>
>>>> CREATE OR REPLACE FUNCTION construyecursordesdequery(refcursor, query text)
>>>> RETURNS SETOF refcursor AS
>>>> $BODY$
>>>> BEGIN
>>>> OPEN $1 FOR Select * from tab1 where field < 11000;
>>>> RAISE NOTICE '%', $1;
>>>> RETURN NEXT $1;
>>>> END;
>>>> $BODY$
>>>> LANGUAGE 'plpgsql' VOLATILE
>>>> COST 100
>>>> ROWS 1000;
>>>>
>>>> begin;
>>>> select * from prueba_cursor4(1, 'h');
>>>> end;
>>>
>>> you pretty much had it.
>>>> select * from prueba_cursor4(1, 'h');
>>> should be
>>>> select * from prueba_cursor(1, 'h');
>>>
>>> after that, but inside the transaction, you can just do:
>>> fetch all from 'cur';
>>
>> That was a typo related with copy & paste. Sorry.
>>
>>> note neither of your functions need to return setof fwict. you are
>>> returning one cursor, not a set of them.
>>
>> That's because originally I was trying to get more then one cursor.
>>
>> Anyway, I was getting an annoying error on a windows server, and now
>> that I test it on my Linux installation it works like a charm.
>>
>> Could it be that I was making changes to the functions and not
>> dropping them before recreating?
>
> not likely -- got the error text?.
The error is version related. On 8.4, it works great. But with 8.3
(which is the version being used in production) I get this:
# select * from prueba_cursor(1, 'a');
ERROR: se llamó una función que retorna un conjunto en un contexto
que no puede aceptarlo
CONTEXTO: PL/pgSQL function "construyecursordesdequery" line 3 at RETURN NEXT
sentencia SQL: «SELECT construyeCursorDesdeQuery( $1 ,'SELECT * from
tab1 WHERE field < 11000')»
PL/pgSQL function "prueba_cursor" line 3 at PERFORM
Why does it work on 8.4 and not on 8.3? Any work around that doesn't
involve upgradeing the DB server?
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
From: | Martín Marqués <martin(dot)marques(at)gmail(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: passing cursors from one PL function to another |
Date: | 2011-08-29 18:24:55 |
Message-ID: | CABeG9Luu7nabio+F=oV2mBD-T9+3FaOdkuuyXpmyQLNFfj+GRw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Actually, what we are trying to do is return 2 recordsets with the
same function call (simulate SP from SQL Server returning 2
recordsets).
I found out that I had to do PERFORM * FROM construyecursordesdequery($1, query)
which works now, but can't run 2 different queries on the same cursor.
I was able to do it on 8.4, but not on 8.3.
El día 29 de agosto de 2011 13:48, Martín Marqués
<martin(dot)marques(at)gmail(dot)com> escribió:
> El día 26 de agosto de 2011 09:15, Merlin Moncure <mmoncure(at)gmail(dot)com> escribió:
>> 2011/8/26 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>>> El día 26 de agosto de 2011 00:04, Merlin Moncure <mmoncure(at)gmail(dot)com> escribió:
>>>> 2011/8/25 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>>>>> CREATE OR REPLACE FUNCTION prueba_cursor(codigo integer, curCursor refcursor)
>>>>> RETURNS SETOF refcursor AS
>>>>> $BODY$
>>>>> DECLARE
>>>>> cur alias for $2;
>>>>> BEGIN
>>>>> PERFORM mpf.ConstruyeCursorDesdeQuery('cur' ,'SELECT * from
>>>>> tab1 WHERE field < 11000');
>>>>> END;
>>>>> $BODY$
>>>>> LANGUAGE 'plpgsql' VOLATILE
>>>>> COST 100
>>>>> ROWS 1000;>
>>>>> CREATE OR REPLACE FUNCTION construyecursordesdequery(refcursor, query text)
>>>>> RETURNS SETOF refcursor AS
>>>>> $BODY$
>>>>> BEGIN
>>>>> OPEN $1 FOR Select * from tab1 where field < 11000;
>>>>> RAISE NOTICE '%', $1;
>>>>> RETURN NEXT $1;
>>>>> END;
>>>>> $BODY$
>>>>> LANGUAGE 'plpgsql' VOLATILE
>>>>> COST 100
>>>>> ROWS 1000;
>>>>>
>>>>> begin;
>>>>> select * from prueba_cursor4(1, 'h');
>>>>> end;
>>>>
>>>> you pretty much had it.
>>>>> select * from prueba_cursor4(1, 'h');
>>>> should be
>>>>> select * from prueba_cursor(1, 'h');
>>>>
>>>> after that, but inside the transaction, you can just do:
>>>> fetch all from 'cur';
>>>
>>> That was a typo related with copy & paste. Sorry.
>>>
>>>> note neither of your functions need to return setof fwict. you are
>>>> returning one cursor, not a set of them.
>>>
>>> That's because originally I was trying to get more then one cursor.
>>>
>>> Anyway, I was getting an annoying error on a windows server, and now
>>> that I test it on my Linux installation it works like a charm.
>>>
>>> Could it be that I was making changes to the functions and not
>>> dropping them before recreating?
>>
>> not likely -- got the error text?.
>
> The error is version related. On 8.4, it works great. But with 8.3
> (which is the version being used in production) I get this:
>
>
> # select * from prueba_cursor(1, 'a');
> ERROR: se llamó una función que retorna un conjunto en un contexto
> que no puede aceptarlo
> CONTEXTO: PL/pgSQL function "construyecursordesdequery" line 3 at RETURN NEXT
> sentencia SQL: «SELECT construyeCursorDesdeQuery( $1 ,'SELECT * from
> tab1 WHERE field < 11000')»
> PL/pgSQL function "prueba_cursor" line 3 at PERFORM
>
>
> Why does it work on 8.4 and not on 8.3? Any work around that doesn't
> involve upgradeing the DB server?
>
> --
> Martín Marqués
> select 'martin.marques' || '@' || 'gmail.com'
> DBA, Programador, Administrador
>
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Martín Marqués <martin(dot)marques(at)gmail(dot)com> |
Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: passing cursors from one PL function to another |
Date: | 2011-08-29 18:28:11 |
Message-ID: | CAFj8pRDPtbsHBAd=SubiuioJmDS1ZUQg845TH7EvNSoikQtWNQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2011/8/29 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
> Actually, what we are trying to do is return 2 recordsets with the
> same function call (simulate SP from SQL Server returning 2
> recordsets).
>
> I found out that I had to do PERFORM * FROM construyecursordesdequery($1, query)
> which works now, but can't run 2 different queries on the same cursor.
>
in your example you use a static cursor.
Pavel
> I was able to do it on 8.4, but not on 8.3.
>
> El día 29 de agosto de 2011 13:48, Martín Marqués
> <martin(dot)marques(at)gmail(dot)com> escribió:
>> El día 26 de agosto de 2011 09:15, Merlin Moncure <mmoncure(at)gmail(dot)com> escribió:
>>> 2011/8/26 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>>>> El día 26 de agosto de 2011 00:04, Merlin Moncure <mmoncure(at)gmail(dot)com> escribió:
>>>>> 2011/8/25 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>>>>>> CREATE OR REPLACE FUNCTION prueba_cursor(codigo integer, curCursor refcursor)
>>>>>> RETURNS SETOF refcursor AS
>>>>>> $BODY$
>>>>>> DECLARE
>>>>>> cur alias for $2;
>>>>>> BEGIN
>>>>>> PERFORM mpf.ConstruyeCursorDesdeQuery('cur' ,'SELECT * from
>>>>>> tab1 WHERE field < 11000');
>>>>>> END;
>>>>>> $BODY$
>>>>>> LANGUAGE 'plpgsql' VOLATILE
>>>>>> COST 100
>>>>>> ROWS 1000;>
>>>>>> CREATE OR REPLACE FUNCTION construyecursordesdequery(refcursor, query text)
>>>>>> RETURNS SETOF refcursor AS
>>>>>> $BODY$
>>>>>> BEGIN
>>>>>> OPEN $1 FOR Select * from tab1 where field < 11000;
>>>>>> RAISE NOTICE '%', $1;
>>>>>> RETURN NEXT $1;
>>>>>> END;
>>>>>> $BODY$
>>>>>> LANGUAGE 'plpgsql' VOLATILE
>>>>>> COST 100
>>>>>> ROWS 1000;
>>>>>>
>>>>>> begin;
>>>>>> select * from prueba_cursor4(1, 'h');
>>>>>> end;
>>>>>
>>>>> you pretty much had it.
>>>>>> select * from prueba_cursor4(1, 'h');
>>>>> should be
>>>>>> select * from prueba_cursor(1, 'h');
>>>>>
>>>>> after that, but inside the transaction, you can just do:
>>>>> fetch all from 'cur';
>>>>
>>>> That was a typo related with copy & paste. Sorry.
>>>>
>>>>> note neither of your functions need to return setof fwict. you are
>>>>> returning one cursor, not a set of them.
>>>>
>>>> That's because originally I was trying to get more then one cursor.
>>>>
>>>> Anyway, I was getting an annoying error on a windows server, and now
>>>> that I test it on my Linux installation it works like a charm.
>>>>
>>>> Could it be that I was making changes to the functions and not
>>>> dropping them before recreating?
>>>
>>> not likely -- got the error text?.
>>
>> The error is version related. On 8.4, it works great. But with 8.3
>> (which is the version being used in production) I get this:
>>
>>
>> # select * from prueba_cursor(1, 'a');
>> ERROR: se llamó una función que retorna un conjunto en un contexto
>> que no puede aceptarlo
>> CONTEXTO: PL/pgSQL function "construyecursordesdequery" line 3 at RETURN NEXT
>> sentencia SQL: «SELECT construyeCursorDesdeQuery( $1 ,'SELECT * from
>> tab1 WHERE field < 11000')»
>> PL/pgSQL function "prueba_cursor" line 3 at PERFORM
>>
>>
>> Why does it work on 8.4 and not on 8.3? Any work around that doesn't
>> involve upgradeing the DB server?
>>
>> --
>> Martín Marqués
>> select 'martin.marques' || '@' || 'gmail.com'
>> DBA, Programador, Administrador
>>
>
>
>
> --
> Martín Marqués
> select 'martin.marques' || '@' || 'gmail.com'
> DBA, Programador, Administrador
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From: | Martín Marqués <martin(dot)marques(at)gmail(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: passing cursors from one PL function to another |
Date: | 2011-08-29 18:52:12 |
Message-ID: | CABeG9LvwcSFY+6LYFE0a6zfLybrZpOvGKdNaJfSdbb_QTM=qfg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
El día 29 de agosto de 2011 15:28, Pavel Stehule
<pavel(dot)stehule(at)gmail(dot)com> escribió:
> 2011/8/29 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>> Actually, what we are trying to do is return 2 recordsets with the
>> same function call (simulate SP from SQL Server returning 2
>> recordsets).
>>
>> I found out that I had to do PERFORM * FROM construyecursordesdequery($1, query)
>> which works now, but can't run 2 different queries on the same cursor.
>>
>
> in your example you use a static cursor.
As opposed to? I see no way to define a cursor not-static.
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Martín Marqués <martin(dot)marques(at)gmail(dot)com> |
Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: passing cursors from one PL function to another |
Date: | 2011-08-29 18:52:54 |
Message-ID: | CAFj8pRAS9Mc2SMFdCu4F6Ue2Y6q1M0TiDwp1J6qFWWLwRDLmcw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2011/8/29 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
> El día 29 de agosto de 2011 15:28, Pavel Stehule
> <pavel(dot)stehule(at)gmail(dot)com> escribió:
>> 2011/8/29 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>>> Actually, what we are trying to do is return 2 recordsets with the
>>> same function call (simulate SP from SQL Server returning 2
>>> recordsets).
>>>
>>> I found out that I had to do PERFORM * FROM construyecursordesdequery($1, query)
>>> which works now, but can't run 2 different queries on the same cursor.
>>>
>>
>> in your example you use a static cursor.
>
> As opposed to? I see no way to define a cursor not-static.
refcursors can be dynamic defined
Pavel
>
> --
> Martín Marqués
> select 'martin.marques' || '@' || 'gmail.com'
> DBA, Programador, Administrador
>
From: | Martín Marqués <martin(dot)marques(at)gmail(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: passing cursors from one PL function to another |
Date: | 2011-08-29 19:02:51 |
Message-ID: | CABeG9LtvYhnsVSXf196yzhkOOeovTTiq9Q6YtDSus7dWw_x=Xw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
El día 29 de agosto de 2011 15:52, Pavel Stehule
<pavel(dot)stehule(at)gmail(dot)com> escribió:
> 2011/8/29 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>> El día 29 de agosto de 2011 15:28, Pavel Stehule
>> <pavel(dot)stehule(at)gmail(dot)com> escribió:
>>> 2011/8/29 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>>>> Actually, what we are trying to do is return 2 recordsets with the
>>>> same function call (simulate SP from SQL Server returning 2
>>>> recordsets).
>>>>
>>>> I found out that I had to do PERFORM * FROM construyecursordesdequery($1, query)
>>>> which works now, but can't run 2 different queries on the same cursor.
>>>>
>>>
>>> in your example you use a static cursor.
>>
>> As opposed to? I see no way to define a cursor not-static.
>
> refcursors can be dynamic defined
OK, I'm totally lost. How do you define a dynamic cursor? Couldn't
find anything in the manuals.
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Martín Marqués <martin(dot)marques(at)gmail(dot)com> |
Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: passing cursors from one PL function to another |
Date: | 2011-08-29 19:12:35 |
Message-ID: | CAFj8pRA1jJjzm3btPAvMN=n4E6r59AkOGNuUfD_xCQ26U1fw4A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2011/8/29 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
> El día 29 de agosto de 2011 15:52, Pavel Stehule
> <pavel(dot)stehule(at)gmail(dot)com> escribió:
>> 2011/8/29 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>>> El día 29 de agosto de 2011 15:28, Pavel Stehule
>>> <pavel(dot)stehule(at)gmail(dot)com> escribió:
>>>> 2011/8/29 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>>>>> Actually, what we are trying to do is return 2 recordsets with the
>>>>> same function call (simulate SP from SQL Server returning 2
>>>>> recordsets).
>>>>>
>>>>> I found out that I had to do PERFORM * FROM construyecursordesdequery($1, query)
>>>>> which works now, but can't run 2 different queries on the same cursor.
>>>>>
>>>>
>>>> in your example you use a static cursor.
>>>
>>> As opposed to? I see no way to define a cursor not-static.
>>
>> refcursors can be dynamic defined
>
> OK, I'm totally lost. How do you define a dynamic cursor? Couldn't
> find anything in the manuals.
>
DECLARE
curs1 refcursor;
BEGIN
OPEN curs1 FOR EXECUTE 'SELECT ...';
>
> --
> Martín Marqués
> select 'martin.marques' || '@' || 'gmail.com'
> DBA, Programador, Administrador
>
From: | Martín Marqués <martin(dot)marques(at)gmail(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: passing cursors from one PL function to another |
Date: | 2011-08-29 19:42:17 |
Message-ID: | CABeG9Lvc4r9srsuS=v=qCif4C5nLZGWWebWqvx39weujKLvGow@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
El día 29 de agosto de 2011 16:12, Pavel Stehule
<pavel(dot)stehule(at)gmail(dot)com> escribió:
> 2011/8/29 Martín Marqués <martin(dot)marques(at)gmail(dot)com>:
>>>
>>> refcursors can be dynamic defined
>>
>> OK, I'm totally lost. How do you define a dynamic cursor? Couldn't
>> find anything in the manuals.
>>
>
> DECLARE
> curs1 refcursor;
> BEGIN
> OPEN curs1 FOR EXECUTE 'SELECT ...';
OK, not that easy, but I think I got it working.
Thanks alot Pavel!
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador