Re: plpgsql FOR LOOP CTE problem ?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Day, David" <dday(at)redcom(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: plpgsql FOR LOOP CTE problem ?
Date: 2013-08-09 22:13:44
Message-ID: 23683.1376086424@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2013-08-09 22:39:07 Re: Snapshot backups
Previous Message Michael Nolan 2013-08-09 22:11:42 Re: incremental dumps