Re: Calling functions with table-based-type parametars

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));
    }
}