Skip site navigation (1) Skip section navigation (2)

Re: SQL-Invoked Procedures for 8.1

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Gavin Sherry <swm(at)linuxworld(dot)com(dot)au>
Cc: Grant Finnemore <grantf(at)guruhut(dot)co(dot)za>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: SQL-Invoked Procedures for 8.1
Date: 2004-09-23 08:17:46
Message-ID: 415286AA.6090507@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Gavin Sherry wrote:

> I don't get this multiple ResultSet stuff. All I can think of is that the
> spec has this in mind:
> 
> CallableStatement cstmt = conn.prepareCall("{call foo(); call bar();}");
> 
> or
> 
> CallableStatement cstmt = conn.prepareCall("{call foo()} {call bar();}");
> 
> or some other permutation.

It's not specific to CallableStatement; you can generate multiple 
resultsets from a plain Statement, and CallableStatement is just 
inheriting that functionality.

The common way of generating multiple resultsets is, indeed, a 
multi-statement query. For example:

>   Statement stmt = conn.createStatement();
>   stmt.execute("SELECT * FROM foo; SELECT * FROM bar");
> 
>   ResultSet rs1 = stmt.getResultSet();
>   // process rs1
>   rs1.close();
> 
>   boolean moreResults = stmt.getMoreResults();
>   assert moreResults;
> 
>   ResultSet rs2 = stmt.getResultSet();
>   // process rs2
>   rs2.close();
> 
>   stmt.close();

AFAIK the multiple-resultset stuff is not *required* functionality in 
JDBC, it's just there to support it if it does happen. The postgresql 
JDBC driver didn't actually support multiple resultsets at all until 
recently.

For function/procedure calls, I'd expect it to look like:

>   CallableStatement cstmt = conn.prepareCall("{call foo()}; {call bar()}");

and for the driver to turn that into two separate SELECT/CALL/whatever 
queries at the protocol level, and manage the multiple resultsets 
itself. The current driver doesn't handle multiple call escapes in one 
query at all, but that's really just a limitation of the reasonably dumb 
call-escape parser it currently has.

I wouldn't worry about this case unless there's some other reason that a 
*single* function/procedure call needs to return more than one set of 
results.

> I see plenty of references to multiple ResultSets but I cannot find an
> example or information on how to generate one.

That's because there's no standard way to generate them :)

-O

In response to

Responses

pgsql-hackers by date

Next:From: Grant FinnemoreDate: 2004-09-23 08:34:12
Subject: Re: SQL-Invoked Procedures for 8.1
Previous:From: Hannu KrosingDate: 2004-09-23 08:12:48
Subject: Re: BUG: possible busy loop when connection is closed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group