Re: Different results in a loop with RECORD vs ROWTYPE...

Sean Chittenden <sean(at)chittenden(dot)org> writes:
> CREATE TABLE s.c (
> x BIGINT NOT NULL,
> y BIGINT NOT NULL,
> w INT NOT NULL DEFAULT 1::INT
> );

> DECLARE
> r_c s.c%ROWTYPE; -- RECORD;
> BEGIN
> FOR r_c IN SELECT d.y FROM s.c d WHERE d.x = NEW.x LOOP
> PERFORM s.add_y_to_x(r_c.y,NEW.z);

It seems to me that the rowtype of this SELECT's result is (y bigint).
When you declare r_c as RECORD, it adopts that rowtype, and so the
reference to r_c.y in the PERFORM delivers the value you want. But
when you declare r_c as s.c%ROWTYPE, that is (x bigint, y bigint, w int),
the result of the SELECT's first column is delivered into r_c.x and then
the other two columns are set to null. So r_c.y is null in the PERFORM.

I think this is basically pilot error, though one could certainly argue
that the system ought to be complaining that the SELECT didn't deliver
enough columns to fill the rowtype variable. Any thoughts? Can anyone
report what Oracle's pl/sql does in comparable situations?

regards, tom lane