Обсуждение: [7.4.6] Calling PLpgSQL stored procedures with table row arguments via JDBC?

Hello everyone,

I've been around and around on the pgsql jdbc documentation, mailing
lists, Google, and various other sites, to no avail.  I'm finally
breaking down and asking other humans.  I hope that this isn't a
bother.

I am trying to call a PLpgSQL stored procedure via the
CallableStatement interface.  Said function takes a table type as a
parameter, and returns some scalar value.  I'm not sure which setXXX()
function to use, or what type I should pass into it.  Allow me to
illustrate:

Imagine a table defined as:

CREATE TABLE foobar (
    x        VARCHAR(24) NOT NULL,
    y        VARCHAR(24) NOT NULL,
    PRIMARY KEY (x)
);

Now say that there is a PLpgSQL function defined as:

CREATE OR REPLACE FUNCTION do_stuff
    (foobar)
RETURNS INTEGER
AS
'
DECLARE
    foobar_in        ALIAS FOR $1;
BEGIN

    [ ... stuff ... ]

    RETURN <some integer>;

END;
' LANGUAGE plpgsql VOLATILE;

I know that this is a legal PLpgSQL function definition, since every
table defines a composite type that represents a row of that table.
But how do I call this function from JDBC?  Can this be done easily?
I've tried variations with Collections, like:

Vector inputRow = new Vector ();
inputRow.add (1, "something");
inputRow.add (2, "something else");
CallableStatement proc = conn.prepareCall ("{ ? = call do_stuff (?)");
proc.registerOutParameter (1, Types.INTEGER);
proc.setObject (2, inputRow);

which returns an error like:

java.sql.SQLException: ERROR: function do_stuff("unknown") does not
exist
  Query: {? = call do_stuff (?)} Parameters: [[something, something
else]]

I know that I could output the composite structure as a string
representing a text array, and then use a call string like "? = call
do_stuff (?::text[])", but that would require that my function be
explicitly defined to accept a parameter of type TEXT [], and that I
manually unpack the TEXT array within the stored procedure.

Any help is greatly appreciated.  Thanks!

Eli Bingham
SavageBeast Technologies

On Wed, 15 Dec 2004, Eli Bingham wrote:

> I've been around and around on the pgsql jdbc documentation, mailing
> lists, Google, and various other sites, to no avail.  I'm finally
> breaking down and asking other humans.  I hope that this isn't a
> bother.

No, that's what this list is for.

> I am trying to call a PLpgSQL stored procedure via the
> CallableStatement interface.  Said function takes a table type as a
> parameter, and returns some scalar value.  I'm not sure which setXXX()
> function to use, or what type I should pass into it.  Allow me to
> illustrate:
>
> CREATE TABLE foobar (
>     x        VARCHAR(24) NOT NULL,
>     y        VARCHAR(24) NOT NULL,
>     PRIMARY KEY (x)
> );
>
> CREATE OR REPLACE FUNCTION do_stuff
>     (foobar)
> RETURNS INTEGER
>
> I know that this is a legal PLpgSQL function definition, since every
> table defines a composite type that represents a row of that table.
> But how do I call this function from JDBC?  Can this be done easily?

In theory with JDBC you should be able to do this with SQLData and
SQLInput/SQLOutput, but the PostgreSQL JDBC driver does not support this.

In plain SQL calling functions with rowtype arguments is not easy to do
before 8.0.  There is no row constructor in SQL prior to 8.0, so you need
to get the row instance created via another means.  Either via a SELECT
like:

SELECT do_stuff(foobar) FROM foobar WHERE ...

or a function that creates the rowtype:

SELECT do_stuff(create_foobar('a','b'));

where create_foobar takes two varchar arguments and returns foobar.

In 8.0 this can be done with the ROW constructor:

SELECT do_stuff(ROW('a','b'));

or

SELECT do_stuff('(a,b)'::foobar);

Kris Jurka

On Dec 15, 2004, at 3:10 PM, Kris Jurka wrote:

<snip>

>
>
> In plain SQL calling functions with rowtype arguments is not easy to do
> before 8.0.  There is no row constructor in SQL prior to 8.0, so you
> need
> to get the row instance created via another means.  Either via a SELECT
> like:
>
>

<snip>

> SELECT do_stuff(create_foobar('a','b'));
>
> where create_foobar takes two varchar arguments and returns foobar.
>

Kris,

Thanks for your speedy reply.  In general, there are lots of features
that we would like to have in 8.0, but we are more comfortable
releasing on a more stable release version at the moment.  We're
considering the transition to 8.0 at some point in the future.  I guess
I'll add another feature to the list of things we would like to have.
;)

In regards to the specific solution that you offer above with a
function that creates row objects, would it be possible to invoke a
composed function via a CallableStatement in Postgres JDBC, like this:

CallableStatement proc = conn.prepareCall ("{ ? = call do_stuff
(create_foobar (?, ?)) }");
proc.registerOutParameter (1, Types.INTEGER);
proc.setObject (2, x);
proc.setObject (3, y);

Eli Bingham
SavageBeast Technologies

On Wed, 15 Dec 2004, Eli Bingham wrote:

> In regards to the specific solution that you offer above with a
> function that creates row objects, would it be possible to invoke a
> composed function via a CallableStatement in Postgres JDBC, like this:
>
> CallableStatement proc = conn.prepareCall ("{ ? = call do_stuff
> (create_foobar (?, ?)) }");
> proc.registerOutParameter (1, Types.INTEGER);
> proc.setObject (2, x);
> proc.setObject (3, y);
>

Yes, this should work fine, behind the scenes the driver rewrites

{? = call func(?)}

into:

SELECT * FROM func(?);

So you should be able to test what a CallableStatement will do directly in
SQL.

Kris Jurka