Re: Moving from PHP to Java: A result was returned when none was expected.

On Wed, Jun 15, 2016 at 11:24 AM, Jan de Visser <jan(at)de-visser(dot)net> wrote:

> On Wednesday, June 15, 2016 10:43:13 AM EDT David G. Johnston wrote:
> > On Wed, Jun 15, 2016 at 10:30 AM, Jan de Visser <jan(at)de-visser(dot)net>
> wrote:
> > > Point is that you're doing a SELECT. A SELECT returns a result, which
> can
> > > be
> > > empty. I would use executeQuery and ignore the result.
> > >
> > > There is a bit of a mismatch between the JDBC stored procedure model
> and
> > > the
> > > pgsql function model, because pgsql doesn't have true stored
> procedures.
> >
> > Can you point to docs, JDBC and/or PG, that describe what it means to
> > "RETURN void"?
> >
> > At a high-level SQL returns SETs and the empty set is a valid SET. I
> take
> > it from your comment that JDBC considers the empty set "a result", whose
> > record count is zero.
>
> Hrm...
>
> jan=# create or replace function foo() returns void as $$
> begin
> raise notice 'foo() called';
> end
> $$ language plpgsql;
> CREATE FUNCTION
> jan=# select foo();
> NOTICE: foo() called
> foo
> -----
>
> (1 row)
>
> So there's a row. Don't know what that row would contain, and how it would
> map
> to JDBC.
>
>
​select r is null from foo() f (r); => false ...
select pg_typeof(r) from foo() f (r); => void ...

Because "void" is a Java keyword this is not that easy to Google...​

Is "void" in the SQL standard​?

I'd say one should simply avoid "void" and chose some meaningful value to
return from most/all PostgreSQL functions. As you say they are not proper
stored procedures in the first place so trying to pretend they are is just
going to lead to frustration. Work with the system.

David J.