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

:-/ I would argue differently for the below points, but it's kinda
mute given RECORD does the job.

*) Explicitly using ROWTYPE is like using a statically declared
language. In the event that the schema changes, the stored
procedure would break and with good cause. Using a RECORD type
obviates this possibility and things may silently continue to work
with differing results.

*) SELECT * into a ROWTYPE is an expensive practice and I would argue
horribly inefficient in that an entire row has been lifted off of
disk, copied from kernel to user space, and then gets copied into the
resulting ROWTYPE. Minimizing this I would think would be of great
interest to DBAs where memory and disk cycles are precious. If
SELECT'ing a single row, or omitting specific columns from a row
throws off the placement of data into ROWTYPEs because it
sequentially places data into the ROWTYPE var, the logic of placing
data into ROWTYPEs is incorrect and that placement of data into a
ROWTYPE should be done by matching the name of the resulting column
from the SELECT into the corresponding name of the element in the
ROWTYPE. The name of an element in a ROWTYPE and table is guaranteed
to be unique by their very nature.

Anyway, my thoughts in the event that pl/pgsql gets its famed overhaul
on the script side.

*shrugs* Given the large amount of pl/pgsql code that I've got running
around, I'm slowly (2-3mo completion time it's looking like) working
on have pl/pgsql scripts compiled to C and then .so's automatically
loaded from their sequentially numbered names stored in the
data/plpgsql. Recompiling the .so between version dumps isn't an
issue because the whole db has to be reimported. If anyone's got any
advice on the topic, I'm all ears, but that's the direction I'm
working toward to help solve some of the inefficiencies in pl/pgsql.
I've nabbed the gram.y and scan.l files from plpgsql so scripts should
be 100% compatible. I figure every BSD and likely every linux server
box likely has a compiler on board, same with Slowaris if they're
running postgresql and are looking for speed. But, if they don't,
then they can use plpgsql instead of plpgsqlc. FWIW, compiler flags
and variables are stored in system catalogs so those can be changed
(Tom, any preferences on a system catalog name for compiler bits, or
should I convert things to abuse the GUC infrastructure). I'm
unconvinced that there is a need for a way to recompile a function
other than DROP'ing it and re-CREATE'ing it. Any need for an ALTER
FUNCTION s.f() RECOMPILE? Since .so's are mmpap()'ed on almost every
system, this is also of memory interest to folks, never mind the
speed.

-sc

--
Sean Chittenden