declare proc printf no check;
[[private]]
proc create_tables()
begin
create table parent_table(
id integer primary key,
u text not null,
v text not null
);
create table child_table(
id integer not null references parent_table(id),
seq integer not null,
a integer not null,
b integer not null,
primary key (id, seq)
);
end;
[[private]]
proc parent(u_ text)
begin
select * from parent_table where u = u_;
end;
[[private]] -- this is the problem
proc child(u_ text)
begin
select T1.id, T2.seq, T2.a, T2.b
from parent_table T1
join child_table T2 on T1.id = T2.id
where u = u_;
end;
-- join together parent and child using 'id'
-- example x_, y_ arguments for illustration only
[[private]]
proc parent_child(u_ text)
begin
out union call parent(u_) join call child(u_) using (id);
end;
[[private]]
proc insert_data()
begin
insert into parent_table values
(1, 'foo', 'goo'),
(2, 'foo', 'stew'),
(3, 'you', 'new'),
(4, 'who', 'moo');
insert into child_table values
(1, 1, 100, 10),
(1, 2, 200, 20),
(2, 1, 300, 30),
(2, 2, 400, 40),
(3, 1, 500, 50),
(4, 1, 600, 60),
(4, 2, 700, 70);
end;
proc go()
begin
call create_tables();
call insert_data();
declare C cursor for call parent_child('foo');
loop fetch C
begin
call printf("id: %d, u:%s, v:%s\n", C.id, C.u, C.v);
declare D cursor for C.child1;
end;
end;