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

[7.4.6] Calling PLpgSQL stored procedures with table row arguments via JDBC?

From: Eli Bingham <eli(at)savagebeast(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: [7.4.6] Calling PLpgSQL stored procedures with table row arguments via JDBC?
Date: 2004-12-15 22:23:33
Message-ID: F50C32D5-4EE7-11D9-895F-000D932A4B80@savagebeast.com (view raw or flat)
Thread:
Lists: pgsql-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


Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2004-12-15 23:10:37
Subject: Re: [7.4.6] Calling PLpgSQL stored procedures with table row
Previous:From: Xavier PoinsardDate: 2004-12-15 18:38:00
Subject: Updated french translation

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