Re: JDBC escaped scalar functions fail when used in function definition

On Thu, Mar 10, 2016 at 4:10 PM, Adam Rauch <adam(at)labkey(dot)com> wrote:

> While testing postgresql-9.4.1208.jar with our system, code is now failing
> when it attempts to use {fn now()} in the body of a PostgreSQL CREATE
> FUNCTION statement. This worked fine on 1207 and all previous versions of
> the driver.
>
> In other words, the code below succeeds on 1207 but fails on 1208 (passing
> in server, port, database, username, and password as parameters):
>
> import java.sql.*;
> public class PGTest
> {
> public static void main(String[] args) throws SQLException
> {
> try (Connection conn = DriverManager.getConnection("jdbc:postgresql://" + args[0] + ":" + args[1] + "/" + args[2], args[3], args[4]))
> {
> try (PreparedStatement stmt = conn.prepareStatement(
> "CREATE FUNCTION public.fn_now() RETURNS VOID AS $$\n" +
> " BEGIN\n" +
> " SELECT {fn now()};" +
> " END;\n" +
> "$$ LANGUAGE plpgsql;" +
> "DROP FUNCTION public.fn_now();")) {
> stmt.execute();
> }
> }
> }
> }
>
> Bug? Intentional change in behavior? Thanks, Adam
>

Intentional.

https://github.com/pgjdbc/pgjdbc/commit/9109451c65d43328b8e4344642331d7750d79cf6

​Not translating values embedded in string literals, dollar-quoted or
otherwise, seems like it is the correct way to operate - though finding an
authoritative source on the topic wasn't quick (so I gave up...).

David J.