@440bx
Here is the PostgreSQL function I wrote:
CREATE OR REPLACE FUNCTION planning_json(id_json text, date_debut date, date_fin date)
RETURNS TABLE(resultat json)
LANGUAGE plpgsql
LEAKPROOF
AS $function$
--
-- Variables
DECLARE i text;
--
BEGIN
--
-- id_json has the following form: {"ID":[17, 18]}
-- Loop through the integer IDs in the array
FOR i IN SELECT * FROM json_array_elements_text(id_json::json->'ID')
LOOP
--
RETURN QUERY
--
SELECT json_agg(_) FROM
(
SELECT a.jours, b.nom_complet, b.description, b.debut, b.fin, b.duree
FROM days_range(date_debut, date_fin) a
INNER JOIN
(
SELECT s.prenom || ' ' || s.nom as nom_complet, e.description,
cast(date_trunc('day', e.debut) AS date) AS date_activite,
cast("time"(e.debut) AS text) AS debut,
cast("time"(e.fin) AS text) AS fin,
cast(date_trunc('minutes', e.fin - e.debut) AS text) AS duree
FROM tvp_event e
INNER JOIN salarie_emploi sh ON sh.emploi_id = e.emploi_id
INNER JOIN emploi h ON h.emploi_id = sh.emploi_id
INNER JOIN salarie s on s.salarie_id = sh.salarie_id
WHERE e.mfd = 'Non' AND s.mfd = 'Non' AND h.mfd = 'Non'
AND h.emploi_id = i::integer
ORDER BY date_activite, debut DESC
)
AS b ON a.jours = b.date_activite
) AS _;
--
END LOOP;
--
END;
$function$
The key part is the way the
PostgreSQL function json_array_elements_text is used in the FOR....LOOP
-- id_json has the following form: {"ID":[17, 18]}
-- Loop through the integer IDs in the array
FOR i IN SELECT * FROM json_array_elements_text(id_json::json->'ID')
Since the loop returns a JSON row for each ID, I aggregate the rows into
one big JSON array when I call this aggregate function from the REST server
CREATE OR REPLACE FUNCTION agg_planning_json(id_json text, date_debut date, date_fin date)
RETURNS text
LANGUAGE sql
AS $function$
SELECT json_agg(resultset)::text FROM
(SELECT * FROM planning_json($1, $2, $3)) as resultset;
$function$
SERVER SIDEThis is the REST server code snippet that calls the aggregate function and sends one big JSON array to the clients: Lazarus, Browser, SQL tool etc
Res := aServer.fDbProps.Execute(Format('select * from agg_planning_json(%s, %s, %s)',
[QuotedStr(IDList), QuotedStr(DateDebut), QuotedStr(DateFin)]), []);
while Res.Step do
Result := Res.ColumnUTF8('agg_planning_json');
LAZARUS CLIENT SIDEClient-side code snippet that sends the request with the JSON array of IDs to the REST server. strIDList below is a string variable that contains a JSON array in the form required by the PostgreSQL function e.g '{"ID": [19,20]}'
// Just one line to send the request to the server
FRestResponse[0].Response := FRestClient.RestMethods.Planning(strIDList, SQLDate(dtDateDebut.Date), SQLDate(dtDateFin.Date));
That's it. I hope that helps.
Cheers,
JD