PG 14 Create Rule ERROR - RETURNING list has too few entries

On 28/02/2022, at 3:44 PM, Lucas <root@sud0.nz> wrote:

Hi,

On PG 14.1, when trying to create a RULE [1], I get the following error:

Ooops, I forgot to paste the CREATE RULE statement as an example:

The idea of these roles is a superimposed version of post-QBO ja_notes that exposes an inferred (from ja_jobs) "clientid" after the redundant column was removed from public.ja_notes. It also localizes timestamps in accordance to the legacy modification dates, and truncates start_time to whole seconds only

CREATE RULE r_di_ja_notes AS
ON DELETE TO doctrine.ja_notes DO INSTEAD DELETE FROM ja_notes r_1103088 WHERE (r_1103088.id = old.id)
RETURNING r_1103088.id,
r_1103088.mobiuserid,
r_1103088.jobid,
r_1103088.description,
r_1103088.time_start,
r_1103088.file_type,
r_1103088.note_type,
r_1103088.item_code,
NULL::bigint AS invoiceid,
NULL::text AS integration_key,
NULL::text AS integration_type,
r_1103088.taskid,
r_1103088.parentid,
r_1103088.pay_txn_id,
r_1103088.pay_status,
r_1103088.n_quote_status,
r_1103088.purchase_order_line_guid,
r_1103088.unleashed_status,
r_1103088.accounts_payable_key,
r_1103088.accounts_payable_type,
r_1103088.purchase_order_guid,
r_1103088.eventflag,
r_1103088.field_options,
r_1103088.field_type_id,
timezone('Etc/UTC'::text,
r_1103088.ts_created) AS timezone,
timezone('Etc/UTC'::text,
r_1103088.ts_modified) AS timezone,
r_1103088.created_userid,
r_1103088.modified_userid,
r_1103088.external_id,
r_1103088.job_users_id,
r_1103088.billable_id,
r_1103088.bill_item_id,
NULL::bigint AS clientid;

Lucas <root@sud0.nz> writes:

Ooops, I forgot to paste the CREATE RULE statement as an example:

The idea of these roles is a superimposed version of post-QBO ja_notes that exposes an inferred (from ja_jobs) "clientid" after the redundant column was removed from public.ja_notes. It also localizes timestamps in accordance to the legacy modification dates, and truncates start_time to whole seconds only

CREATE RULE r_di_ja_notes AS
ON DELETE TO doctrine.ja_notes DO INSTEAD DELETE FROM ja_notes r_1103088 WHERE (r_1103088.id = old.id)
RETURNING r_1103088.id,
r_1103088.mobiuserid,
[etc]

The RETURNING list has to match the column list of the rule's
target table or view (here, doctrine.ja_notes). Maybe you added
some columns to that since this last worked?

regards, tom lane

On 28/02/2022, at 4:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The RETURNING list has to match the column list of the rule's
target table or view (here, doctrine.ja_notes). Maybe you added
some columns to that since this last worked?

You are correct. The view has an extra column.

Thanks!