Re: 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