What is wrong with my RPC function?
I'm trying to create a function that would get some rows from table1 and then loop over them to count the number of rows corresponding in table 2 and table 3. The goal is to create an object with the titles from table1, and the count of table 2 and 3, for each rows selected from table1
This is my query for now:
declare
nbExercices int;
nbDone int;
tLecons text[];
begin
for row in select * from lecons where statut = true order by ordre asc
loop
select count(id) into nbExercices from exercices where lecons = row.id
select count(id) into nbDone from progression where lecon = row.id
array_append(tLecons, {fr: row.titre_fr, en: row.titre_en, nb: nbExercices, done: nbDone})
end loop
return tLecons
end
I would expect to receive something like that:
[{fr: 'Titre', en:'Title', nb: 12, done: 4}, {fr: 'Titre2', en:'Title2', nb: 18, done: 5}]
This is my query for now:
declare
nbExercices int;
nbDone int;
tLecons text[];
begin
for row in select * from lecons where statut = true order by ordre asc
loop
select count(id) into nbExercices from exercices where lecons = row.id
select count(id) into nbDone from progression where lecon = row.id
array_append(tLecons, {fr: row.titre_fr, en: row.titre_en, nb: nbExercices, done: nbDone})
end loop
return tLecons
end
I would expect to receive something like that:
[{fr: 'Titre', en:'Title', nb: 12, done: 4}, {fr: 'Titre2', en:'Title2', nb: 18, done: 5}]
