Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
None
Description
I'd like to get an advice, how can I achieve pipe function (like Oracle) functionality in MariaDB. I know I can create procedure that returns recordset, that's fine. But the only way is to fill some temporary table and issue select at the end of procedure. That means that full content of recordset should be prepared and then directed to the output. What I'm looking for is possibility to return recordset data gradually: take for example first 100 rows, make additional calculation/aggregation inside the procedure and put the output to the caller. And next 100 rows would be processed only if calling side would issue subsequent fetches. So procedure could be ready to output millions of rows only if caller continue fetching, but it also would be ready to output first rows fast enough. Is there such a possibility?
Oracle syntax example
-- This function returns the current time, independent from the start time of the main query
|
create or replace function get_ts return varchar2 |
is
|
pragma autonomous_transaction;
|
begin
|
commit; |
return to_char(systimestamp); |
end; |
/
|
|
|
create or replace type t_person is object( |
id number,
|
name varchar2(200 char) |
);
|
/
|
|
|
create or replace type t_persons is table of t_person; |
/
|
|
|
create or replace function get_persons return t_persons pipelined |
is
|
begin
|
for i in 1..5 loop |
pipe row(t_person(i, 'name_'||i)); |
dbms_session.sleep(0.1);
|
end loop; |
end; |
/
|
|
|
select id, name, get_ts() from get_persons(); |
ID NAME GET_TS()
|
1 name_1 10-JUL-24 11.42.00.319872000 AM +00:00
|
2 name_2 10-JUL-24 11.42.00.425900000 AM +00:00
|
3 name_3 10-JUL-24 11.42.00.533849000 AM +00:00
|
4 name_4 10-JUL-24 11.42.00.637875000 AM +00:00
|
5 name_5 10-JUL-24 11.42.00.741870000 AM +00:00
|
Attachments
Issue Links
- is part of
-
-
- Stalled
-