----- Original Message -----
From: "Tom Lane" <[EMAIL PROTECTED]>
> Neil Conway <[EMAIL PROTECTED]> 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 SELECT employees.* 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
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
