Hackhaton

equi_filters.sql

 WITH joins_only AS (SELECT left_tablename, p1.attname, right_tablename, p2.attname, call_count  FROM public.equi_filters, pg_attribute p1, pg_attribute p2 WHERE right_tablename != 'NULL' AND equi_filters.left_tablename::text::regclass = p1.attrelid AND equi_filters.right_tablename::text::regclass = p2.attrelid AND p1.attnum = left_attrno AND p2.attnum = right_attrname) SELECT * FROM joins_only;



WITH only_filters AS (SELECT left_tablename, attname,call_count FROM pg_attribute, equi_filters WHERE right_tablename = 'NULL' AND  equi_filters.left_tablename::text::regclass = attrelid AND attnum=left_attrno) SELECT * FROM only_filters;

list_foreign_keys.sql

CREATE VIEW foreign_keys AS
WITH filtered_tables AS (
  SELECT 
    schemaname AS schema_name,
    tablename AS table_name
  FROM pg_tables
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (
    SELECT 1
    FROM pg_depend
    WHERE deptype = 'e' AND objid = schemaname::text::regnamespace
  )
),
foreign_keys AS (
  SELECT
    tc.table_schema AS schema_name,
    tc.table_name AS table_name,
    kcu.column_name AS column_name,
    ccu.table_schema AS referenced_schema,
    ccu.table_name AS referenced_table,
    ccu.column_name AS referenced_column,
    rc.update_rule AS update_rule,
    rc.delete_rule AS delete_rule
  FROM information_schema.table_constraints AS tc
  JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
  JOIN information_schema.referential_constraints AS rc ON tc.constraint_name = rc.constraint_name
  JOIN information_schema.constraint_column_usage AS ccu ON rc.unique_constraint_name = ccu.constraint_name
  WHERE tc.constraint_type = 'FOREIGN KEY'
),
filtered_foreign_keys AS (
  SELECT *
  FROM foreign_keys
  WHERE schema_name IN (SELECT schema_name FROM filtered_tables) AND
        table_name IN (SELECT table_name FROM filtered_tables)
)
SELECT 
  json_build_object(
    'schema_name', schema_name,
    'table_name', table_name,
    'column_name', column_name,
    'referenced_schema', referenced_schema,
    'referenced_table', referenced_table,
    'referenced_column', referenced_column,
    'update_rule', update_rule,
    'delete_rule', delete_rule
  ) AS foreign_key_info
FROM filtered_foreign_keys;

list_table_access.sql

CREATE VIEW table_accesses AS 
WITH filtered_tables AS (
  SELECT 
    schemaname AS schema_name,
    tablename AS table_name
  FROM pg_tables
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (
    SELECT 1
    FROM pg_depend
    WHERE deptype = 'e' AND objid = schemaname::text::regnamespace
  )
)
SELECT
  json_build_object(
    'schema_name', ft.schema_name,
    'table_name', ft.table_name,
    'total_reads', COALESCE(st.seq_scan, 0) + COALESCE(st.idx_scan, 0),
    'total_writes', COALESCE(st.n_tup_ins, 0) + COALESCE(st.n_tup_upd, 0) + COALESCE(st.n_tup_del, 0)
  ) AS table_stats
FROM filtered_tables AS ft
JOIN pg_stat_user_tables AS st ON ft.schema_name = st.schemaname AND ft.table_name = st.relname
ORDER BY ft.schema_name, ft.table_name;

list_tables.sql

CREATE VIEW table_list AS 
WITH table_sizes AS (
  SELECT 
    schemaname AS schema_name,
    tablename AS table_name,
    (pg_relation_size(schemaname || '.' || tablename)) AS size
  FROM pg_tables
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'columnar_internal')
),
table_columns AS (
  SELECT 
    table_schema AS schema_name, 
    table_name, 
    json_agg(json_build_object('column_name', column_name, 'data_type', data_type, 'is_nullable', is_nullable)) AS columns
  FROM information_schema.columns
  WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'columnar_internal')
  GROUP BY table_schema, table_name
),
filtered_tables AS (
  SELECT
    table_sizes.schema_name,
    table_sizes.table_name,
    table_sizes.size,
    table_columns.columns
  FROM table_sizes
  JOIN table_columns ON table_sizes.schema_name = table_columns.schema_name AND table_sizes.table_name = table_columns.table_name
  WHERE NOT EXISTS (
    SELECT 1 FROM pg_depend where deptype = 'e' and objid =table_columns.schema_name::text::regnamespace 
  )
)
SELECT 
  json_build_object(
    'schema_name', schema_name,
    'table_name', table_name,
    'size', size,
    'columns', columns
  ) AS table_info
FROM filtered_tables;