Обсуждение: PG 14 Create Rule ERROR - RETURNING list has too few entries

Hi,

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

————————————————————————————————————
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

>
> 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
thelegacy 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

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
thelegacy 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