Recent

Author Topic: SOLVED: Call a PostgreSQL function with variadic parameters from Lazarus  (Read 5165 times)

JD

  • Hero Member
  • *****
  • Posts: 1848
Hi there everyone,

a) Background
I wrote a PostgreSQL function with a variadic parameter that has the following signature:

Code: Pascal  [Select][+][-]
  1. CREATE OR REPLACE FUNCTION planning(date_debut date, date_fin date, VARIADIC id_salarie integer[])
  2.  

The third parameter is a variadic integer array parameter of user IDs. The user can run the query using a variable number of IDs.
I can call the function from within pgAdmin4 or any other SQL tool in any of the following ways

Code: Pascal  [Select][+][-]
  1. select * from planning('2018-01-01', '2018-06-30', 19)                  // 3 parameters
  2. select * from planning('2018-01-01', '2018-06-30', 19, 20)            // 4 parameters
  3. select * from planning('2018-01-01', '2018-06-30', variadic array[19, 20])  // 2 parameter + 1 integer array
  4.  

b) Problem
My problem is how to call this function from Lazarus knowing that the user of the application can select a variable number of integer IDs from the client application?

Thanks for your kind assistance,

JD
« Last Edit: September 25, 2018, 01:50:17 pm by JD »
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

Thaddy

  • Hero Member
  • *****
  • Posts: 14204
  • Probably until I exterminate Putin.
Re: Call a PostgreSQL function with variadic parameters from Lazarus
« Reply #1 on: September 24, 2018, 08:50:41 pm »
Array of const comes to mind, as well as varargs. See lastsection of https://freepascal.org/docs-html/ref/refsu69.html
But if it is pure SQL shouldn't it work?
« Last Edit: September 24, 2018, 08:54:37 pm by Thaddy »
Specialize a type, not a var.

JD

  • Hero Member
  • *****
  • Posts: 1848
Re: Call a PostgreSQL function with variadic parameters from Lazarus
« Reply #2 on: September 24, 2018, 09:22:34 pm »
Array of const comes to mind, as well as varargs. See lastsection of https://freepascal.org/docs-html/ref/refsu69.html
But if it is pure SQL shouldn't it work?

I thought of using the array of const too. This problem has a little history as described here in the Synopse forum

https://synopse.info/forum/viewtopic.php?id=4711

All other function calls with simple data types work. It is only when I decided to use complex data types like JSON arrays or in this case a variadic integer parameter to groups requests and use only one request-response cycle between clients and my mORMot server that problems spring up.

This is the code snippet that calls the function in my REST server
Code: Pascal  [Select][+][-]
  1. Res := aServer.fDbProps.Execute(Format('select * from planning(%s, %s, %s)',
  2.    [QuotedStr(DateDebut), QuotedStr(DateFin), ??????? ]), []);  <-- what goes here for the 3rd parameter?
  3. while Res.Step do
  4.    Result := Res.ColumnUTF8('planning');
  5.  

This is the full PostgreSQL 10.5 function:

Code: Pascal  [Select][+][-]
  1. CREATE OR REPLACE FUNCTION planning (
  2.         date_debut date,
  3.         date_fin date,
  4.         VARIADIC id_salarie integer[])
  5.     RETURNS TABLE(resultat json)
  6.     LANGUAGE 'plpgsql'
  7.     COST 100
  8.     VOLATILE LEAKPROOF
  9.     ROWS 1000
  10. AS
  11.  
  12. $BODY$
  13. -- Variables
  14. DECLARE i integer;
  15. --
  16. BEGIN
  17.   --
  18.   -- Loop over all the IDs in the id_salarie array
  19.   --
  20.   -- JSON version of the same FOR..LOOP where id_json has the form : {"ID":[17, 18]}
  21.   -- will loop though the IDs in the JSON array
  22.   -- FOR i IN SELECT * FROM json_array_elements_text(id_json->'ID')
  23.   --
  24.   --
  25.   FOR i IN array_lower(id_salarie, 1) .. array_upper(id_salarie, 1)
  26.   LOOP
  27.     --
  28.     RETURN QUERY
  29.     --
  30.           SELECT json_agg(_) FROM
  31.       (
  32.           SELECT a.jours, b.nom_complet, b.description, b.debut, b.fin, b.duree
  33.           FROM days_range(date_debut, date_fin) a
  34.           INNER JOIN
  35.           (
  36.             SELECT *
  37.             FROM tvp_event e
  38.               INNER JOIN salarie_emploi sh ON sh.emploi_id = e.emploi_id
  39.               INNER JOIN emploi h ON h.emploi_id = sh.emploi_id
  40.               INNER JOIN salarie s on s.salarie_id = sh.salarie_id
  41.             WHERE e.mfd = 'Non' AND s.mfd = 'Non' AND h.mfd = 'Non' AND h.emploi_id = id_salarie[i]
  42.             ORDER BY date_activite, debut DESC
  43.           )
  44.           AS b ON a.jours = b.date_activite
  45.       ) AS _;
  46.     --
  47.   END LOOP;
  48.   --
  49. END;
  50.  
  51. $BODY$;
  52.  

Thanks,

JD
« Last Edit: September 30, 2018, 11:49:52 am by JD »
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

JD

  • Hero Member
  • *****
  • Posts: 1848
Re: Call a PostgreSQL function with variadic parameters from Lazarus
« Reply #3 on: September 25, 2018, 01:49:46 pm »
@Thaddy
I dropped the function with a variadic parameter and replaced it with a JSON array. This time I did not encode the JSON array before sending it to the REST server and it now works in the Lazarus clients as well as when I call the function from an SQL tool or even from my browser.  :D

Thanks a lot,

JD
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

440bx

  • Hero Member
  • *****
  • Posts: 3944
Re: Call a PostgreSQL function with variadic parameters from Lazarus
« Reply #4 on: September 25, 2018, 04:34:02 pm »
I dropped the function with a variadic parameter and replaced it with a JSON array. This time I did not encode the JSON array before sending it to the REST server and it now works in the Lazarus clients as well as when I call the function from an SQL tool or even from my browser.  :D

Thanks a lot,

JD

JD, if possible, I'd like to see how you implemented the solution.  Could you post the code (only the part that is "equivalent" to the code you posted previously) ?

Thanks.
(FPC v3.0.4 and Lazarus 1.8.2) or (FPC v3.2.2 and Lazarus v3.2) on Windows 7 SP1 64bit.

JD

  • Hero Member
  • *****
  • Posts: 1848
Re: SOLVED: Call a PostgreSQL function with variadic parameters from Lazarus
« Reply #5 on: September 25, 2018, 10:47:52 pm »
@440bx

Here is the PostgreSQL function I wrote:

Code: Pascal  [Select][+][-]
  1. CREATE OR REPLACE FUNCTION planning_json(id_json text, date_debut date, date_fin date)
  2.   RETURNS TABLE(resultat json)
  3.   LANGUAGE plpgsql
  4.   LEAKPROOF
  5. AS $function$
  6.  
  7. --
  8. -- Variables
  9. DECLARE i text;
  10.  
  11. --
  12. BEGIN
  13.   --
  14.   -- id_json has the following form: {"ID":[17, 18]}
  15.   -- Loop through the integer IDs in the array
  16.   FOR i IN SELECT * FROM json_array_elements_text(id_json::json->'ID')
  17.   LOOP
  18.     --
  19.     RETURN QUERY
  20.       --
  21.           SELECT json_agg(_) FROM
  22.       (
  23.           SELECT a.jours, b.nom_complet, b.description, b.debut, b.fin, b.duree
  24.           FROM days_range(date_debut, date_fin) a
  25.           INNER JOIN
  26.           (
  27.             SELECT s.prenom || ' ' || s.nom as nom_complet, e.description,
  28.               cast(date_trunc('day', e.debut) AS date) AS date_activite,
  29.               cast("time"(e.debut) AS text) AS debut,
  30.               cast("time"(e.fin) AS text) AS fin,
  31.               cast(date_trunc('minutes', e.fin - e.debut) AS text) AS duree
  32.             FROM tvp_event e
  33.               INNER JOIN salarie_emploi sh ON sh.emploi_id = e.emploi_id
  34.               INNER JOIN emploi h ON h.emploi_id = sh.emploi_id
  35.               INNER JOIN salarie s on s.salarie_id = sh.salarie_id
  36.             WHERE e.mfd = 'Non' AND s.mfd = 'Non' AND h.mfd = 'Non'
  37.                           AND h.emploi_id = i::integer
  38.             ORDER BY date_activite, debut DESC
  39.           )
  40.           AS b ON a.jours = b.date_activite
  41.       ) AS _;
  42.     --
  43.   END LOOP;
  44.   --
  45. END;
  46.  
  47. $function$
  48.  

The key part is the way the PostgreSQL function json_array_elements_text is used in the FOR....LOOP
Code: Pascal  [Select][+][-]
  1.   -- id_json has the following form: {"ID":[17, 18]}
  2.   -- Loop through the integer IDs in the array
  3.   FOR i IN SELECT * FROM json_array_elements_text(id_json::json->'ID')
  4.  

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

Code: Pascal  [Select][+][-]
  1. CREATE OR REPLACE FUNCTION agg_planning_json(id_json text, date_debut date, date_fin date)
  2.  RETURNS text
  3.  LANGUAGE sql
  4. AS $function$
  5.  
  6. SELECT json_agg(resultset)::text FROM  
  7.   (SELECT * FROM planning_json($1, $2, $3)) as resultset;
  8.  
  9. $function$
  10.  

SERVER SIDE
This 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

Code: Pascal  [Select][+][-]
  1.       Res := aServer.fDbProps.Execute(Format('select * from agg_planning_json(%s, %s, %s)',
  2.         [QuotedStr(IDList), QuotedStr(DateDebut), QuotedStr(DateFin)]), []);
  3.       while Res.Step do
  4.         Result := Res.ColumnUTF8('agg_planning_json');
  5.  

LAZARUS CLIENT SIDE
Client-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]}'

Code: Pascal  [Select][+][-]
  1.       // Just one line to send the request to the server
  2.       FRestResponse[0].Response := FRestClient.RestMethods.Planning(strIDList, SQLDate(dtDateDebut.Date), SQLDate(dtDateFin.Date));
  3.  

That's it. I hope that helps.  :D

Cheers,

JD
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

440bx

  • Hero Member
  • *****
  • Posts: 3944
Re: SOLVED: Call a PostgreSQL function with variadic parameters from Lazarus
« Reply #6 on: September 25, 2018, 11:08:03 pm »
JD, that definitely helps.

I appreciate the time you put into explaining how you solved the problem. 

Thank you!. :)
(FPC v3.0.4 and Lazarus 1.8.2) or (FPC v3.2.2 and Lazarus v3.2) on Windows 7 SP1 64bit.

JD

  • Hero Member
  • *****
  • Posts: 1848
Re: SOLVED: Call a PostgreSQL function with variadic parameters from Lazarus
« Reply #7 on: September 25, 2018, 11:11:28 pm »
JD, that definitely helps.

I appreciate the time you put into explaining how you solved the problem. 

Thank you!. :)

You are most welcome.  :D :D

JD
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

 

TinyPortal © 2005-2018