I'm trying to extract data definitions from a PostgreSQL database for indexes into the application.
The structure in my application, currently, looks something like this:
type IndexColExpr = {
col_or_expr: string;
};
type IncludeColumn = {
name: string;
};
type Index = {
id: number;
table_id: number;
table_name: string;
name: string;
unique: boolean;
method: string;
columns: IndexColExpr[];
include_columns: IncludeColumn[];
where: string;
};
I'm using the pg_class
and pg_index
to get the information that I need.
So far everything is going well until I get to the part where the index is on expressions (not a simple column name). Those expressions are stored in pg_index.indexprs
which is of the type pg_node_tree
.
The problem is when there are multiple expressions in the index; they will all be encoded inside the indexprs
column as a single pg_node_tree
value and I need them separated. That means I have to parse pg_node_tree
to get individual expressions. Or I can convert pg_node_tree
to SQL string using pg_get_expr()
, but I still have to parse the SQL DDL script. Since they are in a list format (expr1, expr2)
I can't simply cast to text[]
. Both pg_node_tree
and the DDL script can contain arbitrary expressions, so I can't use a simple split-by-comma to get them.
Is there something in PostgreSQL that will allow me to extract individual expressions without parsing pg_node_tree
or the DDL script?
If there isn't, then maybe I shouldn't bother with pg_index
and just parse the CREATE INDEX
query provided by pg_get_indexdef()
?
There are two overloaded variants of
pg_get_indexdef()
:If you only pass the OID of the index, you get the complete reconstructed
CREATE INDEX
DDL script.But if you also pass the
smallint
number of the index expression ( =pg_attribute.attnum
) and aboolean
"pretty" flag, you get the definition of that index column. As the manual puts it:To get all index columns of a given index separately, and in order (plain column references and expressions alike):
I am going to
pg_attribute
directly, we don't need to involvepg_index
for this. If you need the connection:If there can be ambiguity, schema-qualify the index name:
This does not include any decorators per index column like
DESC NULLS LAST
, operator class, collation, ... Those are encoded separately inindoption
,indclass
,indcollation
of the tablepg_index
.