The app we’ve all built before

So let’s say you want to create a vanilla app for a bicycle rental store. For the sake of simplicity let’s assume there are only customers and bikes. So you set up a PostgreSQL instance with three tables: bike, customer and rental.
The rental table holds a foreign key to both the customer table and the bike table and some data specific to the concrete rental (startdate, enddate, returned_in_time etc.). So far so good.

The problem

Now in your Xojo app (btw if your using another framework most points given here still apply) you want to have a customer detail form that along with all the customer detail data (name, phone, email etc.) displays a list of all rentals the customer ever had. So traditionally you’d have 2 queries to the database in your form:

1. Select * from customer where id=1;// proceed to use that data to fill your form
2. Select * from rental where customer_id=1; //now load that data into a listbox

Now while there is nothing wrong with this approach doing two calls to the database is a performance issue.

How big you ask?

Fair question. Well it depends on a number of things especially on wether you reuse the database connection, if your PostgreSQL instance is running on a remote server, internet speed, wether or not you were smart enough to set an index on your foreign keys in the rental table, table sizes and so on, but each call is easily a 100 milliseconds (in my web app at least). Maybe that is acceptable for the given example but as soon as you want to display more related data you start to feel it getting sluggish. Is there a way to get the data with one call only? Aren’t we tied to Xojo’s rowset paradigm that disallows for non uniform data? Well, there is a way.

the solution

We can utilise Postgres’ JSON support (btw, both MySQL and SQLite also support JSON, but the syntax is slightly different so as not to confuse you let’s stick with PostgreSQL for now) to achieve that.
I realize chances are you’ve never created a PLPGSQL function in your life and the prospect of diving into the syntax is daunting, but actually it is quite straightforward. Don’t be afraid to experiment with PLPGSQL functions. They’re powerful tools that can significantly optimize your database interactions. Start with simple functions and gradually build up to more complex ones as you become comfortable with the syntax. All the common AI’s know their way around it very well so if you ever get stuck you know who to ask. Frankly, the fact PLPGSQL exists is the reason I keep using PostgreSQL.

So here we go:

CREATE OR REPLACE FUNCTION f_customer_get_detaildata(p_customer_id integer, OUT firstname text, OUT lastname text, OUT street text, OUT zip text, OUT city text, OUT country text, OUT title text, OUT salutation text, OUT email text, OUT phone text, OUT notes text, OUT rental_list jsonb)
RETURNS record
LANGUAGE plpgsql
AS $function$

-- This function retrieves customer details and aggregates all related rentals into a JSON array. The jsonb_agg function is used to collect all rental records for the customer into a single JSON structure.

SELECT id, firstname, lastname, street, zip, city, country, title, salutation, email, phone, notes from customer where id=p_customer_id into id, firstname, lastname, street, zip, city, country, title, salutation, email, phone, notes; -- we must match the names given in the parameter list

SELECT jsonb_agg(t_rental) FROM (
Select * from rental where customer_id=p_customer_id order by id)
AS t_rental into rental_list;

--here more data could be returned

end;
$function$

The “OUT” parameters will be the column names in the rowset. If no “OUT” is given (which is the case for the p_customer_id parameter, I like to prefix parameters with “p_”) then that means the function expects you to hand that value in.
Test it, maybe via psql or your favourite database administration tool. Okay, but how can we read the data back in our Xojo app? The DatabaseColumn class does not provide a .jsonItem method, so let’s make one (maybe in a module called databaseColumn_extensions):

Public Function jsonValue(extends dbf as DatabaseColumn) As JSONItem
Var valueAsString As String=dbf.stringValue //just for debugging purposes..
return new JSONItem(valueAsString)
End Function

Please note that the onus of dealing with exceptions is on the caller.

Okay, now in the open event of our xojo form (window or container or whatever device you use to display the data), assuming we have an integer variable called ID and a connection we just call:

var rs as rowset=myConnection.SQLSelect("Select * from f_customer_get_detaildata($1)",ID)

and get all the data we want back.
To get the rentals we just say:

var thisCustomersRentals as JsonItem=rs.column("rental_list").jsonitem

Now you can just traverse through the JsonItem to load and display the rentals.

For ct As Integer=0 To thisCustomersRentals.count-1
curChild=JSON.childat(ct)
MyListbox.AddRow(curChild.Value(“startdate”).StringValue)
….
Next

Cool, that’s it! Now we have a method that can return any data the caller might want in a single call. PostgreSQL is really fast at pushing that data out so instead of maybe waiting 100 ms twice you now wait 105 ms once!
And not only does this method reduce database calls, it also allows for more flexible data structures. You can easily add or remove fields from your JSON output without changing your Xojo code, making your application more adaptable to changing requirements.

And now you know exactly what to do if the rental shop owner would like to see all customers’ rentals in the bike detail form.

Happy coding!

Maximilian Tyrtania