A Postgresql trigger and function to encrypt and decrypt a field

postgresql-encrypt-decrypt.sql

CREATE OR REPLACE FUNCTION encrypt_field()
  RETURNS TRIGGER
  AS $func$
BEGIN
  IF PGP_SYM_ENCRYPT(NEW.encrypted_field, 'the-password-here')::text != OLD.encrypted_field::text THEN
    NEW.encrypted_field := PGP_SYM_ENCRYPT(NEW.encrypted_field, 'the-password-here')::text;
  END IF;
  RETURN NEW;
END
$func$
LANGUAGE plpgsql;

CREATE TRIGGER on_insert_encypt_field
  BEFORE INSERT
  ON some_table
  FOR EACH ROW
  EXECUTE PROCEDURE encrypt_field();

CREATE TRIGGER on_insert_encypt_field
  BEFORE UPDATE
  ON some_table
  FOR EACH ROW
  EXECUTE PROCEDURE encrypt_field();

CREATE OR REPLACE FUNCTION decrypt_field(field text)
  RETURNS text
  AS $$
BEGIN
  RETURN PGP_SYM_DECRYPT(field::bytea, 'the-password-here');
END;
$$
LANGUAGE plpgsql;

Consider Supabase Vault extension instead: https://github.com/supabase/vault