Postgres Pro
Downloads
Home   >   mailing lists

Thread: bug 1201

bug 1201

From
federico
Date:
i saw in bug 1201 that some got my problem
i detail it
i have a function that returns a void

CREATE OR REPLACE FUNCTION myFunction(int2, int2)
RETURNS void AS
$BODY$
    UPDATE    table
    SET    field1 = $1
    WHERE    field2 = $2;
$BODY$
LANGUAGE 'sql' VOLATILE;


when i execute it with a callablestatement

CallableStatement cstm = conn.prepareCall({ call myFunction(CAST(? AS INT2), CAST(? AS INT2)) });
cstm.setInt(1, aValue.getValue());
cstm.setInt(2, anotherValue.getValue());

i got the following error

org.postgresql.util.PSQLException: ERROR: function "jcpo_setallusersbyissuemessagestatus" in FROM has unsupported
returntype 
    at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
    at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
    at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
    at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
    at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)


any news about it?
i'm using PostgreSQL Database Server 8.0-beta2-dev3 on Windows and
pgdev.307.jdbc3.jar drivers

thank you

federico

Re: bug 1201

From
Kris Jurka
Date:
On Tue, 19 Oct 2004, federico wrote:

> i saw in bug 1201 that some got my problem
> [ you can't do "SELECT * FROM func_returning_void();" ]

This patch seems to fix it, although I have no idea what the actual
implications are, I just changed any place that produced an error.

Kris Jurka

Re: bug 1201

From
Alvaro Herrera
Date:
On Tue, Oct 19, 2004 at 06:23:07PM -0500, Kris Jurka wrote:
>
> On Tue, 19 Oct 2004, federico wrote:
>
> > i saw in bug 1201 that some got my problem
> > [ you can't do "SELECT * FROM func_returning_void();" ]
>
> This patch seems to fix it, although I have no idea what the actual
> implications are, I just changed any place that produced an error.

Huh, shouldn't the user rather do

SELECT func_returning_void();
?

It seems rather silly to try to get tuples from a function returning
void.

Just an idea, I don't have JDBC handy to test.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Por suerte hoy explotó el califont porque si no me habría muerto
de aburrido"  (Papelucho)

Re: bug 1201

From
Kris Jurka
Date:
On Tue, 19 Oct 2004, Alvaro Herrera wrote:

> Huh, shouldn't the user rather do
>
> SELECT func_returning_void();

The problem is that the function may actually be a SRF so the JDBC driver
transforms to the "SELECT * FROM" form.  The JDBC driver doesn't want to
get into the business of trying to determine which function will actually
be called due to overloading complications, and prior to the introduction
of "void" it worked.  The plain select with a void returning function
seems more useful, but the JDBC driver has supported the SRF version for
some time now, so I'm worried about backwards compatibility.

Kris Jurka

Re: bug 1201

From
Tom Lane
Date:
Kris Jurka <books@ejurka.com> writes:
> On Tue, 19 Oct 2004, Alvaro Herrera wrote:
>> Huh, shouldn't the user rather do
>> SELECT func_returning_void();

> The problem is that the function may actually be a SRF so the JDBC driver
> transforms to the "SELECT * FROM" form.

It's not really any weirder to allow this than to allow "SELECT func()",
as far as I can see.  Either way, you end up with a tuple containing one
column of type "void".  If we had a CALL statement then there might be
a reasonable argument for disallowing void-returning functions in both
places, but for now I've applied a modified form of Kris' patch.

            regards, tom lane

Re: bug 1201

From
Federico Fissore
Date:
thank you for your attention

kris, i'll keep the patch but i won't patch my pgsql because i cannot
tell my "customers" (it's a OS program; if it will run ok, maybe my
company will switch from mssql to pgsql) to patch their pgsql installation.

i look forward for the final release of pgsql 8 and of the jdbc drivers

thank you

federico

federico wrote:

> i saw in bug 1201 that some got my problem
> i detail it
> i have a function that returns a void
>
> CREATE OR REPLACE FUNCTION myFunction(int2, int2)
> RETURNS void AS
> $BODY$
>     UPDATE    table     SET    field1 = $1     WHERE    field2 = $2;
> $BODY$
> LANGUAGE 'sql' VOLATILE;
>
>
> when i execute it with a callablestatement
>
> CallableStatement cstm = conn.prepareCall({ call myFunction(CAST(? AS
> INT2), CAST(? AS INT2)) });
> cstm.setInt(1, aValue.getValue());
> cstm.setInt(2, anotherValue.getValue());
>
> i got the following error
>
> org.postgresql.util.PSQLException: ERROR: function
> "jcpo_setallusersbyissuemessagestatus" in FROM has unsupported return
> type
>    at
> org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
>
>    at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
>    at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
>    at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
>    at
> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
>
>    at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
>
>
>
> any news about it?
> i'm using PostgreSQL Database Server 8.0-beta2-dev3 on Windows and
> pgdev.307.jdbc3.jar drivers
>
> thank you
>
> federico
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>