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',
  decimal numeric,
  user_uuid uuid DEFAULT gen_random_uuid()
);
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.blurb_varchar(public.todos_view) 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_single_row(todos public.todos)
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.function_returning_table_with_args(user_id int)
returns table (id int, name text)
language sql
stable
as $$
  select id, name from public.users WHERE id = user_id;
$$;


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_by_matview(todos_matview)
RETURNS SETOF todos ROWS 1
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM public.todos LIMIT 1;
$$;

CREATE OR REPLACE FUNCTION public.search_todos_by_details(search_details text)
RETURNS SETOF todos
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM public.todos WHERE details ilike search_details;
$$;

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

-- Valid postgresql function override but that produce an unresolvable postgrest function call
create function postgrest_unresolvable_function() returns void language sql as '';
create function postgrest_unresolvable_function(a text) returns int language sql as 'select 1';
create function postgrest_unresolvable_function(a int) returns text language sql as $$
    SELECT 'toto'
$$;
-- Valid postgresql function override with differents returns types depending of different arguments
create function postgrest_resolvable_with_override_function() returns void language sql as '';
create function postgrest_resolvable_with_override_function(a text) returns int language sql as 'select 1';
create function postgrest_resolvable_with_override_function(b int) returns text language sql as $$
    SELECT 'toto'
$$;
-- Function overrides returning setof tables
create function postgrest_resolvable_with_override_function(user_id bigint) returns setof users language sql stable as $$
    SELECT * FROM users WHERE id = user_id;
$$;
create function postgrest_resolvable_with_override_function(todo_id bigint, completed boolean) returns setof todos language sql stable as $$
    SELECT * FROM todos WHERE id = todo_id AND completed = completed;
$$;
-- Function override taking a table as argument and returning a setof
create function postgrest_resolvable_with_override_function(user_row users) returns setof todos language sql stable as $$
    SELECT * FROM todos WHERE "user-id" = user_row.id;
$$;

create or replace function public.polymorphic_function_with_different_return(bool) returns int language sql as 'SELECT 1';
create or replace function public.polymorphic_function_with_different_return(int) returns int language sql as 'SELECT 2';
create or replace function public.polymorphic_function_with_different_return(text) returns text language sql as $$ SELECT 'foo' $$;

create or replace function public.polymorphic_function_with_no_params_or_unnamed() returns int language sql as 'SELECT 1';
create or replace function public.polymorphic_function_with_no_params_or_unnamed(bool) returns int language sql as 'SELECT 2';
create or replace function public.polymorphic_function_with_no_params_or_unnamed(text) returns text language sql as $$ SELECT 'foo' $$;
-- Function with a single unnamed params that isn't a json/jsonb/text should never appears in the type gen as it won't be in postgrest schema
create or replace function public.polymorphic_function_with_unnamed_integer(int) returns int language sql as 'SELECT 1';
create or replace function public.polymorphic_function_with_unnamed_json(json) returns int language sql as 'SELECT 1';
create or replace function public.polymorphic_function_with_unnamed_jsonb(jsonb) returns int language sql as 'SELECT 1';
create or replace function public.polymorphic_function_with_unnamed_text(text) returns int language sql as 'SELECT 1';

-- Functions with unnamed parameters that have default values
create or replace function public.polymorphic_function_with_unnamed_default() returns int language sql as 'SELECT 1';
create or replace function public.polymorphic_function_with_unnamed_default(int default 42) returns int language sql as 'SELECT 2';
create or replace function public.polymorphic_function_with_unnamed_default(text default 'default') returns text language sql as $$ SELECT 'foo' $$;

-- Functions with unnamed parameters that have default values and multiple overloads
create or replace function public.polymorphic_function_with_unnamed_default_overload() returns int language sql as 'SELECT 1';
create or replace function public.polymorphic_function_with_unnamed_default_overload(int default 42) returns int language sql as 'SELECT 2';
create or replace function public.polymorphic_function_with_unnamed_default_overload(text default 'default') returns text language sql as $$ SELECT 'foo' $$;
create or replace function public.polymorphic_function_with_unnamed_default_overload(bool default true) returns int language sql as 'SELECT 3';

-- Test function with unnamed row parameter returning setof
CREATE OR REPLACE FUNCTION public.test_unnamed_row_setof(todos)
RETURNS SETOF todos
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM public.todos WHERE "user-id" = $1."user-id";
$$;

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


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

-- Test function with unnamed row parameter returning scalar
CREATE OR REPLACE FUNCTION public.test_unnamed_row_scalar(todos)
RETURNS integer
LANGUAGE SQL STABLE
AS $$
  SELECT COUNT(*) FROM public.todos WHERE "user-id" = $1."user-id";
$$;

-- Test function with unnamed view row parameter
CREATE OR REPLACE FUNCTION public.test_unnamed_view_row(todos_view)
RETURNS SETOF todos
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM public.todos WHERE "user-id" = $1."user-id";
$$;

-- Test function with multiple unnamed row parameters
CREATE OR REPLACE FUNCTION public.test_unnamed_multiple_rows(users, todos)
RETURNS SETOF todos
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM public.todos
  WHERE "user-id" = $1.id
  AND id = $2.id;
$$;

-- Test function with unnamed row parameter returning composite
CREATE OR REPLACE FUNCTION public.test_unnamed_row_composite(users)
RETURNS composite_type_with_array_attribute
LANGUAGE SQL STABLE
AS $$
  SELECT ROW(ARRAY[$1.name])::composite_type_with_array_attribute;
$$;

-- Function that returns a single element
CREATE OR REPLACE FUNCTION public.function_using_table_returns(user_row users)
RETURNS todos
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM public.todos WHERE todos."user-id" = user_row.id LIMIT 1;
$$;

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

-- Function that return the created_ago computed field
CREATE OR REPLACE FUNCTION "public"."created_ago" ("public"."users_audit") RETURNS numeric LANGUAGE "sql"
SET
  "search_path" TO '' AS $_$
  SELECT ROUND(EXTRACT(EPOCH FROM (NOW() - $1.created_at)));
$_$;

-- Create a partitioned table for testing computed fields on partitioned tables
CREATE TABLE public.events (
  id bigint generated by default as identity,
  created_at timestamptz default now(),
  event_type text,
  data jsonb,
  primary key (id, created_at)
) partition by range (created_at);

-- Create partitions for the events table
CREATE TABLE public.events_2024 PARTITION OF public.events
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE public.events_2025 PARTITION OF public.events
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Insert some test data
INSERT INTO public.events (created_at, event_type, data)
VALUES 
  ('2024-06-15', 'login', '{"user": "alice"}'),
  ('2025-03-20', 'logout', '{"user": "bob"}');

-- Function that returns computed field for partitioned table
CREATE OR REPLACE FUNCTION "public"."days_since_event" ("public"."events") RETURNS numeric LANGUAGE "sql"
SET
  "search_path" TO '' AS $_$
  SELECT ROUND(EXTRACT(EPOCH FROM (NOW() - $1.created_at)) / 86400);
$_$;

-- Table with interval columns for testing interval type (nullable and not nullable)
CREATE TABLE public.interval_test (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  duration_required interval NOT NULL,
  duration_optional interval
);

-- Insert test data with interval values
INSERT INTO public.interval_test (duration_required, duration_optional)
VALUES 
  ('1 day 2 hours 30 minutes', '3 days 5 hours'),
  ('1 week', NULL),
  ('2 hours 15 minutes', '45 minutes');

-- Function that takes interval parameter and returns interval
CREATE OR REPLACE FUNCTION public.add_interval_to_duration(
  base_duration interval,
  additional_interval interval
)
RETURNS interval
LANGUAGE SQL
STABLE
AS $$
  SELECT base_duration + additional_interval;
$$;

-- Function that takes a table row with interval and returns interval
CREATE OR REPLACE FUNCTION public.double_duration(interval_test_row public.interval_test)
RETURNS interval
LANGUAGE SQL
STABLE
AS $$
  SELECT interval_test_row.duration_required * 2;
$$;