From: | Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Slick way to update multiple tables. |
Date: | 2021-04-01 19:37:06 |
Message-ID: | 2662957f-36c0-4e02-6be9-f7bf52729309@illuminatedcomputing.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 4/1/21 11:54 AM, Michael Lewis wrote:
> postgresql.org/docs/current/sql-createview.html
> <http://postgresql.org/docs/current/sql-createview.html>
>
> My apologies. It seems INSTEAD OF triggers are required to implement
> updates across multiple tables. I thought not if all were simple joins.
> My mistake.
Even with INSTEAD OF triggers, if you use a view then I suppose you
would be forced to update some of the records more often that necessary?
(Unless your tables are 1-to-1-to-1 of course.) Or if there is some
trick to avoid that I'd be curious to know about it.
Here is something I've done in the past:
WITH
update1(ok) AS (
UPDATE foo SET ... WHERE ...
RETURNING 'ok'
),
update2(ok) AS (
UPDATE bar SET ... WHERE ...
RETURNING 'ok'
),
update3(ok) AS (
UPDATE baz SET ... WHERE ...
RETURNING 'ok'
)
SELECT ok FROM update1
UNION ALL
SELECT ok FROM update2
UNION ALL
SELECT ok FROM update3
;
You could even select different messages from each update if you want to
know how many rows you touched in each table.
--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2021-04-01 19:39:29 | Re: Is replacing transactions with CTE a good idea? |
Previous Message | Michael Lewis | 2021-04-01 18:54:03 | Re: Slick way to update multiple tables. |