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

Re: Calling functions with table-based-type parametars

From: Mario Splivalo <mario(dot)splivalo(at)mobart(dot)hr>
To: Kris Jurka <books(at)ejurka(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Calling functions with table-based-type parametars
Date: 2007-02-26 16:12:35
Message-ID: 1172506356.8206.27.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-jdbc
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


In response to

Responses

pgsql-jdbc by date

Next:From: GeneDate: 2007-02-26 16:12:53
Subject: does prepareThreshold work? forced to use old driver
Previous:From: GeneDate: 2007-02-24 20:47:56
Subject: parameterized queries and prepareThreshold

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