--------------------------------------------------------------------------------------------------------------

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pg_readme_test_extension" to load this file. \quit

--------------------------------------------------------------------------------------------------------------

comment
    on extension pg_readme_test_extension
    is $markdown$
# `pg_readme_test_extension`

The `pg_readme_test_extension` PostgreSQL extension is sort of a sub-extension
of `pg_readme`, in the sense that the former's purpose is to test the latter's
capability to generate a comprehensive `README.md` for an extension.

The reason that this extension exists as a separate set of `.control` and `sql`
files is because we need an extension to fully test `pg_readme` its
`pg_extension_readme()` function.

## Reference

<?pg-readme-reference context-division-depth="2" context-division-is-self="true"?>

## Appendices

<?pg-readme-colophon context-division-depth="2" division-title="Appendix A. Colophon"?>

$markdown$;

--------------------------------------------------------------------------------------------------------------

create domain my_upper as text
    check (value = upper(value));

comment on domain my_upper is
$markdown$Must be uppercase text.
$markdown$;

--------------------------------------------------------------------------------------------------------------

create type my_composite_type as (
    "name with spaces" text[]
    ,created_at timestamptz
);

comment on type my_composite_type is
$markdown$`my_composite_type` doesn't do much.
$markdown$;

--------------------------------------------------------------------------------------------------------------

create type my_enum_type as enum (
    'FIRSTBLA'
    ,'2NDBLA'
);

--------------------------------------------------------------------------------------------------------------

create table my_table (
    a bigint
        primary key
        generated always as identity
    ,b bigint
        not null
        unique
        generated always as (a + 2) stored
    ,z text
    ,check (a <> b)
);

comment
    on table my_table
    is $markdown$
A table with a comment.
$markdown$;

--------------------------------------------------------------------------------------------------------------

create table my_2nd_table (
    a bigint
        primary key
        references my_table(a)
            on delete cascade
    ,c bigint
        generated by default as identity
        not null
        unique
    ,d timestamp
        null
        default now()
);

comment
    on column my_2nd_table.d
    is $markdown$
A column with a comment.

Second paragraph of column comment.
$markdown$;

--------------------------------------------------------------------------------------------------------------

create view my_view as
    select
        my_table.a
        ,my_table.b
        ,my_2nd_table,c
        ,my_table.z
    from
        my_table
    left outer join
        my_2nd_table
        on my_2nd_table.a = my_table.a;

comment
    on view my_view
    is $markdown$
This is a view _with_ a `COMMENT`.
$markdown$;

--------------------------------------------------------------------------------------------------------------

create view view_without_comment as
    select
        my_table.a
        ,my_table.b
        ,my_2nd_table,c
        ,my_table.z
    from
        my_table
    inner join
        my_2nd_table
        on my_2nd_table.a = my_table.a;

--------------------------------------------------------------------------------------------------------------

create function my_view__upsert()
    returns trigger
    language plpgsql
    as $$
begin
    assert tg_when = 'INSTEAD OF';
    assert tg_level = 'ROW';
    assert tg_op IN ('INSERT', 'UPDATE', 'DELETE');
    assert tg_table_name = 'my_view';

    if tg_op = 'INSERT' then
        with parent_row as (
            insert into my_table (z) values (NEW.z)
            returning *
        )
        insert into my_2nd_table (a, c) values (parent_row.a, NEW.c);
    elsif tg_op = 'UPDATE' then
        update my_table
            set z = NEW.z
            where
                my_table.a = OLD.a;
        update my_2nd_table
            set c = NEW.c
            where
                my_2nd_table.a = OLD.a;
    elsif tg_op = 'DELETE' then
        delete from my_table
            when my_table.a = OLD.a;
    end if;
end;
$$;

--------------------------------------------------------------------------------------------------------------

create trigger upsert
    instead of insert or update or delete
    on my_view
    for each row
    execute function my_view__upsert();

--------------------------------------------------------------------------------------------------------------

create procedure test__my_view()
    set search_path from current
    language plpgsql
    as $$
declare
    _my_view my_view;
begin
    insert into my_view (z) values ('blah')
        returning *
        into _my_view;
    update my_view
        set z = 'bleh'  -- “bleh” is obviously better than “blah”
        where a = _my_view.a;
    delete from my_view
        where a = _my_view.a;

    raise transaction_rollback;  -- I could have use any error code, but this one seemed to fit best.
exception
    when transaction_rollback then
end;
$$;

--------------------------------------------------------------------------------------------------------------

create function my_variadic_func(boe$ int, bla$ variadic int[])
    returns void
    language sql
    return null;

--------------------------------------------------------------------------------------------------------------

create procedure test__something_very_verbose()
    set search_path from current
    set pg_readme.include_this_routine_definition = false
    language plpgsql
    as $$
begin
    -- Imagine that this is actually very verbose—_waaay_ to verbose to ever
    -- include in the README.
end;
$$;

--------------------------------------------------------------------------------------------------------------