Thread: PL/pgSQL Function Help

I've got an issue with a plpgsql function and I have not
been able to find any reference to this situation in searching.
I hope that someone can point me in the correct direction.

I'm running v8.0.3 and assuming a test set up of:
-----------------------------
CREATE TYPE myrec AS (
    id int
);

CREATE OR REPLACE FUNCTION test(x int) RETURNS myrec
    AS '
  DECLARE
    output RECORD;
  BEGIN
    IF x THEN
      RETURN NULL;
    END IF;

    SELECT INTO output 9999;
    RETURN output;
  END;
'
    LANGUAGE plpgsql;
-----------------------------

Testing the function yields:
-----------------------------
xxx=# select * from test(0);
  id
------
 9999
(1 row)

xxx=# select * from test(1);
 id
----

(1 row)
-----------------------------

My dilema is that the program I'm trying to write this
function for (not mine) expects that if (in this case) a
1 is sent, it should have zero rows returned (0 affected
rows).  It seems that any time I have a return type of
a record I'll get a nice NULL record, but it still counts
as something.

Is there no way in Postgres that I can simply not return
anything so I show zero rows?

Thanks.

--
David

"Niblett, David A" <niblettda@gru.com> writes:
> Is there no way in Postgres that I can simply not return
> anything so I show zero rows?

Make the function return SETOF myrec not just myrec.  Then you
can return zero or one (or more) myrec's.

            regards, tom lane

On Fri, Dec 16, 2005 at 03:30:01PM -0500, Tom Lane wrote:
> "Niblett, David A" <niblettda@gru.com> writes:
> > Is there no way in Postgres that I can simply not return
> > anything so I show zero rows?
>
> Make the function return SETOF myrec not just myrec.  Then you
> can return zero or one (or more) myrec's.

Dunno if this indicates a possible problem, but the function as
posted fails an assertion in an assert-enabled 8.0.5 server (but
not in 8.1.1 or 8.2devel).

test=> SELECT test(0);
server closed the connection unexpectedly

#2  0x001f7e30 in ExceptionalCondition (conditionName=0x220b10 "!(typeId == ( (olddata)->t_choice.t_datum.datum_typeid
))",errorType=0x2208e0 "FailedAssertion",  
    fileName=0x220868 "tuptoaster.c", lineNumber=830) at assert.c:51
#3  0x00054ac8 in toast_flatten_tuple_attribute (value=2231056, typeId=2230496, typeMod=2230376) at tuptoaster.c:830

--
Michael Fuhr