Skip to content

Instantly share code, notes, and snippets.

@roalcantara
Last active January 30, 2021 08:01
Show Gist options
  • Save roalcantara/9dc3046c2a7a9a3bb000b313c0e4b5ff to your computer and use it in GitHub Desktop.
Save roalcantara/9dc3046c2a7a9a3bb000b313c0e4b5ff to your computer and use it in GitHub Desktop.
Postgres: Create Table and Trigger
DROP TABLE IF EXISTS pokemons CASCADE;
CREATE TABLE pokemons (
id SERIAL PRIMARY KEY,
code integer NOT NULL,
name text NOT NULL,
height integer NOT NULL,
weight integer NOT NULL,
created_at timestamp without time zone NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX pokemons_pkey ON pokemons(id int4_ops);
CREATE OR REPLACE FUNCTION notify_upsert_pokemon()
RETURNS TRIGGER AS $BODY$ BEGIN
PERFORM pg_notify('upsert_pokemon', row_to_json(NEW)::text);
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE TRIGGER on_insert_or_update_pokemon
AFTER INSERT OR UPDATE ON pokemons
FOR EACH ROW EXECUTE PROCEDURE notify_upsert_pokemon();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment