Briven/packages/studio/pg-meta/test/db/00-init.sql

-- We apply our test seedings to template1 so every new created db will have the same structure
\c template1

-- Tables for testing

CREATE TYPE public.user_status AS ENUM ('ACTIVE', 'INACTIVE');
CREATE TYPE composite_type_with_array_attribute AS (my_text_array text[]);

CREATE TABLE public.users (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text,
  status user_status DEFAULT 'ACTIVE'
);
INSERT INTO 
    public.users (name) 
VALUES 
    ('Joe Bloggs'),
    ('Jane Doe');

CREATE TABLE public.todos (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  details text,
  "user-id" bigint REFERENCES users NOT NULL
);

INSERT INTO 
    public.todos (details, "user-id")
VALUES 
    ('Star the repo', 1),
    ('Watch the releases', 2);


CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

create table public.users_audit (
    id BIGINT generated by DEFAULT as identity,
    created_at timestamptz DEFAULT now(),
    user_id bigint,
    previous_value jsonb
);

create function public.audit_action()
returns trigger as $$
begin
    insert into public.users_audit (user_id, previous_value)
    values (old.id, row_to_json(old));

    return new;
end;
$$ language plpgsql;

CREATE VIEW todos_view AS SELECT * FROM public.todos;
-- For testing typegen on view-to-view relationships
create view users_view as select * from public.users;

create materialized view todos_matview as select * from public.todos;

create function public.blurb(public.todos) returns text as
$$
select substring($1.details, 1, 3);
$$ language sql stable;

create function public.blurb_varchar(public.todos) returns character varying as
$$
select substring($1.details, 1, 3);
$$ language sql stable;

create function public.details_length(public.todos) returns integer as
$$
select length($1.details);
$$ language sql stable;

create function public.details_is_long(public.todos) returns boolean as
$$
select $1.details_length > 20;
$$ language sql stable;

create function public.details_words(public.todos) returns text[] as
$$
select string_to_array($1.details, ' ');
$$ language sql stable;

create extension postgres_fdw;
create server foreign_server foreign data wrapper postgres_fdw options (host 'localhost', port '5432', dbname 'postgres');
create user mapping for postgres server foreign_server options (user 'postgres', password 'postgres');
create foreign table foreign_table (
  id int8 not null,
  name text,
  status user_status
) server foreign_server options (schema_name 'public', table_name 'users');

create or replace function public.function_returning_row()
returns public.users
language sql
stable
as $$
  select * from public.users limit 1;
$$;

create or replace function public.function_returning_set_of_rows()
returns setof public.users
language sql
stable
as $$
  select * from public.users;
$$;

create or replace function public.function_returning_table()
returns table (id int, name text)
language sql
stable
as $$
  select id, name from public.users;
$$;

create or replace function public.polymorphic_function(text) returns void language sql as '';
create or replace function public.polymorphic_function(bool) returns void language sql as '';

create table user_details (
  user_id int8 references users(id) primary key,
  details text
);

create view a_view as select id from users;

create table empty();

create table table_with_other_tables_row_type (
  col1 user_details,
  col2 a_view
);

create table table_with_primary_key_other_than_id (
  other_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text
);