konnoscolumns.ts116 lines · main
| 1 | import { safeSql } from '../pg-format' |
| 2 | |
| 3 | export const COLUMNS_SQL = /* SQL */ safeSql` |
| 4 | -- Adapted from information_schema.columns |
| 5 | |
| 6 | SELECT |
| 7 | c.oid :: int8 AS table_id, |
| 8 | nc.nspname AS schema, |
| 9 | c.relname AS table, |
| 10 | (c.oid || '.' || a.attnum) AS id, |
| 11 | a.attnum AS ordinal_position, |
| 12 | a.attname AS name, |
| 13 | CASE |
| 14 | WHEN a.atthasdef THEN pg_get_expr(ad.adbin, ad.adrelid) |
| 15 | ELSE NULL |
| 16 | END AS default_value, |
| 17 | CASE |
| 18 | WHEN t.typtype = 'd' THEN CASE |
| 19 | WHEN bt.typelem <> 0 :: oid |
| 20 | AND bt.typlen = -1 THEN 'ARRAY' |
| 21 | WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, NULL) |
| 22 | ELSE 'USER-DEFINED' |
| 23 | END |
| 24 | ELSE CASE |
| 25 | WHEN t.typelem <> 0 :: oid |
| 26 | AND t.typlen = -1 THEN 'ARRAY' |
| 27 | WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, NULL) |
| 28 | ELSE 'USER-DEFINED' |
| 29 | END |
| 30 | END AS data_type, |
| 31 | COALESCE(bt.typname, t.typname) AS format, |
| 32 | COALESCE(nbt.nspname, nt.nspname) AS format_schema, |
| 33 | a.attidentity IN ('a', 'd') AS is_identity, |
| 34 | CASE |
| 35 | a.attidentity |
| 36 | WHEN 'a' THEN 'ALWAYS' |
| 37 | WHEN 'd' THEN 'BY DEFAULT' |
| 38 | ELSE NULL |
| 39 | END AS identity_generation, |
| 40 | a.attgenerated IN ('s') AS is_generated, |
| 41 | NOT ( |
| 42 | a.attnotnull |
| 43 | OR t.typtype = 'd' AND t.typnotnull |
| 44 | ) AS is_nullable, |
| 45 | ( |
| 46 | c.relkind IN ('r', 'p') |
| 47 | OR c.relkind IN ('v', 'f') AND pg_column_is_updatable(c.oid, a.attnum, FALSE) |
| 48 | ) AS is_updatable, |
| 49 | uniques.table_id IS NOT NULL AS is_unique, |
| 50 | check_constraints.definition AS "check", |
| 51 | array_to_json( |
| 52 | array( |
| 53 | SELECT |
| 54 | enumlabel |
| 55 | FROM |
| 56 | pg_catalog.pg_enum enums |
| 57 | WHERE |
| 58 | enums.enumtypid = coalesce(bt.oid, t.oid) |
| 59 | OR enums.enumtypid = coalesce(bt.typelem, t.typelem) |
| 60 | ORDER BY |
| 61 | enums.enumsortorder |
| 62 | ) |
| 63 | ) AS enums, |
| 64 | col_description(c.oid, a.attnum) AS comment |
| 65 | FROM |
| 66 | pg_attribute a |
| 67 | LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid |
| 68 | AND a.attnum = ad.adnum |
| 69 | JOIN ( |
| 70 | pg_class c |
| 71 | JOIN pg_namespace nc ON c.relnamespace = nc.oid |
| 72 | ) ON a.attrelid = c.oid |
| 73 | JOIN ( |
| 74 | pg_type t |
| 75 | JOIN pg_namespace nt ON t.typnamespace = nt.oid |
| 76 | ) ON a.atttypid = t.oid |
| 77 | LEFT JOIN ( |
| 78 | pg_type bt |
| 79 | JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid |
| 80 | ) ON t.typtype = 'd' |
| 81 | AND t.typbasetype = bt.oid |
| 82 | LEFT JOIN ( |
| 83 | SELECT DISTINCT ON (table_id, ordinal_position) |
| 84 | conrelid AS table_id, |
| 85 | conkey[1] AS ordinal_position |
| 86 | FROM pg_catalog.pg_constraint |
| 87 | WHERE contype = 'u' AND cardinality(conkey) = 1 |
| 88 | ) AS uniques ON uniques.table_id = c.oid AND uniques.ordinal_position = a.attnum |
| 89 | LEFT JOIN ( |
| 90 | -- We only select the first column check |
| 91 | SELECT DISTINCT ON (table_id, ordinal_position) |
| 92 | conrelid AS table_id, |
| 93 | conkey[1] AS ordinal_position, |
| 94 | substring( |
| 95 | pg_get_constraintdef(pg_constraint.oid, true), |
| 96 | 8, |
| 97 | length(pg_get_constraintdef(pg_constraint.oid, true)) - 8 |
| 98 | ) AS "definition" |
| 99 | FROM pg_constraint |
| 100 | WHERE contype = 'c' AND cardinality(conkey) = 1 |
| 101 | ORDER BY table_id, ordinal_position, oid asc |
| 102 | ) AS check_constraints ON check_constraints.table_id = c.oid AND check_constraints.ordinal_position = a.attnum |
| 103 | WHERE |
| 104 | NOT pg_is_other_temp_schema(nc.oid) |
| 105 | AND a.attnum > 0 |
| 106 | AND NOT a.attisdropped |
| 107 | AND (c.relkind IN ('r', 'v', 'm', 'f', 'p')) |
| 108 | AND ( |
| 109 | pg_has_role(c.relowner, 'USAGE') |
| 110 | OR has_column_privilege( |
| 111 | c.oid, |
| 112 | a.attnum, |
| 113 | 'SELECT, INSERT, UPDATE, REFERENCES' |
| 114 | ) |
| 115 | ) |
| 116 | ` |