Add support for ANY/SOME and ALL operators

[mjburghoffer]

Currently this library does not support array operations.

It parses them fine, and will even render them (albeit incorrectly). I think an example is the best way to show this.

const psql = require("pgsql-ast-parser");

const ast = psql.parseFirst(`SELECT '{"foo": "bar"}'::jsonb @> ANY (ARRAY ['{"foo": "bar"}', '{"foo":  "baz"}']::jsonb[]);`);

console.log(psql.toSql.statement(ast));

output is:

SELECT ((('{"foo": "bar"}')::jsonb ) @> ("any" (((ARRAY[('{"foo": "bar"}'), ('{"foo":  "baz"}')])::jsonb [])) ))

and if we declutter it a bit:

SELECT '{"foo": "bar"}'::jsonb @> "any" (ARRAY['{"foo": "bar"}', '{"foo":  "baz"}']::jsonb[]);

If you notice the issue, it is that ANY is quoted, since the library thinks it is a keyword. Running this query nets you this error in postgres: [42883] ERROR: function any(jsonb[]) does not exist.

As an AST, the any is represented as a call node, and eventually ident gets called on it:

function ident(nm: string, forceDoubleQuote?: boolean) {
    if (!forceDoubleQuote) {
        // only add quotes if has upper cases, or if it is a keyword.
        const low = nm.toLowerCase();
        if (low === nm && !kwSet.has(low) && /^[a-z][a-z0-9_]*$/.test(low)) {
            return nm;
        }
    }
    return '"' + nm + '"';
}

kwSet includes any, so it gets quoted. The same issue would happen with SOME (an alias of ANY) and ALL.

[mjburghoffer]

I think it is also worth updating this issue with the fact that surrounding an ANY with parenthesis is also not good.

Valid:

SELECT * FROM table WHERE id = ANY(ARRAY[1, 2, 3]);

Not valid:

SELECT * FROM table WHERE id = (ANY(ARRAY[1, 2, 3]));

[mjburghoffer]

After going over the codebase, I think it would be best to treat ANY, ALL and SOME as separate from keywords. They can still be considered keywords, but they should get their own visitor I think.

Another approach is to treat binary operators as things that can be modified with ANY or ALL. For instance:

export interface ExprBinary extends PGNode {
    type: 'binary';
    left: Expr;
    right: Expr;
    op: BinaryOperator;
    arrayOp?: 'any' | 'all' | 'some';
    opSchema?: string;
}

const myOperator: ExprBinary = {
  type: 'binary',
  left: Ref.id,
  right: Ref.param1,
  op: '@>',
  arrayOp: 'any',
}

myOperator would render as something like id @> ANY ($1)