sql: missing syntax support for accessing member of composite-type without parens

[DrewKimball]

We allow defining PLpgSQL variables with any concrete type, including composite types. This works in some cases:

root@localhost:26257/defaultdb> CREATE OR REPLACE PROCEDURE foo() LANGUAGE PLpgSQL AS $$
                             ->   DECLARE
                             ->     v test2 := ROW(1, 2);
                             ->   BEGIN
                             ->     RAISE NOTICE '%', v;
                             ->   END
                             -> $$;
                             ->
                             -> CALL foo();
CREATE PROCEDURE

Time: 166ms total (execution 165ms / network 0ms)

NOTICE: (1,2)
CALL

Time: 11ms total (execution 11ms / network 0ms)

However, when the procedure attempts to access elements of the tuple, creating the procedure results in an object-resolution error:

root@localhost:26257/defaultdb> CREATE OR REPLACE PROCEDURE foo() LANGUAGE PLpgSQL AS $$
                             ->   DECLARE
                             ->     v test2 := ROW(1, 2);
                             ->   BEGIN
                             ->     RAISE NOTICE '% %', v.x, v.y;
                             ->   END
                             -> $$;
ERROR: no data source matches prefix: v in this context
SQLSTATE: 42P01

Jira issue: CRDB-33628

[blathers-crl[bot]]

Hi @michae2, please add branch-* labels to identify which branch(es) this release-blocker affects.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[DrewKimball]

It looks like this is because we don't allow tuple element access without extra parentheses in a SQL expr:

root@localhost:26257/defaultdb> SELECT v.b FROM (SELECT (y, x)::ab v FROM xy);
ERROR: no data source matches prefix: v in this context
SQLSTATE: 42P01
root@localhost:26257/defaultdb> SELECT (v).b FROM (SELECT (y, x)::ab v FROM xy);
  b
-----
  1
(1 row)

Time: 3ms total (execution 2ms / network 0ms)

root@localhost:26257/defaultdb>  create procedure foo() language plpgsql as $$ declare v xy := ROW(1, 2); y INT; BEGIN RAISE NOTICE '%', (v).x; END $$;
CREATE PROCEDURE

Time: 61ms total (execution 26ms / network 35ms)

root@localhost:26257/defaultdb> CALL foo();
NOTICE: 1
CALL

Time: 19ms total (execution 18ms / network 0ms)

[DrewKimball]

IIRC, postgres has some special logic to resolve PLpgSQL variable references during parsing, which is probably why this syntax is allowed in PLpgSQL but not SQL expressions.

[DrewKimball]

Actually, this isn't a PL/pgSQL-specific problem. We just don't yet support the non-parenthesized column access syntax in SQL, yet.