Re: [7.4.6] Calling PLpgSQL stored procedures with table row

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