The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.
TypeScript aialternativeauthdatabasedenoembeddingsexamplefirebasenextjsoauth2pgvectorpostgispostgrespostgresqlpostgrestrealtimesupabasevectorswebsockets
import { safeSql, type SafeSqlFragment } from '../pg-format'
/**
* Builder for the tables introspection query.
*
* `targetOid`, when provided, is a scalar SQL fragment yielding the single OID
* to restrict the query to -- either a literal (`123`) or an uncorrelated scalar
* subquery (`(select tc.oid from pg_class tc join ... where relname=..)`). It is
* compared with `=` so the planner evaluates it once as an initplan constant and
* drives INDEX scans on the base pg_class scan AND the primary-key /
* relationships subqueries, instead of computing sizes, PKs and FK relationships
* for the ENTIRE catalog. (A multiply-referenced CTE would be materialized and
* act as an optimization barrier, forcing seq scans -- hence a scalar.) The
* relationships filter keeps BOTH directions (conrelid OR confrelid), matching
* the outgoing/incoming FK rows the unscoped query would have matched by name.
*
* Omit `targetOid` for the full-catalog listing used by `tables.list`.
*/
export const getTablesSql = (targetOid?: SafeSqlFragment) => {
const mainScope = targetOid
? safeSql`
AND c.oid = ${targetOid}`
: safeSql``
const pkScope = targetOid
? safeSql`
and c.oid = ${targetOid}`
: safeSql``
const relScope = targetOid
? safeSql`
and (c.conrelid = ${targetOid} or c.confrelid = ${targetOid})`
: safeSql``
// retrieve only: deterministic relationships order (plan-order dependent
// otherwise). A composite FK expands to one entry per ordinal column pair
// sharing constraint_name, so tie-break on the column names.
const relOrder = targetOid
? safeSql` order by relationships.constraint_name, relationships.source_column_name, relationships.target_column_name`
: safeSql``
return /* SQL */ safeSql`
SELECT
c.oid :: int8 AS id,
nc.nspname AS schema,
c.relname AS name,
c.relrowsecurity AS rls_enabled,
c.relforcerowsecurity AS rls_forced,
CASE
WHEN c.relreplident = 'd' THEN 'DEFAULT'
WHEN c.relreplident = 'i' THEN 'INDEX'
WHEN c.relreplident = 'f' THEN 'FULL'
ELSE 'NOTHING'
END AS replica_identity,
pg_total_relation_size(format('%I.%I', nc.nspname, c.relname)) :: int8 AS bytes,
pg_size_pretty(
pg_total_relation_size(format('%I.%I', nc.nspname, c.relname))
) AS size,
pg_stat_get_live_tuples(c.oid) AS live_rows_estimate,
pg_stat_get_dead_tuples(c.oid) AS dead_rows_estimate,
obj_description(c.oid) AS comment,
coalesce(pk.primary_keys, '[]') as primary_keys,
coalesce(
jsonb_agg(relationships${relOrder}) filter (where relationships is not null),
'[]'
) as relationships
FROM
pg_namespace nc
JOIN pg_class c ON nc.oid = c.relnamespace
left join (
select
c.oid::int8 as table_id,
jsonb_agg(
jsonb_build_object(
'table_id', c.oid::int8,
'schema', n.nspname,
'table_name', c.relname,
'name', a.attname
)
order by array_position(i.indkey, a.attnum)
) as primary_keys
from
pg_index i
join pg_class c on i.indrelid = c.oid
join pg_namespace n on c.relnamespace = n.oid
join pg_attribute a on a.attrelid = c.oid and a.attnum = any(i.indkey)
where
i.indisprimary${pkScope}
group by c.oid
) as pk
on pk.table_id = c.oid
left join (
select
c.oid :: int8 as id,
c.conname as constraint_name,
nsa.nspname as source_schema,
csa.relname as source_table_name,
sa.attname as source_column_name,
nta.nspname as target_table_schema,
cta.relname as target_table_name,
ta.attname as target_column_name
from
pg_constraint c
cross join lateral unnest(c.conkey, c.confkey) with ordinality as cols(source_attnum, target_attnum, ord)
join (
pg_attribute sa
join pg_class csa on sa.attrelid = csa.oid
join pg_namespace nsa on csa.relnamespace = nsa.oid
) on sa.attrelid = c.conrelid and sa.attnum = cols.source_attnum
join (
pg_attribute ta
join pg_class cta on ta.attrelid = cta.oid
join pg_namespace nta on cta.relnamespace = nta.oid
) on ta.attrelid = c.confrelid and ta.attnum = cols.target_attnum
where
c.contype = 'f'${relScope}
) as relationships
on (relationships.source_schema = nc.nspname and relationships.source_table_name = c.relname)
or (relationships.target_table_schema = nc.nspname and relationships.target_table_name = c.relname)
WHERE
c.relkind IN ('r', 'p')
AND NOT pg_is_other_temp_schema(nc.oid)
AND (
pg_has_role(c.relowner, 'USAGE')
OR has_table_privilege(
c.oid,
'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'
)
OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES')
)${mainScope}
group by
c.oid,
c.relname,
c.relrowsecurity,
c.relforcerowsecurity,
c.relreplident,
nc.nspname,
pk.primary_keys
`
}
export const TABLES_SQL = getTablesSql()