Create a select trigger in PostgreSQL

2nd February 2023
Jon Meyers profile pic
Jon Meyers @jonmeyers_io

This tweet from @rchrdnsh got me thinking, I wonder if there is a way to set up a trigger on select?

Turns out, No!

“The event can be INSERT , DELETE , UPDATE or TRUNCATE ” - PostgreSQL Tutorial

And any time I hit a point where I think I can ‘t do something in Postgres, I start playing with PostgreSQL Functions.

So let ‘s say we have a simple posts table:

create table if not exists posts (
  id bigint generated by default as identity primary key,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  title text not null,
  views int default 0 not null
);

And let ‘s insert a test post:

insert into posts(title)
values('First post');

Now we want a function to increment the views column every time we select a particular post:

create or replace function get_post(id int) returns posts as
$$
  -- increment views
  update posts p
  set views = views + 1
  where p.id = get_post.id;

  -- return posts
  select *
  from posts p
  where p.id = get_post.id;
$$ language sql security definer;

We can call this function with a select statement in SQL:

select * from get_post(1);

If you ‘re using Supabase, you can trigger this function with RPC in supabase-js 🚀

const { data } = await supabase.rpc("get_post", {
  id: 1,
});

To be able to look at views across time — last 7 days etc — we would need another table — we can call this post_views — which tracks the number of views for each post. We can insert a new row into this table every time a post is viewed.

Okay, let ‘s start over.

drop function get_post;
drop table posts;

And now, let ‘s create the two related tables:

create table if not exists posts (
  id bigint generated by default as identity primary key,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  title text not null
);

create table if not exists post_views (
  id bigint generated by default as identity primary key,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
	post_id bigint references posts not null
);

With an example post:

insert into posts(title)
values('First post');

Our function can now insert a new row into the post_views table, and then return our posts:

create or replace function get_post(id int) returns posts as
$$
  -- increment views
	insert into post_views(post_id)
	values(get_post.id);

  -- return posts
  select *
  from posts p
  where p.id = get_post.id;
$$ language sql security definer;

We can call this function the exact same way, so in a select statement in SQL:

select * from get_post(1);

And with RPC and supabase-js :

const { data } = await supabase.rpc("get_post", {
  id: 1,
});

Now we can query how many times each post was been viewed:

select posts.*, count(post_views.post_id) as views
from posts
left join post_views
on posts.id = post_views.post_id
group by posts.id;

And to filter down to just the posts that had views from the last 7 days, we can add a where clause that uses an interval :

select posts.*, count(post_views.post_id) as views
from posts
left join post_views
on posts.id = post_views.post_id
where post_views.created_at > now() - interval '7 days'
group by posts.id;

Now, if we are going to use this complex query often, or want to query it from supabase-js , we can wrap it in a view.

create or replace view post_views_in_last_week as
	select posts.*, count(post_views.post_id) as views
	from posts
	left join post_views
	on posts.id = post_views.post_id
	where post_views.created_at > now() - interval '7 days'
	group by posts.id;

We can now treat this view like any table in our db. We can write a select statement with SQL:

select * from post_views_in_last_week;

And even tack on an additional where clause to filter this down further:

select *
from post_views_in_last_week
where title = 'First post';

And since this view is just like any other other table, we can query it very easily with supabase-js :

const { data } = await supabase.from("post_views_in_last_week").select();

Found this article useful? Let me know on Twitter! 🐦