Re: help with PL/PgSQL bug

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> Neil Conway <neilc@samurai.com> writes:
> > On Fri, 2003-01-10 at 20:28, Tom Lane wrote:
> >> Clearly, RETURN NEXT with an undefined record variable shouldn't dump
> >> core, but what should it do? Raise an error, or perhaps be a no-op?
>
> > I'd vote for making it a no-op. Raising an error is too severe for a
> > fairly routine occurence, IMHO. If we make it a no-op, it's consistent
> > with how I understand a SELECT INTO of 0 rows -- it doesn't produce an
> > "undefined value", but an "empty result set" (like the difference
> > between "" and a NULL pointer).
>
> There's a consistency issue here, though.  If the SELECT INTO target
> is non-record variable(s), the behavior is to set them to NULL.  Then
> if you do RETURN NEXT on that, you'd emit a row full of NULLs.
>
> It seems inconsistent that SELECT INTO a record variable produces an
> undefined result rather than a row of NULLs, when there are no rows
> in the SELECT result.  This would be an easy change to make, I think.
> We do have a tupledesc available for the SELECT, we're just not using
> it.
>
> Does Oracle's PL/SQL have a concept of record variables?  If so, what
> do they do in this situation?

In Oracle 8, a row of NULLs:
 1  CREATE OR REPLACE FUNCTION foo(t IN NUMBER) 2  RETURN NUMBER 3  IS 4   emp_rec employees%ROWTYPE; 5  BEGIN 6
SELECTemployees.* INTO emp_rec 7   FROM employees 8   WHERE employees.id = t; 9   RETURN(emp_rec.id);10* END; 
SQL> /

Function created.

SQL> select * from employees;

no rows selected

SQL> insert into employees values (1, 'Mike');

1 row created.

SQL> select foo(1) from dual;
   FOO(1)
----------        1

SQL> select foo(2) from dual;
   FOO(2)
----------


SQL> select nvl(foo(2), 0) from dual;

NVL(FOO(2),0)
-------------           0

Mike Mascari
mascarm@mascari.com