00-init.sql

-- 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 a more complex view for testing
CREATE VIEW user_todos_summary_view AS
SELECT 
    u.id as user_id,
    u.name as user_name,
    u.status as user_status,
    COUNT(t.id) as todo_count,
    array_agg(t.details) FILTER (WHERE t.details IS NOT NULL) as todo_details
FROM public.users u
LEFT JOIN public.todos t ON t."user-id" = u.id
GROUP BY u.id, u.name, u.status;

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
);

create type composite_type_with_record_attribute as (
  todo todos
);

create view users_view_with_multiple_refs_to_users as
WITH initial_user AS (
  SELECT 
    u.id as initial_id,
    u.name as initial_name
  FROM users u
  where u.id = 1
),
second_user AS (
  SELECT 
    u.id as second_id,
    u.name as second_name
  FROM users u
  where u.id = 2
)
SELECT * from initial_user iu 
cross join second_user su;

CREATE OR REPLACE FUNCTION public.get_user_audit_setof_single_row(user_row users)
RETURNS SETOF users_audit
LANGUAGE SQL STABLE
ROWS 1
AS $$
  SELECT * FROM public.users_audit WHERE user_id = user_row.id;
$$;

CREATE OR REPLACE FUNCTION public.get_todos_setof_rows(user_row users)
RETURNS SETOF todos
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM public.todos WHERE "user-id" = user_row.id;
$$;

CREATE OR REPLACE FUNCTION public.get_todos_setof_rows(todo_row todos)
RETURNS SETOF todos
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM public.todos WHERE "user-id" = todo_row."user-id";
$$;

-- SETOF composite_type - Returns multiple rows of a custom composite type
CREATE OR REPLACE FUNCTION public.get_composite_type_data()
RETURNS SETOF composite_type_with_array_attribute
LANGUAGE SQL STABLE
AS $$
  SELECT ROW(ARRAY['hello', 'world']::text[])::composite_type_with_array_attribute
  UNION ALL
  SELECT ROW(ARRAY['foo', 'bar']::text[])::composite_type_with_array_attribute;
$$;

-- SETOF record - Returns multiple rows with structure defined in the function
CREATE OR REPLACE FUNCTION public.get_user_summary()
RETURNS SETOF record
LANGUAGE SQL STABLE
AS $$
  SELECT u.id, name, count(t.id) as todo_count
  FROM public.users u
  LEFT JOIN public.todos t ON t."user-id" = u.id
  GROUP BY u.id, u.name;
$$;

-- SETOF scalar_type - Returns multiple values of a basic type
CREATE OR REPLACE FUNCTION public.get_user_ids()
RETURNS SETOF bigint
LANGUAGE SQL STABLE
AS $$
  SELECT id FROM public.users;
$$;


-- Function returning view using scalar as input
CREATE OR REPLACE FUNCTION public.get_single_user_summary_from_view(search_user_id bigint)
RETURNS SETOF user_todos_summary_view
LANGUAGE SQL STABLE
ROWS 1
AS $$
  SELECT * FROM user_todos_summary_view WHERE user_id = search_user_id;
$$;
-- Function returning view using table row as input
CREATE OR REPLACE FUNCTION public.get_single_user_summary_from_view(user_row users)
RETURNS SETOF user_todos_summary_view
LANGUAGE SQL STABLE
ROWS 1 
AS $$
  SELECT * FROM user_todos_summary_view WHERE user_id = user_row.id;
$$;
-- Function returning view using another view row as input
CREATE OR REPLACE FUNCTION public.get_single_user_summary_from_view(userview_row users_view)
RETURNS SETOF user_todos_summary_view
LANGUAGE SQL STABLE
ROWS 1 
AS $$
  SELECT * FROM user_todos_summary_view WHERE user_id = userview_row.id;
$$;


-- Function returning view using scalar as input
CREATE OR REPLACE FUNCTION public.get_todos_from_user(search_user_id bigint)
RETURNS SETOF todos
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM todos WHERE "user-id" = search_user_id;
$$;
-- Function returning view using table row as input
CREATE OR REPLACE FUNCTION public.get_todos_from_user(user_row users)
RETURNS SETOF todos
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM todos WHERE "user-id" = user_row.id;
$$;
-- Function returning view using another view row as input
CREATE OR REPLACE FUNCTION public.get_todos_from_user(userview_row users_view)
RETURNS SETOF todos
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM todos WHERE "user-id" = userview_row.id;
$$;