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
