Re: plpgsql FOR LOOP CTE problem ?

Tom,

I feel chagrined, Your absolutely right and your explanation is quite good..
I see my other test case suffered from a similar logic error.
Thanks for taking the time to look into it for me.

Dave

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Friday, August 09, 2013 6:14 PM
To: Day, David
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

"Day, David" <dday(at)redcom(dot)com> writes:
> A complete self contained test case: example of the problem with my FOR LOOP using a COMMON table expression.

Ah, I see the problem. It's got nothing particularly to do with CTEs; rather, your temporary variable is of the wrong rowtype:

> drow test.tmm%ROWTYPE; -- deleted row holder

> FOR drow IN
> WITH xrows AS (
> DELETE FROM test.tmm
> WHERE tu_id = ws_id RETURNING *
> )
> SELECT translator_id, MIN(tid_seq), MIN(ws_grp_seq)
> FROM xrows GROUP BY translator_id

That SELECT returns three columns, translator_id, MIN(tid_seq),
MIN(ws_grp_seq) (all of type int). The FOR will attempt to stuff those three values into the first three columns of "drow", which are

> name character varying,
> tu_id integer NOT NULL DEFAULT 1,
> translator_id integer NOT NULL,

All the rest are left NULL. It's bad luck that you don't get a type mismatch error here, but there's an assignment coercion from int to varchar, so the assignment of an int to the varchar name column doesn't raise an error.

Personally I'd declare drow as RECORD so as to avoid the issue.

regards, tom lane