psql `\d <table>` query

[sunng87]

The \d <tablename> will be translated to query:

SELECT c.oid,
      n.nspname,
      c.relname
    FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relname OPERATOR(pg_catalog.~) '^(tablename)$' COLLATE pg_catalog.default
      AND pg_catalog.pg_table_is_visible(c.oid)
    ORDER BY 2, 3;

Currently it's blocked by COLLATE support:

This feature is not implemented: Unsupported ast node in sqltorel: Collate { expr: Value(ValueWithSpan { value: SingleQuotedString("^(clubs)$"), span: Span(Location(1,170)..Location(1,181)) }), collation: ObjectName([Identifier(Ident { value: "pg_catalog", quote_style: None, span: Span(Location(1,190)..Location(1,200)) }), Identifier(Ident { value: "default", quote_style: None, span: Span(Location(1,201)..Location(1,208)) })])

We will need to rewrite the sql to remove COLLATE before it is landed in datafusion (apache/datafusion#9192).

[sunng87]

Filter part of this statement:

                selection: Some(
                    BinaryOp {
                        left: BinaryOp {
                            left: CompoundIdentifier(
                                [
                                    Ident {
                                        value: "c",
                                        quote_style: None,
                                        span: Span(Location(6,7)..Location(6,8)),
                                    },
                                    Ident {
                                        value: "relname",
                                        quote_style: None,
                                        span: Span(Location(6,9)..Location(6,16)),
                                    },
                                ],
                            ),
                            op: PGCustomBinaryOperator(
                                [
                                    "pg_catalog",
                                    "~",
                                ],
                            ),
                            right: Collate {
                                expr: Value(
                                    ValueWithSpan {
                                        value: SingleQuotedString(
                                            "^(clubs)$",
                                        ),
                                        span: Span(Location(6,40)..Location(6,51)),
                                    },
                                ),
                                collation: ObjectName(
                                    [
                                        Identifier(
                                            Ident {
                                                value: "pg_catalog",
                                                quote_style: None,
                                                span: Span(Location(6,60)..Location(6,70)),
                                            },
                                        ),
                                        Identifier(
                                            Ident {
                                                value: "default",
                                                quote_style: None,
                                                span: Span(Location(6,71)..Location(6,78)),
                                            },
                                        ),
                                    ],
                                ),
                            },
                        }

Tasks to be done:

• rewrite PGCustomBinaryOperator to simply ~
• rewrite Collate to its expr.

[sunng87]

Second query to support:

SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, false AS relhasoids, c.relispartition, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident, am.amname
    FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
    LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
    WHERE c.oid = '16386';

Current error:

This feature is not implemented: Unsupported SQL type Custom(ObjectName([Identifier(Ident { value: "pg_catalog", quote_style: None, span: Span(Location(1,253)..Location(1,263)) }), Identifier(Ident { value: "text", quote_style: None, span: Span(Location(1,264)..Location(1,268)) })]), [])

[sunng87]

3rd query:

SELECT a.attname,
      pg_catalog.format_type(a.atttypid, a.atttypmod),
      (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
       FROM pg_catalog.pg_attrdef d
       WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
      a.attnotnull,
      (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
       WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
      a.attidentity,
      a.attgenerated
    FROM pg_catalog.pg_attribute a
    WHERE a.attrelid = '16392' AND a.attnum > 0 AND NOT a.attisdropped
    ORDER BY a.attnum;

ERROR:  Invalid (non-executable) plan after Analyzer
caused by
Error during planning: Correlated scalar subquery must be aggregated to return at most one row