cockroachdb / cockroachdb/cockroach
plpgsql: error parsing SQL query string with double-quoted identifier
- 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
Assessment
This issue has not been assessed yet.