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
$$
begin
raise exception 'new.name = %, old.name=%, new.countrycode = %, old.countrycode = %', new.name, old.name, new.countrycode, old.countrycode;
return new;
end;
$$ 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
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.