« »

How PostgreSQL triggers works when called with a PostgREST PATCH HTTP request

Wonder what values are set or not inside your new.column_name and old.column_name when you are calling PostgREST with a PATCH request? This article is for you!

Create a private schema for our sample app, we will expose the public schema for our PostgREST API:

create schema private;

A city table:

create table private.city (
    city__id integer not null primary key ,
    name text not null,
    countrycode character(3) not null,
    district text not null,
    population integer not null

with some data:

copy private.city (city__id, name, countrycode, district, population) FROM stdin;
1	Kabul	AFG	Kabol	1780000
2	Qandahar	AFG	Qandahar	237500
3	Herat	AFG	Herat	186800

Now let's expose this city private table through PostgREST as a public API (public schema) so we stay clean regarding the Separation of Concerns principle:

create view public.cities as 
	select city__id as id, name, countrycode, district, population 
    from private.city;

Let's add support for the PATCH HTTP verb on our newly created /cities REST endpoint.

create or replace function private.update_city() returns trigger as
    raise exception 'new.name = %, old.name=%, new.countrycode = %, old.countrycode = %', new.name, old.name, new.countrycode, old.countrycode;

    return new;
$$ security definer language plpgsql;

create trigger city_update
    instead of update
    on public.cities
    for each row
execute procedure private.update_city();

Now our function private.update_city() will be called for each rows submitted through PATCH /cities HTTP request. As you can see from update_city function body we print the before/after values of name and countrycode columns in PATCH requests.

What's the value of new.countrycode if I don't specify countrycode property in PATCH request body?

# PATCH /cities?id=eq.1 '{"name": "new_value"}'
curl -H "content-type: application/json" \
	--request PATCH \
    --data '{"name": "new_value"}' http://localhost:3000/cities?id=eq.1 | jq '.message'
new.name = new_value, old.name=Kabul,
new.countrycode = AFG, old.countrycode = AFG
As you can see, the property (column in fact) countrycode was not specified in the PATCH request body but it still defined with its current value in new.countrycode and old.countrycode just like we expected it to be.

What's the value of new.name if I set name as a null value in PATCH request body?

# PATCH /cities?id=eq.1 '{"name": null}'

curl -H "content-type: application/json" \
	--request PATCH \
	--data '{"name": null}' http://localhost:3000/cities?id=eq.1 | jq '.message'
new.name = <NULL>, old.name=Kabul,
new.countrycode = AFG, old.countrycode = AFG

Perfect! Just like we expected. We set name property to null in our PATCH request body thus new.name is set to NULL in our trigger function.

Clone this github repository to try all of this locally. Wonder what SQL conventions you should use? Check out these SQL conventions.

« »
Made with on a hot august night from an airplane the 19th of March 2017.