Executando UPSERT (UPDATE ou INSERT) com PostgreSQL e PHP

Essa é uma situação típica. Imagine que você tem uma tabela

CREATE TABLE PUBLIC.TBUPSERTEXAMPLE
(
  KEY1 CHARACTER VARYING(10) NOT NULL,
  KEY2 CHARACTER VARYING(14) NOT NULL,
  KEY3 CHARACTER VARYING(14) NOT NULL,
  KEY4 CHARACTER VARYING(14) NOT NULL,
 
  VALUE1 CHARACTER VARYING(20),
  VALUE2 CHARACTER VARYING(20) NOT NULL,
  VALUE3 CHARACTER VARYING(100),
  VALUE4 CHARACTER VARYING(400),
  VALUE5 CHARACTER VARYING(20),
 
  CONSTRAINT TBUPSERTEXAMPLE_PKEY PRIMARY KEY (KEY1, KEY2, KEY3, KEY4)
)

E você precisa atualizar um registro. Você pode executar uma simples declaração UPDATE, mas o que acontece na primeira vez?

Você não pode atualizar o registro, basicamente porque o registro não existe. Ao invés disso, você precisa criar uma instrução INSERT. Podemos fazer isso seguindo caminhos diferentes. Você pode criar primeiro uma instrução SELECT e, se o registro existir, executar um UPDATE. Se ele não existir, você pode fazer um INSERT. Nós também podemos executar um UPDATE e ver quantos registros são afetados. Se nenhum dos registros é afetado, então você pode fazer um INSERT. Finalmente, podemos executar um INSERT e isso lançará um erro, então fazemos um UPDATE.

Todas essas técnicas funcionam de uma maneira ou de outra, mas o PostgreSQL nos dá uma maneira legal de fazer essa operação com uma sentença SQL. Podemos usar CTE (expressão de tabela comum) e executar algo parecido com isto:

WITH upsert AS (
    UPDATE PUBLIC.TBUPSERTEXAMPLE
    SET
        VALUE1 = :VALUE1,
        VALUE2 = :VALUE2,
        VALUE3 = :VALUE3,
        VALUE4 = :VALUE4,
        VALUE5 = :VALUE5
    WHERE
        KEY1 = :KEY1 AND
        KEY2 = :KEY2 AND
        KEY3 = :KEY3 AND
        KEY4 = :KEY4
    RETURNING *
)
INSERT INTO PUBLIC.TBUPSERTEXAMPLE(KEY1, KEY2, KEY3, KEY4, VALUE1, VALUE2, VALUE3, VALUE4, VALUE5)
SELECT
    :KEY1, :KEY2, :KEY3, :KEY4, :VALUE1, :VALUE2, :VALUE3, :VALUE4, :VALUE5
WHERE
    NOT EXISTS (SELECT 1 FROM upsert);

Desde o PostgreSQL 9.5, também podemos utilizar outra técnica para fazer essas operações Upsert. Nós podemos fazer algo como isto:

INSERT INTO PUBLIC.TBUPSERTEXAMPLE (key1, key2, key3, key4, value1, value2, value3, value4, value5)
  VALUES ('key2', 'key2', 'key3', 'key4', 'value1',  'value2',  'value3',  'value4',  'value5')
ON CONFLICT (key1, key2, key3, key4)
DO UPDATE SET
  value1 = 'value1', 
  value2 = 'value2', 
  value3 = 'value3', 
  value4 = 'value4', 
  value5 = 'value5'
WHERE
  TBUPSERTEXAMPLE.key1 = 'key2' AND
  TBUPSERTEXAMPLE.key2 = 'key2' AND
  TBUPSERTEXAMPLE.key3 = 'key3' AND
  TBUPSERTEXAMPLE.key4 = 'key4';

Para me ajudar a escrever essa frase, eu criei um wrapper PHP simples.

Aqui um exemplo utilizando PDO

use GSqlUtilsUpsert;
 
$conn = new PDO('pgsql:dbname=gonzalo;host=localhost', 'username', 'password');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
$conn->beginTransaction();
try {
    Upsert::createFromPDO($conn)->exec('PUBLIC.TBUPSERTEXAMPLE', [
        'KEY1' => 'key1',
        'KEY2' => 'key2',
        'KEY3' => 'key3',
        'KEY4' => 'key4',
    ], [
        'VALUE1' => 'value1',
        'VALUE2' => 'value2',
        'VALUE3' => 'value3',
        'VALUE4' => 'value4',
        'VALUE5' => 'value5',
    ]);
    $conn->commit();
} catch (Exception $e) {
    $conn->rollback();
    throw $e;
}

E outro usando DBAL

use DoctrineDBALDriverManager;
use GSqlUtilsUpsert;
 
$connectionParams = [
    'dbname'   => 'gonzalo',
    'user'     => 'username',
    'password' => 'password',
    'host'     => 'localhost',
    'driver'   => 'pdo_pgsql',
];
 
$dbh = DriverManager::getConnection($connectionParams);
$dbh->transactional(function ($conn) {
    Upsert::createFromDBAL($conn)->exec('PUBLIC.TBUPSERTEXAMPLE', [
        'KEY1' => 'key1',
        'KEY2' => 'key2',
        'KEY3' => 'key3',
        'KEY4' => 'key4',
    ], [
        'VALUE1' => 'value1',
        'VALUE2' => 'value2',
        'VALUE3' => 'value3',
        'VALUE4' => null,
        'VALUE5' => 'value5',
    ]);
});

E isso é tudo. A biblioteca está disponível no meu GitHub e também em packagist.

***

Gonzalo Ayuso faz parte do time de colunistas internacionais do iMasters. A tradução do artigo é feita pela redação iMasters, com autorização do autor, e você pode acompanhar o artigo em inglês no link: https://gonzalo123.com/.

Powered by WPeMatico