Re: help with PL/PgSQL bug

From: "Mike Mascari" <mascarm(at)mascari(dot)com>
To: "Neil Conway" <neilc(at)samurai(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org>, <darcy(at)wavefire(dot)com>
Subject: Re: help with PL/PgSQL bug
Date: 2003-01-11 03:20:58
Message-ID: 002f01c2b920$75f9ea80$0102a8c0@mascari.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

----- 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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2003-01-11 04:05:58 Re: 7.3 pg_dump with -Fc option crashes
Previous Message Ashley Cambrell 2003-01-11 03:14:33 Re: default to WITHOUT OIDS?