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

Re: SQL-Invoked Procedures for 8.1

From: Gavin Sherry <swm(at)linuxworld(dot)com(dot)au>
To: Oliver Jowett <oliver(at)opencloud(dot)com>
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:47:45
Message-ID: Pine.LNX.4.58.0409231846040.12584@linuxworld.com.au (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, 23 Sep 2004, Oliver Jowett wrote:

> 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 :)

Okay. So, its something that can be handled in the driver. That's what I
thought.

Do you have any idea about databases returning result sets from SQL
procedures (ie, not functions).

Thanks,

Gavin

In response to

Responses

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2004-09-23 10:58:00
Subject: Re: SQL-Invoked Procedures for 8.1
Previous:From: Grant FinnemoreDate: 2004-09-23 08:34:12
Subject: Re: SQL-Invoked Procedures for 8.1

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