Can't run CREATE PROCEDURE with new Postgres' 14 new SQL-standard body

From: Thomas Kellerer <shammat(at)gmx(dot)net>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Can't run CREATE PROCEDURE with new Postgres' 14 new SQL-standard body
Date: 2021-10-03 12:07:59
Message-ID: 582e5837-d639-d774-5a6a-b778d91b2ec1@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hello,

Postgres 14 supports SQL standard conforming bodies for functions and procedures
which are no longer string constants, e.g.

create or replace procedure do_stuff()
language sql
begin atomic
select 1;
select 2;
end

However, it seems that the JDBC driver will parse the string and split it into
multiple statements based on the semicolons thus making it an invalid create procedure
statement.

The above CREATE statement runs fine in psql, but the following Java code:

Connection con = DriverManager.getConnection(...);

String sql =
"create or replace procedure do_stuff()\n" +
" language sql\n" +
" begin atomic\n" +
" select 1;\n" +
" select 2;\n" +
" end";

Statement stmt = con.createStatement();
stmt.execute(sql);

fails with:

org.postgresql.util.PSQLException: ERROR: syntax error at end of input
Position: 79

Position 79 is the semicolon after "select 1"

Is there a way to disable the parsing/splitting in the driver so that it will be possible
to run such statements?

This is important for JDBC based SQL client tools.

Regards
Thomas

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message ldh@laurent-hasson.com 2021-10-04 03:02:47 RE: JDBC Meta-data performance for Foreign Keys
Previous Message Mark Rotteveel 2021-10-02 08:57:10 Re: JDBC Meta-data performance for Foreign Keys