- DATABASE_SQL =
'/* rdbr: database */ SELECT current_database() AS name'.freeze
- NAMESPACES_SQL =
<<~SQL.freeze
/* rdbr: namespaces */
SELECT namespace.nspname AS name
FROM pg_namespace namespace
WHERE true
%<system_filter>s
ORDER BY namespace.nspname
SQL- RELATIONS_SQL =
<<~SQL.freeze
/* rdbr: relations */
SELECT
relation.oid::bigint AS oid,
namespace.nspname AS namespace_name,
relation.relname AS relation_name,
relation.relkind AS relation_kind,
obj_description(relation.oid, 'pg_class') AS comment
FROM pg_class relation
INNER JOIN pg_namespace namespace ON namespace.oid = relation.relnamespace
WHERE relation.relkind IN ('r', 'p', 'v', 'm', 'f')
%<system_filter>s
ORDER BY namespace.nspname, relation.relname
SQL- COLUMNS_SQL =
<<~SQL.freeze
/* rdbr: columns */
SELECT
attribute.attname AS name,
type.typname AS logical_type,
format_type(attribute.atttypid, attribute.atttypmod) AS sql_type,
NOT attribute.attnotnull AS nullable,
pg_get_expr(default_value.adbin, default_value.adrelid, true) AS default_expression,
%<generated_kind>s AS generated_kind,
attribute.attidentity AS identity_kind,
attribute.attnum AS ordinal
FROM pg_attribute attribute
INNER JOIN pg_type type ON type.oid = attribute.atttypid
LEFT JOIN pg_attrdef default_value
ON default_value.adrelid = attribute.attrelid
AND default_value.adnum = attribute.attnum
WHERE attribute.attrelid = %<relation_oid>d
AND attribute.attnum > 0
AND NOT attribute.attisdropped
ORDER BY attribute.attnum
SQL- CONSTRAINTS_SQL =
<<~SQL.freeze
/* rdbr: constraints */
SELECT
constraint_record.conname AS name,
constraint_record.contype AS constraint_type,
to_json(ARRAY(
SELECT attribute.attname
FROM unnest(constraint_record.conkey) WITH ORDINALITY key(attribute_number, position)
INNER JOIN pg_attribute attribute
ON attribute.attrelid = constraint_record.conrelid
AND attribute.attnum = key.attribute_number
ORDER BY key.position
))::text AS columns,
referenced_namespace.nspname AS referenced_namespace,
referenced_relation.relname AS referenced_relation,
to_json(ARRAY(
SELECT attribute.attname
FROM unnest(constraint_record.confkey) WITH ORDINALITY key(attribute_number, position)
INNER JOIN pg_attribute attribute
ON attribute.attrelid = constraint_record.confrelid
AND attribute.attnum = key.attribute_number
ORDER BY key.position
))::text AS referenced_columns,
constraint_record.confupdtype AS on_update,
constraint_record.confdeltype AS on_delete,
pg_get_expr(constraint_record.conbin, constraint_record.conrelid, true) AS check_expression
FROM pg_constraint constraint_record
LEFT JOIN pg_class referenced_relation ON referenced_relation.oid = constraint_record.confrelid
LEFT JOIN pg_namespace referenced_namespace
ON referenced_namespace.oid = referenced_relation.relnamespace
WHERE constraint_record.conrelid = %<relation_oid>d
AND constraint_record.contype IN ('p', 'u', 'f', 'c')
ORDER BY constraint_record.conname
SQL- INDEXES_SQL =
<<~SQL.freeze
/* rdbr: indexes */
SELECT
index_relation.relname AS name,
index.indisunique AS unique,
access_method.amname AS using,
pg_get_expr(index.indpred, index.indrelid, true) AS predicate,
json_agg(
json_build_object(
'column', attribute.attname,
'expression', CASE
WHEN key.attribute_number = 0
THEN pg_get_indexdef(index.indexrelid, key.position::integer, true)
END
) ORDER BY key.position
)::text AS parts
FROM pg_index index
INNER JOIN pg_class index_relation ON index_relation.oid = index.indexrelid
INNER JOIN pg_am access_method ON access_method.oid = index_relation.relam
CROSS JOIN LATERAL unnest(index.indkey)
WITH ORDINALITY key(attribute_number, position)
LEFT JOIN pg_attribute attribute
ON attribute.attrelid = index.indrelid
AND attribute.attnum = key.attribute_number
WHERE index.indrelid = %<relation_oid>d
AND key.position <= index.indnkeyatts
GROUP BY
index.indexrelid,
index_relation.relname,
index.indisunique,
access_method.amname,
index.indpred,
index.indrelid
ORDER BY index_relation.relname
SQL