Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Sean Chittenden <sean(at)chittenden(dot)org>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Different results in a loop with RECORD vs ROWTYPE...
Date: 2003-05-23 03:47:59
Message-ID: 13405.1053661679@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Sean ChittendenDate: 2003-05-23 04:20:36
Subject: Re: Different results in a loop with RECORD vs ROWTYPE...
Previous:From: Sean ChittendenDate: 2003-05-23 00:54:17
Subject: Re: Different results in a loop with RECORD vs ROWTYPE...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group