Introduction to Supabase SQL Trigger Functions and Custom SQL Queries

Supabase is an open-source Firebase alternative that provides a realtime API on top of Postgres. One powerful feature of Supabase is the ability to create SQL trigger functions that run custom code in response to database events and create SQL queries.

Triggers and custom SQL queries allow you to perform actions like validating data before insert or update, modifying related data automatically, logging changes, and more. They can help enforce business logic and data integrity in your application.

In this article, we'll walk through examples of creating triggers in Supabase using JavaScript.

Before Insert Triggers

A BEFORE INSERT trigger runs custom code before a new row is inserted into a table. This is useful for data validation and normalization.

Here's an example trigger that uppercases a text column before insert:

CREATE FUNCTION on_insert_to_users() RETURNS trigger AS $$ BEGIN NEW.name := upper(NEW.name); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER on_insert_to_users BEFORE INSERT ON users FOR EACH ROW EXECUTE PROCEDURE on_insert_to_users();

This simply converts the name column to uppercase before allowing the insert.

After Insert Triggers

An AFTER INSERT trigger runs after a new row is inserted. This is useful for cross-table actions like updating related counts.

For example, let's say we have a table of users and teams. We want to increment a team's user_count whenever a new user is added to that team:

CREATE FUNCTION on_user_insert() RETURNS trigger AS $$ BEGIN UPDATE teams SET user_count = user_count + 1 WHERE id = NEW.team_id; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER on_user_insert AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE on_user_insert();

Now whenever a new user is inserted, it will update the related team's counter.

Before Update Triggers

A BEFORE UPDATE trigger runs custom logic before updating a row. You can access the old and new values using OLD and NEW.

For example, you may want to prevent accidentally decreasing a user's score:

CREATE FUNCTION on_update_users() RETURNS trigger AS $$ BEGIN IF NEW.score < OLD.score THEN RAISE EXCEPTION 'Cannot decrease score!'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER on_update_users BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE on_update_users();

This will throw an error if a score decrease is attempted.

After Update Triggers

An AFTER UPDATE trigger runs after a row update. You can use OLD and NEW to compare values before and after the update.

For example, you may want to log all score changes to an audit table:

CREATE FUNCTION audit_score_updates() RETURNS trigger AS $$ BEGIN IF NEW.score <> OLD.score THEN INSERT INTO score_audit (user_id, old_score, new_score) VALUES (NEW.id, OLD.score, NEW.score); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER audit_score_updates AFTER UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE audit_score_updates();

This will add a new audit record anytime a score is changed.

Before Delete Triggers

A BEFORE DELETE trigger runs before deleting a row. This is useful for validation or cleanup logic.

For example, you may want to prevent deleting users who still have active teams:

CREATE FUNCTION on_delete_users() RETURNS trigger AS $$ BEGIN IF EXISTS (SELECT 1 FROM teams WHERE user_id = OLD.id) THEN RAISE EXCEPTION 'Cannot delete user with active teams!'; END IF; RETURN OLD; END; $$ LANGUAGE plpgsql; CREATE TRIGGER on_delete_users BEFORE DELETE ON users FOR EACH ROW EXECUTE PROCEDURE on_delete_users();

This will prevent accidental cascading deletes.

After Delete Triggers

An AFTER DELETE trigger runs custom logic after deleting a row. This is useful for cleaning up related data.

For example, you may want to delete all scores for a user when the user is deleted:

CREATE FUNCTION on_user_delete() RETURNS trigger AS $$ BEGIN DELETE FROM scores WHERE user_id = OLD.id; RETURN OLD; END; $$ LANGUAGE plpgsql; CREATE TRIGGER on_user_delete AFTER DELETE ON users FOR EACH ROW EXECUTE PROCEDURE on_user_delete();

Now scores will be cleaned up after a user is deleted.

Here is a Supabase trigger function that add the 'food_status' column to the 'posts' table with predefined values and CHECK constraint:

ALTER TABLE posts
ADD COLUMN food_status VARCHAR(20) 
DEFAULT 'room for more' 
CHECK (food_status IN ('nearly empty', 'room for more', 'pretty full', 'overflowing'));

This query adds a column called 'food_status' to the 'posts' table in PostgreSQL.

The 'food_status' column has a single select data type with predefined values of 'nearly empty', 'room for more', 'pretty full', and 'overflowing'.

The default value for the 'food_status' column is set to 'room for more'. This query ensures that the 'food_status' column can only contain one of the specified values using a CHECK constraint.

Supabase trigger function that adds a new column with a check constraint

ALTER TABLE posts
ADD COLUMN label text DEFAULT 'Freedge' CHECK (label IN ('Network', 'Freedge', 'Farm Collective'));

This SQL snippet adds a new column to the 'posts' table with the name 'label'.

The column has a data type of 'text' and a default value of 'Freedge'. Additionally, a check constraint is added to ensure that the value of the 'label' column can only be one of the following: 'Network', 'Freedge', or 'Farm Collective'.

How to calculate the distance between two points using the ll_to_earth function

SELECT address(
  ll_to_earth(1, 1),
  ll_to_earth(22, 22)
) AS distance_in_meters
FROM your_table_name;

Calculate the distance in meters between two points using the ll_to_earth function in Postgres SQL.

How to calculate the total size of all databases in Postgres

select
  sum(pg_database_size (pg_database.datname)) / (1024 * 1024) as db_size_mb
from
  pg_database;

This SQL query calculates the total size of all databases in Postgres and returns the result in megabytes.

Check for NULL values in address columns

SELECT profile_id
FROM address
WHERE profile_id IS NULL;

SELECT address_line_1
FROM address
WHERE address_line_1 IS NULL;

SELECT address_line_2
FROM address
WHERE address_line_2 IS NULL;

SELECT address_line_3
FROM address
WHERE address_line_3 IS NULL;

SELECT address_line_3
FROM address
WHERE address_line_3 IS NULL;

SELECT address_line_3
FROM address
WHERE address_line_3 IS NULL;

This SQL snippet checks for NULL values in the address columns of the address table.

Managing Spatial Data in PostgreSQL and PostGIS

This part explains how to work with spatial data in PostgreSQL databases using the PostGIS extension. We'll cover creating a spatial table, indexing, and querying location data.

Introduction

PostGIS adds support for geographic objects in PostgreSQL, allowing storage and analysis of location data. With PostGIS, you can store points, lines, polygons, and more in a database column as geometry or geography data types.

Some key concepts:

Geometry - Planar coordinate system. Used for data where precise distance measurements matter.
Geography - Ellipsoidal coordinate system based on latitude/longitude. Used for global data where spherical distances should be calculated.
Spatial index - Special index for accelerating spatial queries. Created with CREATE INDEX ... USING GIST.

-- Creates a table named 'address' in the 'public' schema with columns 'profile_id' and 'location' of type 'geography(POINT)'

-- Create a table to store address information
create table if not exists public.address (
    profile_id uuid not null primary key DEFAULT uuid_generate_v4 (),

    -- Timestamp column to record when row was created
    created_at timestamp with time zone default timezone('utc'::text, now()) not null,
    
    -- Column for a text description 
    description varchar(320) not null,

    -- Column to store a geographic point representing the address location
    location_test geography(POINT) not null
);

-- Add foreign key constraint 
ALTER TABLE address
ADD COLUMN location geography(POINT) not null;

-- Create a spatial index on the location column
ALTER TABLE address
ADD location geography(POINT) not null
-- ADD CONSTRAINT ABCDE   ----OPTIONAL
DEFAULT ('0101000020E6100000A4DFBE0E9C91614044FAEDEBC0494240')

-- Create a spatial index on the location column
create index address_geo_index
  on public.address
  using GIST (location);

-- Insert some sample data
insert into public.address
(city, country, location)
values
  ('Moscow', '187', st_point(55.5775293427673, 37.4826983793694)),
  ('London', '233', st_point(51.5073219, -0.1276474));

-- Function to find nearby addresses
create or replace function nearby_address(lat float, long float)
returns setof record
language sql
as $$
  select profile_id, city, st_astext(location) as location, st_distance(location, st_point(long, lat)::geography) as dist_meters
  from public.address
  order by location <-> st_point(long, lat)::geography;
$$;

-- Function to query addresses in bounding box 
create or replace function address_in_view(min_lat float, min_long float, max_lat float, max_long float)
returns setof record
language sql
as $$
	select profile_id, city, st_astext(location) as location
	from public.address
	where location && ST_SetSRID(ST_MakeBox2D(ST_Point(min_long, min_lat), ST_Point(max_long, max_lat)),4326)
$$;

-- Function to query addresses in bounding box 
create or replace function test_nearby(lat float, long float)
returns setof address
as $$
    select *
    from address
    order by location_test <-> st_point(long, lat)::geography;
$$
language sql;

ALTER TABLE address 
ADD COLUMN location_test1 geometry(Point, 4326);

-- Update geometry column from lat/lon values
UPDATE address
SET location = point(st_y(test_nearby(location_test, 4326)),
st_x(test_nearby(location_test, 4326)));

ALTER TABLE address
     ADD COLUMN location_test2 geometry(Point,4326);

    UPDATE address
      SET location_test2 = st_Setsrid(st_Point("long_"::double precision, "lat"::double precision),4326);

SELECT ST_GeogFromText('POINT(35.21076593772987 11.22855348629825)');

ALTER TABLE address ADD COLUMN code geometry(Point,4326);
ALTER TABLE address ADD COLUMN geog geometry(Point,4326);

INSERT INTO address (code, geog)
  VALUES ('LAX', 'POINT(-118.4079 33.9434)');

UPDATE address SET location_test1 = 'point(37.7, 122.4)' 
where profile_id = 'b57f61eb-bcde-42a3-a2dd-038caaf24ceb';

select ST_GeomFromText('POINT(-27.75 ,114.75)', 3857)

-- Convert geometry to different projection
ALTER TABLE address
  ALTER COLUMN location_geometry TYPE GEOMETRY(MULTILINESTRING,3857)
    USING ST_SetSRID(location_geometry, 3857)
;

-- Analyze table after making changes
VACUUM ANALYZE address;

-- The prior example is equivalent to this DDL statement:
ALTER TABLE address
  ALTER COLUMN location_geometry TYPE geometry(MULTILINESTRING, 4326)
    USING ST_SetSRID(location_geometry,4326);

-- If you got the projection wrong (or brought it in as unknown) in load and you wanted to transform to web mercator all in one shot you can do this with DDL but there is no equivalent PostGIS management function to do so in one go.
ALTER TABLE address
 ALTER COLUMN location_geometry TYPE geometry(MULTILINESTRING, 3857) 
  USING ST_Transform(ST_SetSRID(location_geometry,4326),3857) ;

ALTER TABLE address ALTER COLUMN location_geometry TYPE geometry(Geometry, 3857);

UPDATE address SET location_geometry = ST_SETSRID(location_geometry, 3857);

This demonstrates the key concepts of managing spatial data in PostgreSQL! With PostGIS, you can build powerful location-based applications.

Create Spatial Index on Address Table

create index address_geo_index
  on public.address
  using GIST (location);

insert into public.address
(profile_id, location)
values
  ('e4e43fcb-9500-437e-a813-c3b227689d78', st_point(-73.946823, 40.807416)),
  ('6478dcae-a4df-4e28-9ab2-406d5e1c5491', st_point(-73.94581, 40.807475)),
  ('2dec64bf-1b1c-48f5-bab8-d59566cc3f72', st_point(-73.945826, 40.80629));

INSERT INTO public.address
(profile_id, address_line_1, location)
VALUES
  ('e4e43fcb-9500-437e-a813-c3b227689d78', 'London', st_point(-73.946823, 40.807416)),
  ('6478dcae-a4df-4e28-9ab2-406d5e1c5491', 'London', st_point(-73.94581, 40.807475)),
  ('2dec64bf-1b1c-48f5-bab8-d59566cc3f72', 'London', st_point(-73.945826, 40.80629));

UPDATE public.address
SET
  address_line_1 = 'New York',
  location = st_point(-74.0060, 40.7128)
WHERE
  profile_id = '2dec64bf-1b1c-48f5-bab8-d59566cc3f72';

create or replace function nearby_address(lat float, long float)
returns setof record
language sql
as $$
  select profile_id, address_line_1, st_astext(location) as location, st_distance(location, st_point(long, lat)::geography) as dist_meters
  from public.address
  order by location <-> st_point(long, lat)::geography;
$$;

create or replace function address_in_view(min_lat float, min_long float, max_lat float, max_long float)
returns setof record
language sql
as $$
	select profile_id, address_line_1, st_astext(location) as location
	from public.address
	where location && ST_SetSRID(ST_MakeBox2D(ST_Point(min_long, min_lat), ST_Point(max_long, max_lat)),4326)
$$;

This SQL snippet creates a spatial index on the location column of the public.address table.

Create Trigger Function for Inserting Data into Users Table

CREATE OR REPLACE FUNCTION auth.
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
begin
  insert into public.Users (id, first_name, photo_url,email,password,second_name,birth_date,user_address)
  values (new.id, new.raw_user_meta_data->>'first_name',
   new.raw_user_meta_data->>'photo_url',
   new.raw_user_meta_data->>'email',
   new.raw_user_meta_data->>'password',
   new.raw_user_meta_data->>'second_name',
   new.raw_user_meta_data->>'birth_date',
   new.raw_user_meta_data->>'user_address' );
  return new;
end;
$function$

This SQL snippet creates a trigger function named 'auth' that is written in PL/pgSQL. The function inserts data into the 'public.Users' table based on the values provided in the 'new' row.

Create Countries Table and Populate with ISO Data

create type public.continents as enum (
    'Africa',
    'Antarctica',
    'Asia',
    'Europe',
    'Oceania',
    'North America',
    'South America'
);
create table public.countries (
  id bigint generated by default as identity primary key,
  name text,
  iso2 text not null,
  iso3 text,
  local_name text,
  continent continents
);
comment on table countries is 'Full list of countries.';
comment on column countries.name is 'Full country name.';
comment on column countries.iso2 is 'ISO 3166-1 alpha-2 code.';
comment on column countries.iso3 is 'ISO 3166-1 alpha-3 code.';
comment on column countries.local_name is 'Local variation of the name.';
insert into public.countries (name,iso2,iso3,local_name,continent) values
  ('Bonaire, Sint Eustatius and Saba','BQ','BES',null,null),
  ('Curaçao','CW','CUW',null,null),
  ('Guernsey','GG','GGY',null,null),
  ('Isle of Man','IM','IMN',null,null),
  ('Jersey','JE','JEY',null,null),
  ('Åland Islands','AX','ALA',null,null),
  ('Montenegro','ME','MNE',null,null),
  ('Saint Barthélemy','BL','BLM',null,null),
  ('Saint Martin (French part)','MF','MAF',null,null),
  ('Serbia','RS','SRB',null,null),
  ('Sint Maarten (Dutch part)','SX','SXM',null,null),
  ('South Sudan','SS','SSD',null,null),
  ('Timor-Leste','TL','TLS',null,null),
  ('American Samoa','as','ASM','Amerika Samoa','Oceania'),
  ('Andorra','AD','AND','Andorra','Europe'),
  ('Angola','AO','AGO','Angola','Africa'),
  ('Anguilla','AI','AIA','Anguilla','North America'),
  ('Antarctica','AQ','ATA','','Antarctica'),
  ('Antigua and Barbuda','AG','ATG','Antigua and Barbuda','North America'),
  ('Argentina','AR','ARG','Argentina','South America'),
  ('Armenia','AM','ARM','Hajastan','Asia'),
  ('Aruba','AW','ABW','Aruba','North America'),
  ('Australia','AU','AUS','Australia','Oceania'),
  ('Austria','AT','AUT','Österreich','Europe'),
  ('Azerbaijan','AZ','AZE','Azerbaijan','Asia'),
  ('Bahamas','BS','BHS','The Bahamas','North America'),
  ('Bahrain','BH','BHR','Al-Bahrayn','Asia'),
  ('Bangladesh','BD','BGD','Bangladesh','Asia'),
  ('Barbados','BB','BRB','Barbados','North America'),
  ('Belarus','BY','BLR','Belarus','Europe'),
  ('Belgium','BE','BEL','Belgium/Belgique','Europe'),
  ('Belize','BZ','BLZ','Belize','North America'),
  ('Benin','BJ','BEN','Benin','Africa'),
  ('Bermuda','BM','BMU','Bermuda','North America'),
  ('Bhutan','BT','BTN','Druk-Yul','Asia'),
  ('Bolivia','BO','BOL','Bolivia','South America'),
  ('Bosnia and Herzegovina','BA','BIH','Bosna i Hercegovina','Europe'),
  ('Botswana','BW','BWA','Botswana','Africa'),
  ('Bouvet Island','BV','BVT','Bouvet Island','Antarctica'),
  ('Brazil','BR','BRA','Brasil','South America'),
  ('British Indian Ocean Territory','IO','IOT','British Indian Ocean Territory','Africa'),
  ('Brunei Darussalam','BN','BRN','Brunei Darussalam','Asia'),
  ('Bulgaria','BG','BGR','Balgarija','Europe'),
  ('Burkina Faso','BF','BFA','Burkina Faso','Africa'),
  ('Burundi','BI','BDI','Burundi/Uburundi','Africa'),
  ('Cambodia','KH','KHM','Cambodia','Asia'),
  ('Cameroon','CM','CMR','Cameroun/Cameroon','Africa'),
  ('Canada','CA','CAN','Canada','North America'),
  ('Cape Verde','CV','CPV','Cabo Verde','Africa'),
  ('Cayman Islands','KY','CYM','Cayman Islands','North America'),
  ('Central African Republic','CF','CAF','Centrafrique','Africa'),
  ('Chad','TD','TCD','Tchad/Tshad','Africa'),
  ('Chile','CL','CHL','Chile','South America'),
  ('China','CN','CHN','Zhongquo','Asia'),
  ('Christmas Island','CX','CXR','Christmas Island','Oceania'),
  ('Cocos (Keeling) Islands','CC','CCK','Cocos (Keeling) Islands','Oceania'),
  ('Colombia','CO','COL','Colombia','South America'),
  ('Comoros','KM','COM','Komori/Comores','Africa'),
  ('Congo','CG','COG','Congo','Africa'),
  ('Congo, the Democratic Republic of the','CD','COD','Republique Democratique du Congo','Africa'),
  ('Cook Islands','CK','COK','The Cook Islands','Oceania'),
  ('Costa Rica','CR','CRI','Costa Rica','North America'),
  ('Cote DIvoire','CI','CIV','Côte dIvoire','Africa'),
  ('Croatia','HR','HRV','Hrvatska','Europe'),
  ('Cuba','CU','CUB','Cuba','North America'),
  ('Cyprus','CY','CYP','Cyprus','Asia'),
  ('Czech Republic','CZ','CZE','Czech','Europe'),
  ('Denmark','DK','DNK','Danmark','Europe'),
  ('Djibouti','DJ','DJI','Djibouti/Jibuti','Africa'),
  ('Dominica','DM','DMA','Dominica','North America'),
  ('Dominican Republic','DO','DOM','Republica Dominicana','North America'),
  ('Ecuador','EC','ECU','Ecuador','South America'),
  ('Egypt','EG','EGY','Misr','Africa'),
  ('El Salvador','SV','SLV','El Salvador','North America'),
  ('Equatorial Guinea','GQ','GNQ','Guinea Ecuatorial','Africa'),
  ('Eritrea','ER','ERI','Ertra','Africa'),
  ('Estonia','EE','EST','Eesti','Europe'),
  ('Ethiopia','ET','ETH','Yeityopiya','Africa'),
  ('Falkland Islands (Malvinas)','FK','FLK','Falkland Islands','South America'),
  ('Faroe Islands','FO','FRO','Faroe Islands','Europe'),
  ('Fiji','FJ','FJI','Fiji Islands','Oceania'),
  ('Finland','FI','FIN','Suomi','Europe'),
  ('France','FR','FRA','France','Europe'),
  ('French Guiana','GF','GUF','Guyane francaise','South America'),
  ('French Polynesia','PF','PYF','Polynésie française','Oceania'),
  ('French Southern Territories','TF','ATF','Terres australes françaises','Antarctica'),
  ('Gabon','GA','GAB','Le Gabon','Africa'),
  ('Gambia','GM','GMB','The Gambia','Africa'),
  ('Georgia','GE','GEO','Sakartvelo','Asia'),
  ('Germany','DE','DEU','Deutschland','Europe'),
  ('Ghana','GH','GHA','Ghana','Africa'),
  ('Gibraltar','GI','GIB','Gibraltar','Europe'),
  ('Greece','GR','GRC','Greece','Europe'),
  ('Greenland','GL','GRL','Kalaallit Nunaat','North America'),
  ('Grenada','GD','GRD','Grenada','North America'),
  ('Guadeloupe','GP','GLP','Guadeloupe','North America'),
  ('Guam','GU','GUM','Guam','Oceania'),
  ('Guatemala','GT','GTM','Guatemala','North America'),
  ('Guinea','GN','GIN','Guinea','Africa'),
  ('Guinea-Bissau','GW','GNB','Guinea-Bissau','Africa'),
  ('Guyana','GY','GUY','Guyana','South America'),
  ('Haiti','HT','HTI','Haiti/Dayti','North America'),
  ('Heard Island and Mcdonald Islands','HM','HMD','Heard and McDonald Islands','Antarctica'),
  ('Holy See (Vatican City State)','VA','VAT','Santa Sede/Città del Vaticano','Europe'),
  ('Honduras','HN','HND','Honduras','North America'),
  ('Hong Kong','HK','HKG','Xianggang/Hong Kong','Asia'),
  ('Hungary','HU','HUN','Hungary','Europe'),
  ('Iceland','IS','ISL','Iceland','Europe'),
  ('India','IN','IND','Bharat/India','Asia'),
  ('Indonesia','ID','IDN','Indonesia','Asia'),
  ('Iran, Islamic Republic of','IR','IRN','Iran','Asia'),
  ('Iraq','IQ','IRQ','Al-Irāq','Asia'),
  ('Ireland','IE','IRL','Ireland','Europe'),
  ('Israel','IL','ISR','Yisrael','Asia'),
  ('Italy','IT','ITA','Italia','Europe'),
  ('Jamaica','JM','JAM','Jamaica','North America'),
  ('Japan','JP','JPN','Nihon/Nippon','Asia'),
  ('Jordan','JO','JOR','Al-Urdunn','Asia'),
  ('Kazakhstan','KZ','KAZ','Qazaqstan','Asia'),
  ('Kenya','KE','KEN','Kenya','Africa'),
  ('Kiribati','KI','KIR','Kiribati','Oceania'),
  ('Korea, Democratic People''s Republic of','KP','PRK','Choson Minjujuui Inmin Konghwaguk (Bukhan)','Asia'),
  ('Korea, Republic of','KR','KOR','Taehan-minguk (Namhan)','Asia'),
  ('Kuwait','KW','KWT','Al-Kuwayt','Asia'),
  ('Kyrgyzstan','KG','KGZ','Kyrgyzstan','Asia'),
  ('Lao People''s Democratic Republic','LA','LAO','Lao','Asia'),
  ('Latvia','LV','LVA','Latvija','Europe'),
  ('Lebanon','LB','LBN','Lubnan','Asia'),
  ('Lesotho','LS','LSO','Lesotho','Africa'),
  ('Liberia','LR','LBR','Liberia','Africa'),
  ('Libya','LY','LBY','Libiya','Africa'),
  ('Liechtenstein','LI','LIE','Liechtenstein','Europe'),
  ('Lithuania','LT','LTU','Lietuva','Europe'),
  ('Luxembourg','LU','LUX','Luxembourg','Europe'),
  ('Macao','MO','MAC','Macau/Aomen','Asia'),
  ('Macedonia, the Former Yugoslav Republic of','MK','MKD','Makedonija','Europe'),
  ('Madagascar','MG','MDG','Madagasikara/Madagascar','Africa'),
  ('Malawi','MW','MWI','Malawi','Africa'),
  ('Malaysia','MY','MYS','Malaysia','Asia'),
  ('Maldives','MV','MDV','Dhivehi Raajje/Maldives','Asia'),
  ('Mali','ML','MLI','Mali','Africa'),
  ('Malta','MT','MLT','Malta','Europe'),
  ('Marshall Islands','MH','MHL','Marshall Islands/Majol','Oceania'),
  ('Martinique','MQ','MTQ','Martinique','North America'),
  ('Mauritania','MR','MRT','Muritaniya/Mauritanie','Africa'),
  ('Mauritius','MU','MUS','Mauritius','Africa'),
  ('Mayotte','YT','MYT','Mayotte','Africa'),
  ('Mexico','MX','MEX','Mexico','North America'),
  ('Micronesia, Federated States of','FM','FSM','Micronesia','Oceania'),
  ('Moldova, Republic of','MD','MDA','Moldova','Europe'),
  ('Monaco','MC','MCO','Monaco','Europe'),
  ('Mongolia','MN','MNG','Mongol Uls','Asia'),
  ('Albania','AL','ALB','Republika e Shqipërisë','Europe'),
  ('Montserrat','MS','MSR','Montserrat','North America'),
  ('Morocco','MA','MAR','Al-Maghrib','Africa'),
  ('Mozambique','MZ','MOZ','Mozambique','Africa'),
  ('Myanmar','MM','MMR','Myanma Pye','Asia'),
  ('Namibia','NA','NAM','Namibia','Africa'),
  ('Nauru','NR','NRU','Naoero/Nauru','Oceania'),
  ('Nepal','NP','NPL','Nepal','Asia'),
  ('Netherlands','NL','NLD','Nederland','Europe'),
  ('New Caledonia','NC','NCL','Nouvelle-Calédonie','Oceania'),
  ('New Zealand','NZ','NZL','New Zealand/Aotearoa','Oceania'),
  ('Nicaragua','NI','NIC','Nicaragua','North America'),
  ('Niger','NE','NER','Niger','Africa'),
  ('Nigeria','NG','NGA','Nigeria','Africa'),
  ('Niue','NU','NIU','Niue','Oceania'),
  ('Norfolk Island','NF','NFK','Norfolk Island','Oceania'),
  ('Northern Mariana Islands','MP','MNP','Northern Mariana Islands','Oceania'),
  ('Norway','NO','NOR','Norge','Europe'),
  ('Oman','OM','OMN','Oman','Asia'),
  ('Pakistan','PK','PAK','Pakistan','Asia'),
  ('Palau','PW','PLW','Belau/Palau','Oceania'),
  ('Palestine, State of','PS','PSE','Filastin','Asia'),
  ('Panama','PA','PAN','República de Panamá','North America'),
  ('Papua New Guinea','PG','PNG','Papua New Guinea/Papua Niugini','Oceania'),
  ('Paraguay','PY','PRY','Paraguay','South America'),
  ('Peru','PE','PER','Perú/Piruw','South America'),
  ('Philippines','PH','PHL','Pilipinas','Asia'),
  ('Pitcairn','PN','PCN','Pitcairn','Oceania'),
  ('Poland','PL','POL','Polska','Europe'),
  ('Portugal','PT','PRT','Portugal','Europe'),
  ('Puerto Rico','PR','PRI','Puerto Rico','North America'),
  ('Qatar','QA','QAT','Qatar','Asia'),
  ('Reunion','RE','REU','Reunion','Africa'),
  ('Romania','RO','ROM','Romania','Europe'),
  ('Russian Federation','RU','RUS','Rossija','Europe'),
  ('Rwanda','RW','RWA','Rwanda/Urwanda','Africa'),
  ('Saint Helena, Ascension and Tristan da Cunha','SH','SHN','Saint Helena','Africa'),
  ('Saint Kitts and Nevis','KN','KNA','Saint Kitts and Nevis','North America'),
  ('Saint Lucia','LC','LCA','Saint Lucia','North America'),
  ('Saint Pierre and Miquelon','PM','SPM','Saint-Pierre-et-Miquelon','North America'),
  ('Saint Vincent and the Grenadines','VC','VCT','Saint Vincent and the Grenadines','North America'),
  ('Samoa','WS','WSM','Samoa','Oceania'),
  ('San Marino','SM','SMR','San Marino','Europe'),
  ('Sao Tome and Principe','ST','STP','São Tomé e Príncipe','Africa'),
  ('Saudi Arabia','SA','SAU','Al-Mamlaka al-Arabiya as-Saudiya','Asia'),
  ('Senegal','SN','SEN','Sénégal/Sounougal','Africa'),
  ('Seychelles','SC','SYC','Sesel/Seychelles','Africa'),
  ('Sierra Leone','SL','SLE','Sierra Leone','Africa'),
  ('Singapore','SG','SGP','Singapore/Singapura/Xinjiapo/Singapur','Asia'),
  ('Slovakia','SK','SVK','Slovensko','Europe'),
  ('Slovenia','SI','SVN','Slovenija','Europe'),
  ('Solomon Islands','SB','SLB','Solomon Islands','Oceania'),
  ('Somalia','SO','SOM','Soomaaliya','Africa'),
  ('South Africa','ZA','ZAF','South Africa','Africa'),
  ('South Georgia and the South Sandwich Islands','GS','SGS','South Georgia and the South Sandwich Islands','Antarctica'),
  ('Spain','ES','ESP','España','Europe'),
  ('Sri Lanka','LK','LKA','Sri Lanka/Ilankai','Asia'),
  ('Sudan','SD','SDN','As-Sudan','Africa'),
  ('Suriname','SR','SUR','Suriname','South America'),
  ('Svalbard and Jan Mayen','SJ','SJM','Svalbard og Jan Mayen','Europe'),
  ('Swaziland','SZ','SWZ','kaNgwane','Africa'),
  ('Sweden','SE','SWE','Sverige','Europe'),
  ('Switzerland','CH','CHE','Schweiz/Suisse/Svizzera/Svizra','Europe'),
  ('Syrian Arab Republic','SY','SYR','Suriya','Asia'),
  ('Taiwan (Province of China)','TW','TWN','Tai-wan','Asia'),
  ('Tajikistan','TJ','TJK','Tajikistan','Asia'),
  ('Tanzania, United Republic of','TZ','TZA','Tanzania','Africa'),
  ('Thailand','TH','THA','Prathet Thai','Asia'),
  ('Togo','TG','TGO','Togo','Africa'),
  ('Tokelau','TK','TKL','Tokelau','Oceania'),
  ('Tonga','TO','TON','Tonga','Oceania'),
  ('Trinidad and Tobago','TT','TTO','Trinidad and Tobago','North America'),
  ('Tunisia','TN','TUN','Tunis/Tunisie','Africa'),
  ('Turkey','TR','TUR','Türkiye','Asia'),
  ('Turkmenistan','TM','TKM','Türkmenistan','Asia'),
  ('Turks and Caicos Islands','TC','TCA','The Turks and Caicos Islands','North America'),
  ('Tuvalu','TV','TUV','Tuvalu','Oceania'),
  ('Uganda','UG','UGA','Uganda','Africa'),
  ('Ukraine','UA','UKR','Ukrajina','Europe'),
  ('United Arab Emirates','AE','ARE','Al-Amirat al-Arabiya al-Muttahida','Asia'),
  ('United Kingdom','GB','GBR','United Kingdom','Europe'),
  ('United States','US','USA','United States','North America'),
  ('United States Minor Outlying Islands','UM','UMI','United States Minor Outlying Islands','Oceania'),
  ('Uruguay','UY','URY','Uruguay','South America'),
  ('Uzbekistan','UZ','UZB','Uzbekiston','Asia'),
  ('Vanuatu','VU','VUT','Vanuatu','Oceania'),
  ('Venezuela','VE','VEN','Venezuela','South America'),
  ('Viet Nam','VN','VNM','Viet Nam','Asia'),
  ('Virgin Islands (British)','VG','VGB','British Virgin Islands','North America'),
  ('Virgin Islands (U.S.)','VI','VIR','Virgin Islands of the United States','North America'),
  ('Wallis and Futuna','WF','WLF','Wallis-et-Futuna','Oceania'),
  ('Western Sahara','EH','ESH','As-Sahrawiya','Africa'),
  ('Yemen','YE','YEM','Al-Yaman','Asia'),
  ('Zambia','ZM','ZMB','Zambia','Africa'),
  ('Zimbabwe','ZW','ZWE','Zimbabwe','Africa'),
  ('Afghanistan','AF','AFG','Afganistan/Afqanestan','Asia'),
  ('Algeria','DZ','DZA','Al-Jazair/Algerie','Africa');

This script creates an enum type called continents with values for each continent. It then creates a countries table with columns for id, name, ISO-2 and ISO-3 codes, local name, and continent. Comments are added for the table and columns. The countries table is populated with rows for 249 countries and territories, including name, ISO codes, local names, and continent. This provides a lookup table for standard country names, codes, and continents.

Create Indexes on Foreign Key Columns in Views Table

-- Create an index on the foreign key columns post_id and profile_id for optimized joins and lookups:
CREATE INDEX ON views (post_id);
CREATE INDEX ON views (profile_id);
CREATE INDEX ON views (forum_id);
CREATE INDEX ON views (challenge_id);

-- Here is SQL to create a post_views table to track post views, with foreign keys referencing the posts and profiles tables:
DROP TABLE IF EXISTS post_views;

-- Here is SQL to create a views table to track post views, with foreign keys referencing the posts and profiles tables:
DROP TABLE IF EXISTS views;

CREATE TABLE views (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL,
  profile_id UUID NOT NULL,
  forum_id BIGINT,
  challenge_id BIGINT,
  viewed_at TIMESTAMPTZ DEFAULT NOW(),
  -- Add foreign key constraints to link the post_id and profile_id columns to their respective tables
  CONSTRAINT fk_views_post_id FOREIGN KEY (post_id) REFERENCES posts (id),
  CONSTRAINT fk_views_profile_id FOREIGN KEY (profile_id) REFERENCES profiles (id)
);

-- Create an index on the foreign key columns for optimized joins and lookups
CREATE INDEX IF NOT EXISTS idx_views_post_id ON views (post_id);
CREATE INDEX IF NOT EXISTS idx_views_profile_id ON views (profile_id);
CREATE INDEX IF NOT EXISTS idx_views_forum_id ON views (forum_id);
CREATE INDEX IF NOT EXISTS idx_views_challenge_id ON views (challenge_id);

This SQL snippet creates an index on the foreign key columns post_id and profile_id in the views table. It also creates indexes on the forum_id and challenge_id columns. The purpose of these indexes is to optimize joins and lookups when querying the views table.

How to create 'posts' table with 'post_address' and 'locations' columns, install PostGIS extension, create trigger function to update 'locations' column, create trigger to automatically update 'locations' column on insert or update of 'post_address', select PostGIS full version

-- install the PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- create the trigger function to update locations
CREATE OR REPLACE FUNCTION update_locations()
RETURNS TRIGGER AS $$
BEGIN
  NEW.locations := ST_SetSRID(geocode(NEW.post_address, 1), 4326);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql
SET search_path = public, postgis_tiger_geocoder;

-- create the trigger to update locations on insert or update of post_address
CREATE TRIGGER update_posts_locations
BEFORE INSERT OR UPDATE OF post_address
ON posts
FOR EACH ROW
EXECUTE FUNCTION update_locations();

SELECT PostGIS_full_version();

tiger.

This SQL snippet creates a table called 'posts' with two columns: 'post_address' and 'locations'. It also installs the PostGIS extension, creates a trigger function to update the 'locations' column based on the 'post_address', and creates a trigger to automatically update the 'locations' column on insert or update of the 'post_address'. Finally, it selects the PostGIS full version.

Create Trigger and Function to Update Post Like Counter

-- Drop existing trigger and function
DROP TRIGGER IF EXISTS on_post_user_liked ON likes;
DROP FUNCTION IF EXISTS update_post_like_counter;

-- Create function that checks for existing duplicates and updates post_like_counter
CREATE OR REPLACE FUNCTION update_post_like_counter() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    -- Check if the like already exists
    IF EXISTS (
        SELECT 1
        FROM likes
        WHERE post_id = NEW.post_id AND profile_id = NEW.profile_id
    ) THEN
        -- Discount the duplicate like by not incrementing the counter
        RETURN NULL;
    ELSE
        -- Increment the post_like_counter for the given post_id
        UPDATE posts
        SET post_like_counter = post_like_counter + 1
        WHERE id = NEW.post_id;

        RETURN NEW;
    END IF;
END;
$$;

-- Trigger the function before each insert on likes table
CREATE TRIGGER on_post_user_liked
BEFORE INSERT ON likes
FOR EACH ROW
EXECUTE FUNCTION update_post_like_counter();

This SQL snippet drops any existing trigger and function, and then creates a new function that checks for duplicate likes and updates the post_like_counter in the posts table. It also creates a trigger that executes the function before each insert on the likes table.

Create Trigger Functions for Address Table

-- Drop existing trigger and function
DROP TRIGGER IF EXISTS update_generated_full_address_trigger ON public.address;
DROP FUNCTION IF EXISTS update_generated_full_address;
DROP FUNCTION IF EXISTS update_lat_long;

-- Create the trigger function for generating "generated_full_address" column
CREATE FUNCTION update_generated_full_address()
RETURNS TRIGGER AS $$
BEGIN
    NEW.generated_full_address := TRIM(
        CONCAT_WS(', ',
            NULLIF(NULLIF(NEW.address_line_1, '-'), ''),
            NULLIF(NULLIF(NEW.address_line_2, '-'), ''),
            NULLIF(NULLIF(NEW.address_line_3, '-'), ''),
            NULLIF(NULLIF(NEW.city, '-'), ''),
            NULLIF(NULLIF(NEW.state_province, '-'), ''),
            NULLIF(NULLIF(NEW.postal_code, '-'), ''),
            NULLIF(NULLIF(NEW.county, '-'), ''),
            countries.name
        )
    )
    FROM countries
    WHERE countries.id = NEW.country;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger for generating "generated_full_address" column
CREATE TRIGGER update_generated_full_address_trigger
BEFORE INSERT OR UPDATE ON address
FOR EACH ROW
EXECUTE FUNCTION update_generated_full_address();

-- Create the trigger function for populating "lat" and "long" columns
CREATE FUNCTION update_lat_long()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.generated_full_address IS NOT NULL THEN
        NEW.lat := ST_Y(ST_GeomFromText('POINT(' || NEW.long || ' ' || NEW.lat || ')'));
        NEW.long := ST_X(ST_GeomFromText('POINT(' || NEW.long || ' ' || NEW.lat || ')'));
    ELSE
        NEW.lat := NULL;
        NEW.long := NULL;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger for populating "lat" and "long" columns
CREATE TRIGGER update_lat_long_trigger
BEFORE INSERT OR UPDATE ON address
FOR EACH ROW
EXECUTE FUNCTION update_lat_long();

-- Update existing rows to generate the "generated_full_address" column
UPDATE address
SET generated_full_address = TRIM(
        CONCAT_WS(', ',
            NULLIF(NULLIF(address_line_1, '-'), ''),
            NULLIF(NULLIF(address_line_2, '-'), ''),
            NULLIF(NULLIF(address_line_3, '-'), ''),
            NULLIF(NULLIF(city, '-'), ''),
            NULLIF(NULLIF(state_province, '-'), ''),
            NULLIF(NULLIF(postal_code, '-'), ''),
            NULLIF(NULLIF(county, '-'), ''),
            countries.name
        )
    )
FROM countries
WHERE countries.id = address.country;

-- Update existing rows to populate "lat" and "long" columns
UPDATE address
SET lat = CASE WHEN generated_full_address IS NOT NULL THEN ST_Y(ST_GeomFromText('POINT(' || long || ' ' || lat || ')')) ELSE NULL END,
    long = CASE WHEN generated_full_address IS NOT NULL THEN ST_X(ST_GeomFromText('POINT(' || long || ' ' || lat || ')')) ELSE NULL END;

This SQL script creates trigger functions for the address table in a PostgreSQL database. The first trigger function is used to generate the 'generated_full_address' column by concatenating various address fields. The second trigger function is used to populate the 'lat' and 'long' columns based on the generated address. The script also includes the creation of triggers for both functions and updates existing rows to generate the 'generated_full_address' column and populate the 'lat' and 'long' columns.

Create a trigger to update the 'updated_at' column on the 'todos' table

create extension if not exists moddatetime schema extensions;

-- assuming the table name is "todos", and a timestamp column "updated_at"
-- this trigger will set the "updated_at" column to the current timestamp for every update
create trigger
  handle_updated_at before update
on todos
for each row execute
  procedure moddatetime(updated_at);

Create a trigger in Postgres to update the 'updated_at' column with the current timestamp for every update on the 'todos' table.

Create Trigger and Function to Decrement Post Like Counter

-- Drop existing trigger and function
DROP TRIGGER IF EXISTS on_post_user_unliked;
DROP FUNCTION IF EXISTS decrement_count;

-- Create function counter that decrements the value in the 'post_like_counter' column in the 'posts' table 
CREATE OR REPLACE FUNCTION decrement_count()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE posts 
  SET post_like_counter = post_like_counter - 1
  WHERE id = OLD.post_id;

  RETURN OLD;
END;
$$;

-- Trigger the function every time a user unliked/ delited post in 'likes' table
CREATE TRIGGER on_post_user_unliked
  AFTER DELETE ON likes
  FOR EACH ROW EXECUTE PROCEDURE public.decrement_count();

This SQL snippet drops an existing trigger and function, and then creates a new function and trigger. The function is called 'decrement_count' and it decrements the value in the 'post_like_counter' column in the 'posts' table. The trigger, 'on_post_user_unliked', is triggered every time a user unlikes or deletes a post in the 'likes' table.

Conclusion

Triggers and custom SQL queries are powerful for enforcing business logic and maintaining data integrity in Supabase. They can help normalize data on input, propagate changes across tables, prevent accidental data loss and much more.

Some key points:

Use BEFORE triggers for validation and normalization
Use AFTER triggers for cross-table actions and cleanup
Access NEW and OLD to compare values
Handle exceptions to provide user feedback

By mastering Supabase SQL triggers and custom SQL queries, you can create a robust data backend for your application!

Introduction to Supabase SQL Trigger Functions and Custom SQL Queries | Part 1