PG14 added a new syntax for creating SQL functions (see release notes, as well as this SO):
CREATE OR REPLACE FUNCTION asterisks(n int)
RETURNS SETOF text
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
BEGIN ATOMIC
SELECT repeat('*', g) FROM generate_series (1, n) g; -- <-- Note this semicolon
END;Unfortunately, our SQL query parser splits this SQL at the semicolon terminating the select, resulting in mangled SQL being sent to PG, and failing. It seems like the JDBC driver has the same issue (tested in Rider's database UI).
It's possible to turn off SQL rewriting globally, this is the proper fix:
AppContext.SetSwitch("Npgsql.EnableSqlRewriting", false);... but although this is recommended, many users probably won't be able to do this.
As a hacky workaround, it's possible to tell Npgsql to not parse/rewrite SQL for this specific command, by adding a single unnamed parameter, triggering positional parameters:
cmd.Parameters.Add(new() { Value = 0 });Originally flagged in this SO.
PG14 added a new syntax for creating SQL functions (see release notes, as well as this SO):
Unfortunately, our SQL query parser splits this SQL at the semicolon terminating the select, resulting in mangled SQL being sent to PG, and failing. It seems like the JDBC driver has the same issue (tested in Rider's database UI).
It's possible to turn off SQL rewriting globally, this is the proper fix:
... but although this is recommended, many users probably won't be able to do this.
As a hacky workaround, it's possible to tell Npgsql to not parse/rewrite SQL for this specific command, by adding a single unnamed parameter, triggering positional parameters:
Originally flagged in this SO.