execute_sql corrupts backslash escaping in PL/pgSQL function bodies (round-trip failure)

[brian316]

Bug report

Describe the bug

When using the MCP execute_sql tool to CREATE OR REPLACE FUNCTION with PL/pgSQL bodies containing E-string backslash literals (e.g., E'\\'), the stored function body has incorrect escaping. The backslashes get doubled at some point in the JSON → MCP → PostgreSQL pipeline, resulting in a function that is semantically different from what was submitted.

This makes it impossible to reliably round-trip function definitions through execute_sql:

1. Read a function via SELECT pg_get_functiondef(oid) using execute_sql
2. Use the returned definition in a CREATE OR REPLACE FUNCTION via execute_sql
3. The resulting function body has corrupted backslash sequences

To Reproduce

1. Have a function on your Supabase project containing an E-string backslash literal:

-- This function compares the first char against a literal backslash
IF left(NEW.access_token, 1) != E'\\' THEN

2. Read the function definition via MCP execute_sql:

SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = 'encrypt_token';

3. Take the returned CREATE OR REPLACE FUNCTION statement and execute it via MCP execute_sql.
4. Compare the stored function body:

SELECT md5(pg_get_functiondef(oid)) FROM pg_proc WHERE proname = 'encrypt_token';

5. The hash does not match the original. Inspecting the body reveals the E-string E'\\' was stored as E'\\\\' (two backslashes instead of one), and comment text like (\x...) became (\\x...).

Expected behavior

The CREATE OR REPLACE FUNCTION statement executed via execute_sql should produce a byte-for-byte identical function body to the one that was read. Backslash sequences in dollar-quoted function bodies ($function$...$function$) should be passed through to PostgreSQL verbatim without additional escaping.

Workaround

Using a different dollar-quote tag (e.g., $fn_body$) and manually adjusting the backslash count can produce the correct result, but it requires trial-and-error to determine how many levels of escaping the MCP layer adds. This is fragile and not suitable for automated schema replication workflows.

Environment

MCP server: https://mcp.supabase.com/mcp
PostgreSQL version: 17.6.1.113
Agent: Claude Opus (via OpenCode)
Observed during branch provisioning (replicating function definitions from prod to a new branch via MCP execute_sql)

[gregnr]

Thanks for the report @brian316. I dug into this and I think the problem is that we're double JSON-encoding execute_sql specifically (because of the prompt-mitigation wrapper). This results in 8 (!) backslashes presented to the LLM which it incorrectly converts to 4 backslashes instead of 2.

Somewhat related, we have plans to support outputSchema / structuredContent which might eliminate the need for double encoding. Will follow up as we make progress there.