Re: help with PL/PgSQL bug

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Neil Conway <neilc(at)samurai(dot)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 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
mascarm(at)mascari(dot)com