Thread: Calling functions with table-based-type parametars
Hello again :)
I have a PSQL function that accepts table-based-type as parametar:
CREATE TABLE t1(c1 int4, c2 varchar);
CREATE FUNCTION f1(t1) RETURNS VOID
AS 'BEGIN RETURN END;' LANGUAGE 'plpgsql';
Now, when I call this function from another function (or from psql, for
instance), i need to do it like this:
select f1(ROW(1, 'sometext'));
How do I do that from JDBC? Usualy I did it like this:
callStatement = conn.getCallStatement("{call f1(?, ?)}");
callStatement.setInt(1, 1);
callStatement.setString(2, 'sometext');
callStatement.execute();
ResultSet rs = callStatement.getResultSet();
But when I do it like that I get 'function does not exists', which is
logical (my function accepts one, not two parametars).
I have found out that I can use createstatement like this:
Connection c = DriverManager.getConnection('jdbc://...');
s = c.createStatement();
ResultSet rs = s.executeQuery("select f1(ROW(1, 'sometext'));");
Now, as I understand, first snippet uses prepared statements, and second
one doesnt. Can I have prepared statements and still pass ROW as
function parametar? Or JDBC does not support that?
Mike
On Fri, 23 Feb 2007, Mario Splivalo wrote:
> Hello again :)
>
> I have a PSQL function that accepts table-based-type as parametar:
>
> CREATE TABLE t1(c1 int4, c2 varchar);
>
> CREATE FUNCTION f1(t1) RETURNS VOID
> AS 'BEGIN RETURN END;' LANGUAGE 'plpgsql';
>
> Now, when I call this function from another function (or from psql, for
> instance), i need to do it like this:
>
> select f1(ROW(1, 'sometext'));
>
> How do I do that from JDBC? Usualy I did it like this:
>
> callStatement = conn.getCallStatement("{call f1(?, ?)}");
> ResultSet rs = s.executeQuery("select f1(ROW(1, 'sometext'));");
>
Why not conn.prepareCall("{call f1(ROW(?, ?)}") or
conn.prepareStatement("SELECT f1(ROW(?, ?))");
Technically the JDBC way would be to to create a class implementing
SQLData, but since the JDBC driver doesn't implement such a thing, one of
the above should work.
Kris Jurka
On Fri, 2007-02-23 at 14:20 -0500, Kris Jurka wrote:
>
> On Fri, 23 Feb 2007, Mario Splivalo wrote:
>
> > Hello again :)
> >
> > I have a PSQL function that accepts table-based-type as parametar:
> >
> > CREATE TABLE t1(c1 int4, c2 varchar);
> >
> > CREATE FUNCTION f1(t1) RETURNS VOID
> > AS 'BEGIN RETURN END;' LANGUAGE 'plpgsql';
> >
> > Now, when I call this function from another function (or from psql, for
> > instance), i need to do it like this:
> >
> > select f1(ROW(1, 'sometext'));
> >
> > How do I do that from JDBC? Usualy I did it like this:
> >
> > callStatement = conn.getCallStatement("{call f1(?, ?)}");
> > ResultSet rs = s.executeQuery("select f1(ROW(1, 'sometext'));");
> >
>
> Why not conn.prepareCall("{call f1(ROW(?, ?)}") or
> conn.prepareStatement("SELECT f1(ROW(?, ?))");
When I try it like above (using conn.prepaleCall), i get this:
2007-02-26 16:58:19.004 CET [9592] <jura> SELECTERROR: function
f1(record) does not exist
2007-02-26 16:58:19.004 CET [9592] <jura> SELECTHINT: No function
matches the given name and argument types. You may need to add explicit
type casts.
I know I can't but I still tried creating function with record-type
parametar, postgres won't let me do so.
I tried SELECT f1(ROW(1, 'one'));, that works. If I try it like this:
SELECT f1(ROW(1, 'one', 'two)), postgres tells me I have to many
columns, and it can't cast to t1 type.
If, in Java code, i do my call like this:
conn.prepareCall("{call f1(ROW(?, ?, ?)}"
and set all three parametars, I still get 'function f1(record) does not
exist', no complaints about missing or to many columns.
I'm using pg7.4 JDBC driver with pg8.1.2, and I tought that might be the
problem. But I switched to postgresql-8.1-407.jdbc3.jar, and I still get
the same error.
Mike
On Mon, 26 Feb 2007, Mario Splivalo wrote:
>> Why not conn.prepareCall("{call f1(ROW(?, ?)}") or
>> conn.prepareStatement("SELECT f1(ROW(?, ?))");
>
> When I try it like above (using conn.prepaleCall), i get this:
>
> 2007-02-26 16:58:19.004 CET [9592] <jura> SELECTERROR: function
> f1(record) does not exist
> 2007-02-26 16:58:19.004 CET [9592] <jura> SELECTHINT: No function
> matches the given name and argument types. You may need to add explicit
> type casts.
This is saying you need to write it with a cast from the row to the table
type:
conn.prepareCall("{call f1(ROW(?, ?)::t1)}")
Kris Jurka
Mario Splivalo wrote:
>> This is saying you need to write it with a cast from the row to the table
>> type:
>>
>> conn.prepareCall("{call f1(ROW(?, ?)::t1)}")
>
> Can't do that either. Postgres tells me that there is no type t1:
>
> 2007-02-26 18:25:19.004 CET [10324] <jura> PARSEERROR: type "t1" does
> not exist
>
> I even tried creating type _t1 wich has same member variables as table
> rows, and created function f2 wich takes _t1 as parametar, still same
> error: type "_t1" does not exsit.
>
The attached test case works fine for me without a cast. Perhaps you
can modify this to show the failure you're getting.
Kris Jurka
import java.sql.*;
public class RowFunc {
public static void main(String args[]) throws Exception {
Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/jurka","jurka","");
Statement stmt = conn.createStatement();
try {
stmt.execute("DROP FUNCTION f1(t1)");
} catch (SQLException sqle) {
}
try {
stmt.execute("DROP TABLE t1");
} catch (SQLException sqle) {
}
stmt.execute("CREATE TABLE t1 (a int, b int)");
stmt.execute("CREATE FUNCTION f1(t1) RETURNS int AS 'SELECT 1' language 'SQL'");
stmt.close();
CallableStatement cs = conn.prepareCall("{? = call f1(ROW(?,?))}");
cs.registerOutParameter(1, Types.INTEGER);
cs.setInt(2,2);
cs.setInt(3,3);
cs.execute();
System.out.println(cs.getInt(1));
}
}
On Mon, 2007-02-26 at 10:53 -0700, Kris Jurka wrote:
> Mario Splivalo wrote:
> >> This is saying you need to write it with a cast from the row to the table
> >> type:
> >>
> >> conn.prepareCall("{call f1(ROW(?, ?)::t1)}")
> >
> > Can't do that either. Postgres tells me that there is no type t1:
> >
> > 2007-02-26 18:25:19.004 CET [10324] <jura> PARSEERROR: type "t1" does
> > not exist
> >
> > I even tried creating type _t1 wich has same member variables as table
> > rows, and created function f2 wich takes _t1 as parametar, still same
> > error: type "_t1" does not exsit.
> >
>
> The attached test case works fine for me without a cast. Perhaps you
> can modify this to show the failure you're getting.
>
As it turns out, it was permissions problem. I created the table and the
function as the database owner. But, there is user set up for
tomcat/jdbc, and all data manipulation is done trough psql functions.
Now, that user has GRANT EXECUTE ON f1(t1) TO jdbcuser;
Still, when I try to SELECT f1 from psql, connected as uset jdbcuser, I
get the ERROR: function f1(record) does not exist.
If I try it with dbowner user, everything works fine. Now I don't know
how to set up permisions, but that's for another mailing list.
Thank you for your effort, Kris.
Mario
On Tue, 2007-02-27 at 13:24 +0100, Mario Splivalo wrote:
> As it turns out, it was permissions problem. I created the table and the
> function as the database owner. But, there is user set up for
> tomcat/jdbc, and all data manipulation is done trough psql functions.
> Now, that user has GRANT EXECUTE ON f1(t1) TO jdbcuser;
I forgot to GRANT USAGE on schema public for jdbcuser. Now it's ok.
Mike