Formato de consultas PostgreSQL y PL/pgSQL

Cómo organiza SQL Format la sintaxis propia de PostgreSQL, desde los casts con :: y los parámetros $1 hasta RETURNING, ON CONFLICT y los cuerpos de funciones PL/pgSQL, con la salida real del formateador para cada caso, además de algunos hábitos de nomenclatura que hacen que el código PostgreSQL sea más fácil de leer.

Qué reconoce el formateador

SQL Format no tiene un selector de dialecto: formatea el SQL que la mayoría de las bases de datos tienen en común. Además de eso, entiende algunos elementos propios de PostgreSQL:

• los casts de tipo con ::, como created_at::date;
• los operadores de JSON, de arrays y de patrones, como ->, ->>, #>>, @>, <@, && y ~*, además de los literales de array y los subíndices como ARRAY['beta'] y tags[1];
• los parámetros posicionales $1, $2, que usan los prepared statements y la mayoría de los drivers de PostgreSQL;
• los cuerpos entre dólares ($$ … $$) de CREATE FUNCTION, CREATE PROCEDURE y DO, que reciben un diseño de bloque PL/pgSQL en lugar de tratarse como una sola cadena larga.

RETURNING y ON CONFLICT reciben un diseño de cláusula propio, que se muestra en las secciones de abajo. Para ver en qué se diferencian otras bases de datos, consulta diferencias entre dialectos SQL que afectan el formato.

Casts y parámetros

Las consultas escritas para un driver o para PREPARE están llenas de marcadores $n y de casts que le indican a PostgreSQL el tipo de cada parámetro. El formateador conserva los marcadores exactamente como se escribieron y escribe cada cast pegado a sus operandos, como en $2::TIMESTAMPTZ, para que un cast se lea como una unidad. El nombre del tipo que sigue respeta la opción Palabras clave, así que aquí DATE y TIMESTAMPTZ quedan en mayú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

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

La misma consulta con palabras clave en minúsculas, un estilo que usan muchos proyectos PostgreSQL. Los casts acompañan el cambio, así que ::date y ::timestamptz quedan en 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

Los dos estilos están bien. Lo que importa es que el proyecto use uno de ellos en todas partes; la guía de estilo de SQL explica cómo elegir.

Operadores de JSON y de arrays

Los operadores de varios caracteres de PostgreSQL se mantienen intactos y se escriben con un espacio a cada lado, así que payload->>'email' se convierte en payload ->> 'email' y una cadena como -> 'address' #>> '{city}' sigue siendo legible. Los literales de array y los subíndices quedan pegados a sus corchetes:

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

El texto JSON dentro del literal de cadena se deja exactamente como se escribió. ARRAY no está en la lista de palabras clave, así que conserva las mayúsculas y minúsculas que escribiste.

RETURNING

RETURNING devuelve valores de las filas afectadas por un INSERT, UPDATE o DELETE, lo que ahorra un segundo viaje de ida y vuelta a la base de datos. El formateador lo trata como una cláusula: empieza en su propia línea, después del WHERE, con la primera columna devuelta en la misma línea y las demás en líneas con sangría debajo, como una lista de GROUP BY. La lista del SET funciona de la misma manera, con una asignación por línea:

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

En la revisión, la condición balance >= $1 es la línea que más importa, y tenerla en su propia línea junto a las demás condiciones hace que sea difícil pasarla por alto.

ON CONFLICT … DO UPDATE

Un upsert tiene varias partes que los revisores deben verificar por separado: la lista de columnas, los valores, el destino del conflicto y la actualización. El formateador le da a cada parte su propio lugar, para que cada una sea 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 ( se queda en una sola línea, con la lista de columnas con sangría debajo, y la lista de VALUES recibe el mismo tratamiento. ON CONFLICT (sku, warehouse_id) DO UPDATE se queda en una sola línea, para que el destino del conflicto y la acción se lean juntos; DO NOTHING se mantiene en esa línea de la misma forma. Luego SET y RETURNING empiezan en sus propias líneas, con sus elementos de continuación con sangría.

Dos hechos de PostgreSQL que conviene tener en cuenta al leer upserts: el destino del conflicto debe coincidir con un índice o una restricción única, y excluded se refiere a la fila que se propuso insertar, mientras que el nombre de la tabla (inventory en este caso) se refiere a la fila que ya está almacenada.

Cuerpos de funciones

Para el parser de SQL, el cuerpo de una función PL/pgSQL es un literal de cadena; por eso va entre dólares. Por esa razón, muchas herramientas lo dejan en una sola línea. SQL Format, en cambio, lo formatea como PL/pgSQL: el encabezado CREATE FUNCTION se queda en una sola línea hasta AS $$, luego DECLARE, BEGIN y END empiezan en el margen izquierdo y cada sentencia lleva un nivel de sangría. IF, ELSIF, ELSE y END IF quedan alineados, con sus sentencias un nivel más adentro. Las sentencias SQL dentro del cuerpo reciben el diseño habitual de cláusulas, y el $$; de cierre va en su propia línea:

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;
$$;

La opción Palabras clave también se aplica dentro del cuerpo. Palabras como plpgsql y row_count no están en la lista de palabras clave y quedan tal como se escribieron, y esta opción nunca toca los literales de cadena, como los mensajes de RAISE.

Bloques DO, loops y manejadores de excepciones

Los bloques DO anónimos son comunes en migraciones y scripts de mantenimiento. Reciben el mismo diseño. El encabezado de un FOR … IN … LOOP se queda en una sola línea, el cuerpo del loop lleva sangría y END LOOP queda alineado con FOR. Un bloque BEGIN … EXCEPTION … END anidado se sangra como una unidad, con cada manejador WHEN un nivel debajo de EXCEPTION y sus sentencias un nivel más adentro. Aquí con palabras clave en 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
$$;

Ese anidamiento es justo lo que necesitas ver al revisar el manejo de errores: qué sentencias están protegidas por el manejador y cuáles no. Con la sangría predeterminada de cuatro espacios, cada nivel es fácil de seguir; si un anidamiento profundo empuja el código demasiado a la derecha, dos espacios es una elección común para PL/pgSQL.

Prefiere identificadores en snake_case y minúsculas

PostgreSQL convierte a minúsculas los identificadores sin comillas. CREATE TABLE CustomerOrders crea una tabla llamada customerorders, y SELECT * FROM CUSTOMERORDERS la encuentra. Solo las comillas dobles conservan las mayúsculas, y una vez que un nombre se crea con comillas y mayúsculas, todas las consultas deben ponerlo entre comillas exactamente de la misma manera, para siempre:

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;

El snake_case en minúsculas evita el problema por completo: los nombres se leen igual en SQL escrito en mayúsculas o en minúsculas, no necesitan comillas en las consultas y sobreviven a un cambio de la opción Palabras clave en el formateador o en un ORM. Lo mismo vale para los nombres de columna generados a partir de código en camelCase: mapéalos en la aplicación en lugar de ponerlos entre comillas en la base de datos. Evita también usar palabras reservadas como user u order como nombres, ya que también necesitan comillas.

¿text o varchar?

En PostgreSQL, text y varchar se almacenan y se manejan de la misma manera; varchar(n) solo agrega una verificación de longitud máxima. Así que la elección tiene que ver sobre todo con lo que la declaración le comunica al próximo lector. Una convención útil:

• usa text cuando no exista un límite real de negocio, en lugar de un varchar(255) arbitrario que sugiere una regla que nadie decidió;
• cuando un límite o un formato sea una regla real, exprésalo como una restricción CHECK con nombre, que documenta la intención y aparece por su nombre en los mensajes de error;
• evita char(n) para datos de longitud variable, porque rellena los valores con espacios.

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
);

Los equipos que necesitan portabilidad hacia bases de datos donde varchar(n) es obligatorio pueden estandarizar en ese tipo; lo que más beneficia a los lectores es la consistencia dentro de un esquema. Para saber más sobre cómo escribir cambios de esquema, consulta la lista de verificación de formato para migraciones.

Preguntas frecuentes