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

> I was under the impression that a ROWTYPE was basically akin to a C
> structure that represented a ROW from the specified table.

Indeed, but your SELECT doesn't deliver a ROW from the specified table.
It only delivers one column. If you'd said "SELECT * FROM s.c" then
things would have worked as you expect. But in the above command, the
column matching is positional, and so it's r_c.x not r_c.y that gets
loaded with the sole column supplied by the SELECT.

I don't think that the choice of positional matching is wrong, and in
any case we couldn't change it without breaking a lot of existing
plpgsql code. Arguably it should be an error to supply the wrong number
of columns to fill a rowtype result variable, though.

regards, tom lane