> adrian.kla...@aklaver.com wrote:
>
>> b...@yugabyte.com replied to laurenz.a...@cybertec.at:
>>
>> Thanks for the link to your SQL file at the line where you get the row count
>> in the way that you describe... I noted that neither of these functions has
>
n and how to use it,
> not how I went about implementing it. You are interpreting too much. I just
> wanted to show you a case where scrollable cursors can be useful.
>
>> The upshot, therefore, is that I'm afraid that I can only guess at why you
>> use "o
~200 lines). And
I saw that you use the cursor mechanism that we're discussing here in
only one other function, "db_migrate_refresh()" (~480 lines). But this
second one doesn't use the "move forward all" device to get a row count.
I looked only at these two functions.
to show you a case where
scrollable cursors
can be useful.
> The upshot, therefore, is that I'm afraid that I can only guess at why you
> use "open, fetch, close"
> on a refcursor rather than an ordinary cursor for loop. After all, you use
> the technique only
> to tr
igrate_refresh()" (~480 lines). But this second one doesn't use the "move
forward all" device to get a row count. I looked only at these two functions.
I noted that neither of these functions has a refcursor formal argument and
that, rather, you open (i.e. create) each of
equivalent of
> "refcursor".
> So I had never come across use cases where this was beneficial. I wanted,
> therefore,
> to hear about some. I thought that insights here would help me understand the
> mechanics.
> But I didn't get anything beyond "Scrollability is
f testing before responding
to make sure that the mental model that I've formed for myself is consistent with these.
You may not be interested in what follows. But, anyway, here goes.
— I happen to have many years of experience with Oracle Database and PL/SQL. The latter
has analogous feature
with psychology. I probably can't explain this
convincingly. That's why it's taken me a long time to respond. I also had to do
lots of testing before responding to make sure that the mental model that I've
formed for myself is consistent with these.
You may not be inte
On 2023-03-16 11:52:47 +0100, Dominique Devienne wrote:
> On Thu, Mar 16, 2023 at 11:15 AM Pavel Stehule
> wrote:
> čt 16. 3. 2023 v 9:55 odesílatel Dominique Devienne
> napsal:
> That's a nice way to put it Pavel.
> And to have it both ways, use COPY in binary protocol?
/SQL's packages, you have no
mechanism to hold the opened cursor variable between successive server
calls.)"
What part of this:
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SE
čt 16. 3. 2023 v 11:52 odesílatel Dominique Devienne
napsal:
> On Thu, Mar 16, 2023 at 11:15 AM Pavel Stehule
> wrote:
>
>> čt 16. 3. 2023 v 9:55 odesílatel Dominique Devienne
>> napsal:
>>
>>> On Thu, Mar 16, 2023 at 9:23 AM Pavel Stehule
>>> wrote:
>>>
čt 16. 3. 2023 v 9:18 odesílatel D
On Thu, Mar 16, 2023 at 11:15 AM Pavel Stehule
wrote:
> čt 16. 3. 2023 v 9:55 odesílatel Dominique Devienne
> napsal:
>
>> On Thu, Mar 16, 2023 at 9:23 AM Pavel Stehule
>> wrote:
>>
>>> čt 16. 3. 2023 v 9:18 odesílatel Dominique Devienne
>>> napsal:
>>>
[...] depends on what you value in
čt 16. 3. 2023 v 9:55 odesílatel Dominique Devienne
napsal:
> On Thu, Mar 16, 2023 at 9:23 AM Pavel Stehule
> wrote:
>
>> čt 16. 3. 2023 v 9:18 odesílatel Dominique Devienne
>> napsal:
>>
>>> [...] depends on what you value in a particular situation, latency or
>>> throughput. --DD
>>>
>>
>> cu
On Thu, Mar 16, 2023 at 9:23 AM Pavel Stehule
wrote:
> čt 16. 3. 2023 v 9:18 odesílatel Dominique Devienne
> napsal:
>
>> [...] depends on what you value in a particular situation, latency or
>> throughput. --DD
>>
>
> cursors are optimized for minimal cost of first row, queries are optimized
>
čt 16. 3. 2023 v 9:18 odesílatel Dominique Devienne
napsal:
> On Thu, Mar 16, 2023 at 6:48 AM Laurenz Albe
> wrote:
>
>> As we wrote, some of us think that cursors are useful, and we tried to
>> explain why we think that. If you don't think that cursors are useful,
>> don't use them. We are no
On Thu, Mar 16, 2023 at 6:48 AM Laurenz Albe
wrote:
> As we wrote, some of us think that cursors are useful, and we tried to
> explain why we think that. If you don't think that cursors are useful,
> don't use them. We are not out to convince you otherwise.
>
Perhaps OT (I only skimed this thr
Well, it is simple.
As we wrote, some of us think that cursors are useful, and we tried to
explain why we think that. If you don't think that cursors are useful,
don't use them. We are not out to convince you otherwise.
Yours,
Laurenz Albe
rse, from being able to
use the right vocabulary to express what confused me.
It's very much clearer now than when I started this thread, about twenty-four
hours ago. Here's (some of) what I believe that I now understand.
"refcursor" is a base type, listed in pg_type. Th
> adrian.kla...@aklaver.com wrote:
>
>> b...@yugabyte.com wrote:
>>
>>> laurenz.a...@cybertec.at wrote:
>>>
>>> You seem to think that a client request corresponds to a single database
>>> request
>>
>> …I can’t picture a concrete use case where, not withstanding the "where"
>> restriction th
On 3/15/23 13:37, Bryn Llewellyn wrote:
laurenz.a...@cybertec.at wrote:
Re « You seem to think that a client request corresponds to a single database request », I meant no more than what psql
models when you hit "return"after terminating an ordinary SQL statement with semi-colon (i.e. not a
> laurenz.a...@cybertec.at wrote:
>
>> b...@yugabyte.com wrote:
>>
>> Section "43.7. Cursors” in the PL/pgSQL chapter of the doc
>> (www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS)
>> starts with this:
>>
>> «
>> [...]
>> A more interesting usage is to return a
On Tue, 2023-03-14 at 17:50 -0700, Bryn Llewellyn wrote:
> Section "43.7. Cursors” in the PL/pgSQL chapter of the doc
> (www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS)
> starts with this:
>
> «
> [...]
> A more interesting usage is to return a reference to a cur
's owned by a role that can select from the relevant
table(s) that returns refcursor. And I called it from a subprogram
that's owned by a role that cannot select from the relevant table(s)
to loop through the rows. But I can't convince myself that this
division of labor is useful.
er to read the rows. This provides an efficient way to return large row
>> sets from functions.
>> »
>> On its face, it seems to make sense. And I’ve written a few proof-of-concept
>> tests. For example, I wrote a “security definer” function that's owned by a
>> ro
of-of-concept tests. For
example, I wrote a “security definer” function that's owned by a role that can select
from the relevant table(s) that returns refcursor. And I called it from a subprogram
that's owned by a role that cannot select from the relevant table(s) to loop through the
ro
ote a “security definer” function that's owned by a
role that can select from the relevant table(s) that returns refcursor. And I
called it from a subprogram that's owned by a role that cannot select from the
relevant table(s) to loop through the rows. But I can't convince myself th
On Mon, 2021-03-15 at 13:58 +0200, Yambu wrote:
> May I know why the below takes so long?
>
>
> BEGIN;
> select * from func1() ;
> fetch all from "";
> end;
>
> Select * from func1() ; on its own is very fast
The second just fetches a cursor for the query, the
first executes the query.
Hi
I new to cursors
May I know why the below takes so long?
BEGIN;
select * from func1() ;
fetch all from "";
end;
Select * from func1() ; on its own is very fast
28 matches
Mail list logo
