toh.pgsql

-- PostgreSQL database creation script for Angular "Tour of Heroes" tutorial
-- May be better to use "int GENERATED BY DEFAULT AS IDENTITY" (or ALWAYS) rather than SERIAL for primary keys, but current PostGraphile v4-alpha is problematic, see #631.

-- Could use citext and LIKE instead of ILIKE:
-- CREATE EXTENSION IF NOT EXISTS citext;

DROP TABLE IF EXISTS hero CASCADE;
CREATE TABLE hero(
id SERIAL PRIMARY KEY,
name text NOT NULL);

CREATE OR REPLACE FUNCTION herowithterm(
  term text)
    RETURNS SETOF hero LANGUAGE 'sql' STABLE
AS $$
    select *
    from hero
    where name ILIKE '%'||term||'%'
$$;

INSERT INTO hero (id, name) OVERRIDING SYSTEM VALUE VALUES
    (11,'Mr. Nice'),
    (12,'Narco'),
    (13,'Bombasto'),
    (14,'Celeritas'),
    (15,'Magneta'),
    (16,'RubberMan'),
    (17,'Dynama'),
    (18,'Dr IQ'),
    (19,'Magma'),
    (20,'Tornado');

ALTER SEQUENCE hero_id_seq RESTART WITH 21;