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