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

>
> On 28/02/2022, at 3:44 PM, Lucas <root(at)sud0(dot)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;

> ————————————————————————————————————
> ERROR: RETURNING list has too few entries
> ————————————————————————————————————
>
> The same syntax works on PG 9.x (previous version of the DB). Any suggestions on a fix, please?
>
> Thanks!
>
> [1] https://www.postgresql.org/docs/current/sql-createrule.html