Found cool trick how today implement Orable MERGE in PostgreSQL:

Oracle statement:

 

  1. MERGE INTO acme_obj_value d
  2. USING ( SELECT object_id
  3.         FROM   acme_state_tmp
  4.       ) s
  5. ON (d.object_id = s.object_id)
  6.   WHEN matched THEN
  7.     UPDATE SET d.date_value = LEAST(l_dt, d.date_value)
  8.   WHEN NOT matched THEN
  9.     INSERT (d.id, d.object_id, d.date_value)
  10.     VALUES (acme_param_sequence.NEXTVAL, s.object_id, l_dt)

PostgreSQL statement:

 

  1. WITH s AS (
  2.      SELECT object_id
  3.      FROM   acme_state_tmp
  4. ),
  5. upd AS (
  6.      UPDATE acme_obj_value
  7.      SET    date_value = LEAST(l_dt, d.date_value)
  8.      FROM   s
  9.      WHERE  acme_obj_value.object_id = s.object_id
  10.      RETURNING acme_obj_value.object_id
  11. )
  12. INSERT INTO acme_obj_value(id, object_id,  date_value)
  13. SELECT NEXTVAL(‘acme_param_sequence’), s.object_id, l_dt
  14. FROM   s
  15. WHERE  s.object_id NOT IN (SELECT object_id FROM upd)

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s