LLM‑generated SQL function call syntax error on AWS RDS

Q: Root Cause

PostgreSQL distinguishes between functions (invoked with SELECT) and procedures (invoked with CALL as of PostgreSQL 11). The LLM often produces calls that violate one or more of the following rules documented in the official PostgreSQL manuals:

Q: 1. Use the correct invocation keyword

For functions (returning a value) use SELECT. For procedures (no return value) use CALL only on PostgreSQL 11+.

Q: 2. Qualify the schema and ensure proper case

Either set search_path to include the target schema or prefix the function with the schema name. If the schema or function name contains upper‑case letters, quote it.

In this article

Table of Contents

Problem

When an application uses a large language model (LLM) to generate SQL for invoking PostgreSQL stored procedures, the resulting statements often fail with errors such as:

ERROR: syntax error at or near '('
ERROR: function my_schema.my_func(integer, text) does not exist
ERROR: function my_func(integer, text) does not exist
ERROR: function my_schema.my_func(unknown) does not exist

Typical symptoms observed in the RDS logs:

2024-07-12 14:03:21.123 UTC [12345] user@mydb LOG:  statement: SELECT my_schema.my_func((1, 'abc'));
2024-07-12 14:03:21.124 UTC [12345] user@mydb ERROR:  syntax error at or near '('
2024-07-12 14:05:07.456 UTC [12346] user@mydb LOG:  statement: CALL my_schema.my_func(1, 'abc');
2024-07-12 14:05:07.457 UTC [12346] user@mydb ERROR:  syntax error at or near 'CALL'

The failures prevent the downstream business logic from executing and cause retries, timeouts, and degraded SLA.

Root Cause

PostgreSQL distinguishes between functions (invoked with SELECT) and procedures (invoked with CALL as of PostgreSQL 11). The LLM often produces calls that violate one or more of the following rules documented in the official PostgreSQL manuals:

Schema qualification: If search_path does not include the target schema, an unqualified name results in “function … does not exist”. (PostgreSQL Docs – Chapter 9.3)
Argument ordering and type matching: PostgreSQL resolves overloads based on exact type signatures; missing casts lead to “function … does not exist” or “argument type mismatch”. (Chapter 9.3)
Parentheses placement: Extra parentheses (e.g., SELECT my_func((1, 'abc'))) produce “syntax error at or near ‘(‘”. (Stack Overflow discussion)
CALL vs SELECT: On PostgreSQL 10 and earlier, CALL is not supported, causing a syntax error. (Chapter 9.5)
Identifier case: Unquoted mixed‑case identifiers are folded to lower‑case, so SELECT My_Schema.My_Func(...) fails unless quoted. (Real incident)

In managed services like AWS RDS, the default search_path is often set to public only, so any generated statement that omits the schema will hit the “function does not exist” error (AWS RDS documentation).

Debug

Step‑by‑step investigation that reproduces the issue on a test RDS instance:

Capture the failing query and error. Example log entry:

2024-07-12 14:03:21.124 UTC [12345] user@mydb ERROR:  syntax error at or near '('

Inspect the function definition. Verify name, schema, and argument types:

SELECT n.nspname, p.proname, pg_catalog.pg_get_function_arguments(p.oid)
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = 'my_func';

Expected output:

 my_schema | my_func | integer, text

Check search_path for the session.

SHOW search_path;

Typical RDS output:

search_path
----------------
public

Validate argument types. Run a harmless cast test:

SELECT pg_typeof(1), pg_typeof('abc');

Result:

 integer | text

Reproduce the syntax error with a minimal query.
```
SELECT my_schema.my_func((1, 'abc'));
```
Observe the same “syntax error at or near ‘(‘”.

Solution

The fix consists of three orthogonal adjustments:

1. Use the correct invocation keyword

For functions (returning a value) use SELECT. For procedures (no return value) use CALL only on PostgreSQL 11+.

2. Qualify the schema and ensure proper case

Either set search_path to include the target schema or prefix the function with the schema name. If the schema or function name contains upper‑case letters, quote it.

3. Remove extra parentheses and add explicit casts when needed

Pass arguments exactly as defined, casting literals if PostgreSQL cannot infer the type.

Before (failing)

SELECT my_schema.my_func((1, 'abc'));

After (correct for a function on PostgreSQL 12)

SELECT my_schema.my_func(1::integer, 'abc'::text);

After (correct for a procedure on PostgreSQL 13)

CALL my_schema.my_func(1, 'abc');

Alternative: set `search_path` at session start

SET search_path = my_schema, public;
SELECT my_func(1, 'abc');

Implementation in the LLM prompt pipeline

Inject a post‑processing step that rewrites the raw LLM output:

def normalize_pg_call(llm_sql: str, db_version: int, target_schema: str) -> str:
    # 1. Strip double parentheses
    sql = re.sub(r'\(\s*\((.+?)\)\s*\)', r'(\1)', llm_sql)

    # 2. Ensure proper keyword
    if db_version < 11 and sql.lstrip().upper().startswith('CALL'):
        sql = sql.replace('CALL', 'SELECT', 1)

    # 3. Add schema qualification if missing
    pattern = r'SELECT\s+([a-zA-Z_][a-zA-Z0-9_]*)\s*\('
    if not re.search(pattern, sql, re.IGNORECASE):
        sql = re.sub(r'SELECT\s+', f'SELECT {target_schema}.', sql, flags=re.IGNORECASE)

    # 4. Append explicit casts based on a known signature map (omitted for brevity)
    return sql

Verify

After deploying the corrected SQL, run the following checks:

Execute the statement manually on the RDS endpoint:
```
SELECT my_schema.my_func(1, 'abc');
```
Expected output: the function’s return value (or CALL returns CALL success).

Confirm no error messages appear in postgresql.log:

2024-07-12 14:10:03.001 UTC [12400] user@mydb LOG:  statement: SELECT my_schema.my_func(1, 'abc');

Validate that the application’s monitoring metric (e.g., db_query_success_total) increments.
Run an integration test that exercises the same LLM‑generated path and asserts the expected result.

Prevent

Enforce schema qualification in code generation. Require the LLM prompt to include “use fully‑qualified name my_schema.my_func”.
Version‑aware generation. Detect PostgreSQL version at startup; if major_version < 11, force SELECT syntax.
Static analysis of generated SQL. Run a linter (e.g., sqlfluff) that flags extra parentheses and missing casts.
Set a safe search_path at the database level. In RDS parameter groups, add the application schema to search_path for the DB user.
Log full query text on error. Configure log_min_error_statement = error to capture the offending SQL for rapid triage.

Related Topic Hub: Data Infrastructure Troubleshooting Hub

FAQ

Why does the same LLM‑generated query work in my local PostgreSQL but fail on RDS?
Local instances often have search_path set to include the schema or run a newer PostgreSQL version that supports CALL. RDS defaults to public only and may be on PostgreSQL 10, causing both schema‑resolution and keyword‑support issues.
Can I rely on SELECT my_func(...) for procedures?
No. Procedures introduced in PostgreSQL 11 must be invoked with CALL. Using SELECT will raise “syntax error at or near ‘CALL’” or “function … does not exist”.
How do I know which argument types to cast?
Query pg_catalog.pg_proc for the function’s signature (see the debug step). Cast literals to those types explicitly, e.g., CAST($1 AS integer).
My function name contains upper‑case letters; why does PostgreSQL say it doesn’t exist?
Unquoted identifiers are folded to lower‑case. Use double quotes around mixed‑case identifiers: SELECT "My_Schema"."My_Func"(1, 'abc');
Is there a way to make RDS automatically include my schema in search_path?
Yes. Modify the DB parameter group or set ALTER ROLE my_user SET search_path = my_schema, public; to persist the setting for the role.