Search Postgresql Archives

Re: Slick way to update multiple tables.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxxxxxxxxxxx





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux