Thread: 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
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!