columns.ts81 lines · main

1	import { joinSqlFragments, literal, safeSql, type SafeSqlFragment } from '../../../pg-format'
2
3	export const getTableColumnsSql = ({
4	table,
5	schema,
6	}: {
7	table?: string
8	schema?: string
9	}): SafeSqlFragment => {
10	const conditions: Array<SafeSqlFragment> = []
11	if (table) {
12	conditions.push(safeSql`tablename = ${literal(table)}`)
13	}
14	if (schema) {
15	conditions.push(safeSql`schemaname = ${literal(schema)}`)
16	}
17
18	const whereClause =
19	conditions.length > 0 ? safeSql`WHERE ${joinSqlFragments(conditions, ' AND ')}` : safeSql``
20
21	return safeSql`
22
23	SELECT
24	tbl.schemaname,
25	tbl.tablename,
26	tbl.quoted_name,
27	tbl.is_table,
28	json_agg(a) as columns
29	FROM
30	(
31	SELECT
32	n.nspname as schemaname,
33	c.relname as tablename,
34	(quote_ident(n.nspname) || '.' || quote_ident(c.relname)) as quoted_name,
35	true as is_table
36	FROM
37	pg_catalog.pg_class c
38	JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
39	WHERE
40	c.relkind = 'r'
41	AND n.nspname not in ('information_schema', 'pg_catalog', 'pg_toast')
42	AND n.nspname not like 'pg_temp_%'
43	AND n.nspname not like 'pg_toast_temp_%'
44	AND has_schema_privilege(n.oid, 'USAGE') = true
45	AND has_table_privilege(quote_ident(n.nspname) || '.' || quote_ident(c.relname), 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') = true
46	union all
47	SELECT
48	n.nspname as schemaname,
49	c.relname as tablename,
50	(quote_ident(n.nspname) || '.' || quote_ident(c.relname)) as quoted_name,
51	false as is_table
52	FROM
53	pg_catalog.pg_class c
54	JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
55	WHERE
56	c.relkind in ('v', 'm')
57	AND n.nspname not in ('information_schema', 'pg_catalog', 'pg_toast')
58	AND n.nspname not like 'pg_temp_%'
59	AND n.nspname not like 'pg_toast_temp_%'
60	AND has_schema_privilege(n.oid, 'USAGE') = true
61	AND has_table_privilege(quote_ident(n.nspname) || '.' || quote_ident(c.relname), 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') = true
62	) as tbl
63	LEFT JOIN (
64	SELECT
65	attrelid,
66	attname,
67	format_type(atttypid, atttypmod) as data_type,
68	attnum,
69	attisdropped
70	FROM
71	pg_attribute
72	) as a ON (
73	a.attrelid = tbl.quoted_name::regclass
74	AND a.attnum > 0
75	AND NOT a.attisdropped
76	AND has_column_privilege(tbl.quoted_name, a.attname, 'SELECT, INSERT, UPDATE, REFERENCES')
77	)
78	${whereClause}
79	GROUP BY schemaname, tablename, quoted_name, is_table;
80	`
81	}