How to Visualize Your App’s Google Play Store Metrics in Looker Studio
Android App Development
Aside from a field named “id”, the next two most commonly found columns in a typical database table are usually some form of timestamp column to track the date the row was created at (ie. “DateCreated”,”date_created”,”created_at”,.etc) and the date that the row was last modified (ie. “DateModified”, “updated_at”, “last_modified_date”,etc.).
Knowing when a row was inserted and when it was last updated are important pieces of information that are used across all kinds of apps.
A data synchronization tool might use a modified date to identify rows that have changed since the last sync, while a dating app might use the creation timestamp to identify spam accounts.
While the creation timestamp is usually very straightforward to implement and enforce usage, making sure all developers on a team are always properly updating the date of last modification on every row across every table can become a tediously annoying management task.
No matter the length and breadth of your engineering standards, there is always that one developer who will not properly keep these values up to date and in doing so introduce very serious issues to a code base.
The moment that the values within a modified timestamp can’t be trusted to be accurate, you’ve not only destroyed the utility that timestamp is meant to provide, but also turned a potential asset into a liability as any other code that relies on these timestamps breaks.
Luckily, for those working with the PostgreSQL database there is a very easy to way to overcome this issue by remove the responsibility for setting and maintaining of these timestamps from developers and putting it into the hands of the database itself.
This is done through the PostgreSQL feature known as triggers.
With triggers, it is trivial to automate the management of the creation and last modified date timestamp fields.
A Trigger in PostgreSQL is a special type of user defined function that is automatically invoked by the PostgreSQL database any time a particular set of events occur at the row level (or statement level) within a table.
So in order to automatically populate and maintain creation and modification timestamps, we will be defining a generic function (called a Trigger Function) as the database level that will contain the logic for the updating the timestamps and then binding that trigger function to events within each table through the definition a Trigger at the table level.
Setup Method 1: The Pure SQL Way
For you alpha-dog SQL jocks out there who scoff at the use of any GUI tool, setting up PostgreSQL to automatically maintain the creation and last modified timestamp is as easy as breaking the confidence of an intern developer.
Assume we have a database table named “user”, and columns named “date_modified” and “date_created”, here are the steps to setting up PostgreSQL via SQL commands to automatically populate those columns:
1.) Define the Trigger Function at the database level
The first step is to setup the single Trigger Function that will contain the logic for updating the two timestamp columns.
In the below SQL, we define a generic piece of code which will determine whether or not it is being executed as as result of an update or insert operation.
In the former case, it sets the date_created and date_modified timestamps to the current time. In the latter, it just sets the date_modified timestamp.
In this case, the function is able to dynamically figure out what the operation is and what table it is being executed on by accessing a set special variables exposed by PostgreSQL to trigger functions.
CREATE FUNCTION public.trigger_update_created_and_modified_date()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
IF (TG_OP = ‘INSERT’) THEN
EXECUTE format(E’update "%s" set date_created = \’%s\’, date_modified = \’%s\’
WHERE id = %s’,
TG_TABLE_NAME,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,NEW.id);
ELSEIF (TG_OP = 'UPDATE') THEN
EXECUTE format(E'update "%s" set date_modified = \'%s\'
WHERE id = %s', TG_TABLE_NAME,CURRENT_TIMESTAMP,NEW.id);
END IF;
RETURN NEW;
END
$BODY$;
2.) Create a Trigger on each table
Now for each table in your database, you create a Trigger which defines the event binding between the trigger function defined in 1.) with the events on that table that should execute the aforementioned function.
For this example, we want the trigger function to execute *after* any insert or update operations, but there a myriad of different conditions you can use for setting up a trigger.
Triggers can be defined to execute at the row level, meaning that the trigger function is called for each row that is modified or at the statement level which triggers the function only 1 time no matter how many rows are modified.
For our user case, a row level trigger is what we need. Note that I’ve added a condition checking that the trigger only fire if
[ sql ] pg_trigger_depth()==0 [ / sql ]
It’s important to include this otherwise you will end up in an infinite loop of your trigger code updating fields on your row there by causing the same triggers to fire again, etc.
CREATE TRIGGER user_trigger
AFTER INSERT OR UPDATE
ON public.user
FOR EACH ROW
WHEN (((pg_trigger_depth() = 0) ))
EXECUTE PROCEDURE public.trigger_update_created_and_modified_date();
Method 2: The pgAdmin Approach
I have no shame in admitting I prefer using the pgAdmin web-based management tool when working with PostgreSQL, it just works!
1.) Define the Trigger Function at the database level
- Under the database node, expand Schemas, right click on Trigger Functions and click New.
- Keep the default options the same, specify the name as “trigger_update_created_and_modified_date” and under the Definition tab in the Code text box paste the same SQL as step 1 above.
- Click “Save”.
2.) Create a Trigger on each table
- Find and expand the table you want to create the Trigger on, right click on Triggers and click “New”.
- Give your Trigger a nice name in the General tab.
- Under Definition, select “trigger_update_created_and_modified_date” in the Trigger Function dropdown.
- Under Events:
- Select “AFTER” in the Fires dropbown.
- Enable “INSERT” and “UPDATE” within Events.
- In the WHEN textbox enter the condition:
[ sql ]((pg_trigger_depth() = 0) )[ / sql ]
- Click Save.
Conclusion
Creation and modification timestamps are important, and the integrity and accuracy of each need to be guaranteed in order for those columns to be used within application logic.
Rather than rely on human developers to ensure their code properly updates these timestamps, leverage the trigger functionality within PostgreSQL to automatically manage these fields and guarantee their accuracy.
Bobby Gill
