Formatação de consultas PostgreSQL e PL/pgSQL

Como o SQL Format organiza a sintaxe específica do PostgreSQL, de casts com :: e parâmetros $1 a RETURNING, ON CONFLICT e corpos de funções PL/pgSQL, com a saída real do formatador para cada caso, além de alguns hábitos de nomenclatura que deixam o código PostgreSQL mais fácil de ler.

O que o formatador reconhece

O SQL Format não tem seletor de dialeto: ele formata o SQL que a maioria dos bancos de dados tem em comum. Além disso, ele entende algumas coisas que são específicas do PostgreSQL:

• casts de tipo com ::, como created_at::date;
• operadores de JSON, de array e de padrão como ->, ->>, #>>, @>, <@, && e ~*, além de literais e subscritos de array como ARRAY['beta'] e tags[1];
• parâmetros posicionais $1, $2, usados por prepared statements e pela maioria dos drivers do PostgreSQL;
• corpos entre dollar quotes ($$ … $$) de CREATE FUNCTION, CREATE PROCEDURE e DO, que recebem um layout de bloco PL/pgSQL em vez de serem tratados como uma única string longa.

RETURNING e ON CONFLICT recebem um layout de cláusula próprio, mostrado nas seções abaixo. Para ver como outros bancos de dados diferem, veja diferenças entre dialetos SQL que afetam a formatação.

Casts e parâmetros

Consultas escritas para um driver ou para PREPARE são cheias de placeholders $n e de casts que informam ao PostgreSQL o tipo de cada parâmetro. O formatador mantém os placeholders exatamente como foram escritos e escreve cada cast colado aos seus operandos, como em $2::TIMESTAMPTZ, para que o cast seja lido como uma unidade. O nome do tipo depois dele segue a opção Palavras-chave, então DATE e TIMESTAMPTZ ficam em maiúsculas aqui:

select o.id, o.created_at::date as order_day, o.total::text as total_label, o.status from orders o where o.customer_id = $1 and o.created_at >= $2::timestamptz and o.created_at < $2::timestamptz + interval '1 month' order by o.created_at desc limit $3

SELECT o.id,
    o.created_at::DATE AS order_day,
    o.total::TEXT AS total_label,
    o.status
FROM orders o
WHERE o.customer_id = $1
AND o.created_at >= $2::TIMESTAMPTZ
AND o.created_at < $2::TIMESTAMPTZ + INTERVAL '1 month'
ORDER BY o.created_at DESC
LIMIT $3

A mesma consulta com palavras-chave em minúsculas, estilo que muitos projetos PostgreSQL usam. Os casts acompanham, então ::date e ::timestamptz ficam em minúsculas:

select o.id,
    o.created_at::date as order_day,
    o.total::text as total_label,
    o.status
from orders o
where o.customer_id = $1
and o.created_at >= $2::timestamptz
and o.created_at < $2::timestamptz + interval '1 month'
order by o.created_at desc
limit $3

Os dois estilos são válidos. O que importa é que o projeto use um deles em todo lugar; o guia de estilo de SQL discute como escolher.

Operadores de JSON e de array

Os operadores de vários caracteres do PostgreSQL são mantidos inteiros e escritos com um espaço de cada lado, então payload->>'email' vira payload ->> 'email' e uma cadeia como -> 'address' #>> '{city}' continua legível. Literais e subscritos de array ficam colados aos seus colchetes:

select e.id, e.payload->>'email' as email, e.payload->'address'#>>'{city}' as city, e.tags[1] as first_tag from events e where e.payload @> '{"type": "signup"}' and e.tags && ARRAY['beta'] and e.created_at >= $1::timestamptz

SELECT e.id,
    e.payload ->> 'email' AS email,
    e.payload -> 'address' #>> '{city}' AS city,
    e.tags[1] AS first_tag
FROM events e
WHERE e.payload @> '{"type": "signup"}'
AND e.tags && ARRAY['beta']
AND e.created_at >= $1::TIMESTAMPTZ

O texto JSON dentro do literal de string fica exatamente como foi digitado. ARRAY não está na lista de palavras-chave, então mantém a caixa que você digitou.

RETURNING

O RETURNING devolve valores das linhas afetadas por um INSERT, UPDATE ou DELETE, o que economiza uma segunda ida ao banco. O formatador o trata como uma cláusula: ele começa a sua própria linha, depois do WHERE, com a primeira coluna retornada na mesma linha e as demais em linhas indentadas logo abaixo, como numa lista de GROUP BY. A lista do SET funciona do mesmo jeito, com uma atribuição por linha:

update accounts set balance = balance - $1, updated_at = now() where id = $2 and balance >= $1 returning id, balance

UPDATE accounts
SET balance = balance - $1,
    updated_at = now()
WHERE id = $2
AND balance >= $1
RETURNING id,
    balance

Na revisão, a condição balance >= $1 é a linha que mais importa, e tê-la na sua própria linha, ao lado das outras condições, faz com que seja difícil deixá-la passar.

ON CONFLICT … DO UPDATE

Um upsert tem várias partes que quem revisa precisa conferir separadamente: a lista de colunas, os valores, o alvo do conflito e a atualização. O formatador dá a cada parte o seu próprio lugar, para que cada uma seja fácil de encontrar:

insert into inventory (sku, warehouse_id, quantity) values ($1, $2, $3) on conflict (sku, warehouse_id) do update set quantity = inventory.quantity + excluded.quantity, updated_at = now() returning sku, quantity

INSERT INTO inventory (
    sku,
    warehouse_id,
    quantity
)
VALUES (
    $1,
    $2,
    $3
)
ON CONFLICT (sku, warehouse_id) DO UPDATE
SET quantity = inventory.quantity + excluded.quantity,
    updated_at = now()
RETURNING sku,
    quantity

INSERT INTO inventory ( fica numa linha só, com a lista de colunas indentada logo abaixo, e a lista de VALUES recebe o mesmo tratamento. ON CONFLICT (sku, warehouse_id) DO UPDATE fica numa linha só, para que o alvo do conflito e a ação sejam lidos juntos; DO NOTHING também é mantido nessa linha. SET e RETURNING começam então as suas próprias linhas, com os itens seguintes indentados.

Dois fatos do PostgreSQL que vale a pena ter em mente ao ler upserts: o alvo do conflito precisa corresponder a um índice ou constraint única, e excluded se refere à linha que foi proposta para inserção, enquanto o nome da tabela (inventory, aqui) se refere à linha já armazenada.

Corpos de funções

Para o parser SQL, o corpo de uma função PL/pgSQL é uma string literal, e é por isso que ele vem entre dollar quotes. Por esse motivo, muitas ferramentas o deixam numa linha só. O SQL Format, em vez disso, o formata como PL/pgSQL: o cabeçalho do CREATE FUNCTION fica numa linha até o AS $$, depois DECLARE, BEGIN e END começam na margem esquerda e cada comando é indentado um nível. IF, ELSIF, ELSE e END IF ficam alinhados, com os seus comandos indentados um nível a mais. Os comandos SQL dentro do corpo recebem o layout de cláusulas de sempre, e o $$; de fechamento fica na sua própria linha:

create or replace function close_stale_orders(p_days integer) returns integer language plpgsql as $$ declare v_count integer; begin if p_days < 1 then raise exception 'p_days must be positive, got %', p_days; elsif p_days > 365 then raise notice 'closing orders older than one year'; end if; update orders set status = 'closed' where status = 'open' and created_at < now() - p_days * interval '1 day'; get diagnostics v_count = row_count; return v_count; end; $$;

CREATE OR REPLACE FUNCTION close_stale_orders(p_days INTEGER) RETURNS INTEGER LANGUAGE plpgsql AS $$
DECLARE
    v_count INTEGER;
BEGIN
    IF p_days < 1 THEN
        RAISE EXCEPTION 'p_days must be positive, got %', p_days;
    ELSIF p_days > 365 THEN
        RAISE NOTICE 'closing orders older than one year';
    END IF;
    UPDATE orders
    SET status = 'closed'
    WHERE status = 'open'
    AND created_at < now() - p_days * INTERVAL '1 day';
    GET DIAGNOSTICS v_count = row_count;
    RETURN v_count;
END;
$$;

A opção Palavras-chave também vale dentro do corpo. Palavras como plpgsql e row_count não estão na lista de palavras-chave e ficam como foram digitadas, e a opção Palavras-chave nunca mexe em strings literais, como as mensagens do RAISE.

Blocos DO, loops e tratadores de exceção

Blocos DO anônimos são comuns em migrations e scripts de manutenção. Eles recebem o mesmo layout. O cabeçalho de um FOR … IN … LOOP fica numa linha, o corpo do loop é indentado e o END LOOP fica alinhado com o FOR. Um bloco BEGIN … EXCEPTION … END aninhado é indentado como uma unidade, com cada tratador WHEN um nível abaixo do EXCEPTION e os seus comandos um nível a mais. Aqui, com palavras-chave em minúsculas:

do $$ declare r record; begin for r in select id from tenants where enabled loop begin perform refresh_tenant_stats(r.id); exception when others then raise warning 'tenant % failed: %', r.id, sqlerrm; end; end loop; end $$;

do $$
declare
    r record;
begin
    for r in select id from tenants where enabled loop
        begin
            perform refresh_tenant_stats(r.id);
        exception
            when others then
                raise warning 'tenant % failed: %', r.id, sqlerrm;
        end;
    end loop;
end
$$;

Esse aninhamento é exatamente o que você precisa enxergar ao revisar o tratamento de erros: quais comandos estão protegidos pelo tratador e quais não estão. Com a indentação padrão de quatro espaços, cada nível é fácil de acompanhar; se um aninhamento profundo empurrar o código muito para a direita, dois espaços é uma escolha comum para PL/pgSQL.

Prefira identificadores em snake_case minúsculo

O PostgreSQL converte identificadores sem aspas para minúsculas. CREATE TABLE CustomerOrders cria uma tabela chamada customerorders, e SELECT * FROM CUSTOMERORDERS a encontra. Só as aspas duplas preservam a caixa, e, uma vez que um nome é criado com aspas e maiúsculas, toda consulta precisa colocá-lo entre aspas exatamente do mesmo jeito, para sempre:

CREATE TABLE "CustomerOrders" ("OrderId" bigint, "createdAt" timestamptz);
-- every later query must repeat the quotes exactly:
SELECT "OrderId", "createdAt" FROM "CustomerOrders";
-- this fails: unquoted names are folded to customerorders / orderid
SELECT OrderId FROM CustomerOrders;

-- snake_case needs no quotes and reads the same in any case
CREATE TABLE customer_orders (order_id bigint, created_at timestamptz);
SELECT order_id, created_at FROM customer_orders;

O snake_case em minúsculas evita o problema por completo: os nomes ficam iguais em SQL com palavras-chave maiúsculas ou minúsculas, não precisam de aspas nas consultas e sobrevivem a uma mudança da opção Palavras-chave no formatador ou num ORM. O mesmo vale para nomes de colunas gerados a partir de código em camelCase; faça o mapeamento na aplicação em vez de usar aspas no banco de dados. Evite também palavras reservadas como user ou order como nomes, já que elas também exigem aspas.

text ou varchar?

No PostgreSQL, text e varchar são armazenados e tratados da mesma forma; varchar(n) só acrescenta uma verificação de tamanho máximo. Então a escolha diz respeito principalmente ao que a declaração comunica a quem lê depois. Uma convenção útil:

• use text quando não houver um limite real de negócio, em vez de um varchar(255) arbitrário que sugere uma regra que ninguém definiu;
• quando um limite ou formato for uma regra real, expresse-o como uma constraint CHECK nomeada, que documenta a intenção e aparece pelo nome nas mensagens de erro;
• evite char(n) para dados de tamanho variável, porque ele completa os valores com espaços.

CREATE TABLE customers (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        text NOT NULL,
    -- the business rule lives in a named constraint, not in a type length
    country     text NOT NULL CONSTRAINT ck_customers_country_iso2
                    CHECK (char_length(country) = 2),
    notes       text
);

Times que precisam de portabilidade para bancos de dados em que varchar(n) é obrigatório podem padronizar nele; o que mais beneficia quem lê é a consistência dentro de um esquema. Para mais sobre como escrever alterações de esquema, veja o checklist de formatação de migrations.

Perguntas frequentes