The cloud sql shell produces an error when a function is defined using the $$ syntax.
To Reproduce
Following some examples from our docs, we observe that the following function:
CREATE OR REPLACE FUNCTION update_code(
code_name VARCHAR,
new_rules JSONB
)
RETURNS promo_codes AS $$
UPDATE promo_codes SET rules = new_rules
WHERE code = code_name
RETURNING *;
$$ LANGUAGE SQL;
On 23.2.1 using cockroach demo works as expected:
On 23.2.1 using the cloud sql shell produces a lexical error:
CREATE FUNCTION fib(n int) RETURNS INT AS $$
DECLARE
tmp INT;
a INT := 0;
b INT := 1;
i INT := 2;
BEGIN
IF n < 0 THEN
RAISE EXCEPTION 'n must be non-negative';
END IF;
IF n = 0 THEN RETURN 0; END IF;
IF n = 1 THEN RETURN 1; END IF;
LOOP
IF i > n THEN EXIT; END IF;
tmp := a + b;
a := b;
b := tmp;
i := i + 1;
END LOOP;
RETURN b;
END
$$ LANGUAGE PLpgSQL;
Describe the problem
The cloud sql shell produces an error when a function is defined using the
$$
syntax.To Reproduce
Following some examples from our docs, we observe that the following function:
On 23.2.1 using
cockroach demo
works as expected:On 23.2.1 using the cloud sql shell produces a lexical error:
This is a bug reported on our community forum by a user when they tried testing out creating a function using a loop:
Jira issue: CRDB-36454