-- sample-postgresql.sql: PostgreSQL-only features, as a psql script.
-- Free sample from 99helpers.com/file-types/sql
--
-- Needs PostgreSQL 13 or later. Run it with psql in a throwaway database:
--   createdb sample
--   psql -d sample -X -f sample-postgresql.sql
-- Lines starting with a backslash are psql commands, not SQL: other tools
-- (pgAdmin, DBeaver, a driver in your code) will reject them.

-- 1. psql meta-commands: stop at the first error instead of carrying on,
--    and print a message.
\set ON_ERROR_STOP on
\echo 'Creating the sample tables...'

DROP TABLE IF EXISTS signups;
DROP FUNCTION IF EXISTS slugify(text);
DROP TYPE IF EXISTS plan;

-- 2. A custom enum type: the column can only hold these values.
CREATE TYPE plan AS ENUM ('free', 'pro', 'team');

-- 3. An identity column numbers rows automatically (the standard SQL
--    way; older scripts use SERIAL). TIMESTAMPTZ stores an exact moment,
--    JSONB stores JSON in a binary form you can query and index.
CREATE TABLE signups (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      TEXT        NOT NULL UNIQUE,
    plan       plan        NOT NULL DEFAULT 'free',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    profile    JSONB       NOT NULL DEFAULT '{}'
);

-- 4. A function written in PL/pgSQL. Dollar quoting ($$ ... $$) wraps the
--    body, so the quotes and semicolons inside it need no escaping.
CREATE FUNCTION slugify(input text) RETURNS text
LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
    -- Lowercase, turn every run of other characters into one hyphen.
    RETURN trim(BOTH '-' FROM regexp_replace(lower(input), '[^a-z0-9]+', '-', 'g'));
END;
$$;

-- 5. INSERT ... RETURNING hands back the generated ids.
INSERT INTO signups (email, plan, created_at, profile) VALUES
    ('ada@example.com',  'pro',  '2026-10-01 09:30:00+02', '{"name": "Ada Example", "tags": ["beta"]}'),
    ('ben@example.org',  'free', '2026-10-02 18:05:00+01', '{"name": "Ben Example"}'),
    ('cleo@example.net', 'team', '2026-10-03 07:45:00Z',   '{"name": "Cleo Example", "seats": 5}')
RETURNING id, email;

-- 6. An upsert: insert, or update the existing row if the email is taken.
INSERT INTO signups (email, plan) VALUES ('ben@example.org', 'pro')
ON CONFLICT (email) DO UPDATE SET plan = EXCLUDED.plan;

-- 7. Querying JSONB: ->> extracts a field as text, ? tests for a key.
--    Timestamps are shown in UTC whatever time zone they were written in.
SELECT id,
       profile ->> 'name'                       AS name,
       slugify(profile ->> 'name')              AS slug,
       plan,
       created_at AT TIME ZONE 'UTC'            AS created_utc,
       profile ? 'tags'                         AS has_tags
FROM signups
ORDER BY id;

-- 8. Transactional DDL: in PostgreSQL even CREATE TABLE can be rolled
--    back. (MySQL commits automatically before most DDL statements.)
BEGIN;
CREATE TABLE scratch (n INTEGER);
INSERT INTO scratch SELECT generate_series(1, 3);
ROLLBACK;
SELECT to_regclass('scratch') IS NULL AS scratch_is_gone;

\echo 'Done.'