cockroachdb / cockroachdb/cockroach

plpgsql: error parsing SQL query string with double-quoted identifier

Open
#150,741 1 comment 0 reactions 0 assignees View on GitHub
A-sql-plpgsql branch-release-24.3 branch-release-25.1 branch-release-25.2 branch-release-25.3 C-bug T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

(Thanks to @steven-hubbard for [finding this](https://cockroachlabs.slack.com/archives/C04U1BTF8/p1753364231259859).)

Identifiers that are reserved words in PL/pgSQL must be double-quoted. But when these double-quoted identifiers are used within a SQL statement, we fail to parse the SQL statement when trying to execute the routine. Here's an example that @dt came up with:

```sql
-- it works when the double-quoted identifier is not a reserved word
CREATE OR REPLACE FUNCTION fn1()
RETURNS TRIGGER AS $$
BEGIN
SELECT "error_msg";
END;
$$ LANGUAGE plpgsql;

SHOW CREATE FUNCTION fn1;
-- CREATE FUNCTION
-- function_name | create_statement
-- ----------------+-------------------------------
-- fn1 | CREATE FUNCTION public.fn1()
-- | RETURNS TRIGGER
-- | VOLATILE
-- | NOT LEAKPROOF
-- | CALLED ON NULL INPUT
-- | LANGUAGE plpgsql
-- | SECURITY INVOKER
-- | AS $$
-- | BEGIN
-- | SELECT error_msg;
-- | END;
-- | $$

-- it fails when the double-quoted identifier is a reserved word
CREATE OR REPLACE FUNCTION fn2()
RETURNS TRIGGER AS $$
BEGIN
SELECT "error";
END;
$$ LANGUAGE plpgsql;

SHOW CREATE FUNCTION fn2;
-- CREATE FUNCTION
-- ERROR: failed to parse query string: at or near "select": syntax error: error
-- SQLSTATE: 42601
-- DETAIL: source SQL:
-- BEGIN
-- SELECT error;
-- ^
```

This might be similar to https://github.com/cockroachdb/cockroach/issues/148985 but in this case the double-quoted identifier is within a SQL statement rather than within a FOR loop.

Jira issue: CRDB-52876

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.