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

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

Oooh, if indeed that is the way that things are implemented, then yes,
that is pilot error.  I should submit some doco to that effect because
that would have been most useful to know upfront.

I was under the impression that a ROWTYPE was basically akin to a C
structure that represented a ROW from the specified table.  Each
column was a pointer to the datum returned by the SELECT.  Therefore,
if r_c is defined as s.c%ROWTYPE, then r_c.x, r_c.y, and r_c.w would
all be initialized to NULLs until the FOR r_c IN SELECT populated the
values of the r_c structure, with r_c.y mapping to d.y.  Granted the
mapping would break down instantly if the SELECT was rewritten as:

    FOR r_c IN SELECT d.y AS x...

but I'd think that'd be a powerful feature that could be easily
abused, but very useful if indeed ROWTYPEs were just pointers to the
returned datums... instead, datums are copied, something I was not
wild to discover.  I thought everything was done by reference in
pl/pgsql.

Are there any pl/pgsql -> C converters?

-sc

--
Sean Chittenden