Subconsultas, campos autoincrementales y gestión de privilegios en PostgreSQL

Las subconsultas, o consultas anidadas, permiten integrar sentencias SELECT dentro de la cláusula WHERE de otra consulta. Los resultados de una consulta interna pueden servir como valores de entrada para la consulta externa. Este mecanismo es compatible con las sentencias SELECT, INSERT, UPDATE y DELETE, pudiendo utilizar operadores de comparación y pertenencia.

Existen reglas clave para su implementación:

La subconsulta debe encerrarse entre paréntesis.
Dentro de una cláusula SELECT, la subconsulta típicamente devuelve una sola columna, a menos que la comparación externa involucre múltiples columnas.
La cláusula ORDER BY no está permitida dentro de la subconsulta; sin embargo, sí puede emplearse en la consulta principal.
Si la subconsulta devuelve múltiples filas, solo puede combinarse con operadores que soporten conjuntos, como IN.
El operador BETWEEN no puede aplicarse directamente a una subconsulta, aunque sí puede usarse dentro de ella.

Uso en sentencias SELECT

Un patrón habitual combina una subconsulta en la condición WHERE para filtrar resultados.

SELECT nombre_cliente, ciudad
FROM clientes
WHERE codigo_ciudad IN (
    SELECT codigo
    FROM ciudades
    WHERE poblacion > 500000
);

Uso en sentencias INSERT

Los datos obtenidos de una subconsulta pueden incertarse directamente en otra tabla, permitiendo transformaciones con funciones.

INSERT INTO registro_empleados (id_empleado, nombre_completo)
SELECT id, CONCAT(primer_nombre, ' ', apellido)
FROM empleados
WHERE departamento = 'Ventas';

Uso en sentencias UPDATE

Una subconsulta puede determinar el nuevo valor de una columna o identificar qué filas actualizar.

UPDATE inventario
SET estado = 'Reservado'
WHERE producto_id IN (
    SELECT producto_id
    FROM pedidos
    WHERE estado_pedido = 'Pendiente'
);

Uso en sentencias DELETE

La eliminación condicional de registros puede basarse en valores devueltos por una subconsulta.

DELETE FROM sesiones
WHERE usuario_id IN (
    SELECT id
    FROM usuarios
    WHERE fecha_ultimo_acceso < '2023-01-01'
);

Campos con autoincremento

El mecanismo de autoincremento genera valores únicos y consecutivos automáticamente al insertar nuevos registros. PostgreSQL implementa esto mediante objetos de secuencia, asociados a los tipos de datos SMALLSERIAL, SERIAL y BIGSERIAL.

A diferencia de bases de datos como MySQL, que utilizan la palabra reservada AUTO_INCREMENT, PostgreSQL emplea tipos de datos específicos que internamente gestionan una secuencia asociada.

CREATE TABLE productos (
    id_producto SERIAL PRIMARY KEY,
    descripcion TEXT NOT NULL,
    precio NUMERIC(10, 2) CHECK (precio > 0)
);

Al insertar registros sin especificar el campo SERIAL, este genera automáticamente un valor único.

INSERT INTO productos (descripcion, precio) VALUES ('Teclado mecánico', 89.99);
INSERT INTO productos (descripcion, precio) VALUES ('Monitor 24 pulgadas', 249.95);

SELECT * FROM productos;

El resultado mostrará valores de id_producto autoasignados (1, 2, etc.).

Ejemplo práctico con una tabla CLIENTE

CREATE TABLE CLIENTE (
    ID_CLIENTE  SERIAL      PRIMARY KEY,
    NOMBRE      VARCHAR(50) NOT NULL,
    EDAD        INTEGER     NOT NULL,
    DIRECCION   VARCHAR(80),
    CREDITO     NUMERIC(12,2) DEFAULT 0
);

INSERT INTO CLIENTE (NOMBRE, EDAD, DIRECCION, CREDITO) VALUES ('Laura', 28, 'Calle Sol 45', 1500.50);
INSERT INTO CLIENTE (NOMBRE, EDAD, DIRECCION, CREDITO) VALUES ('Carlos', 35, 'Avenida Luna 12', 3200.00);
INSERT INTO CLIENTE (NOMBRE, EDAD, DIRECCION, CREDITO) VALUES ('Sofía', 42, 'Plaza Estrella 7', 2800.75);

Sistema de privilegios (PRIVILEGES)

Cada objeto de base de datos en PostgreSQL posee un propietario, usualmente el rol que lo creó. Por defecto, solo el propietario (o un superusuario) puede modificarlo o eliminarlo. Para permitir la colaboración, se deben conceder permisos explícitos a otros roles o usuarios.

Los privilegios disponibles se aplican según el tipo de objeto:

Para tablas y vistas: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER.
Para bases de datos: CREATE, CONNECT, TEMPORARY.
Para funciones: EXECUTE.
Para esquemas: CREATE, USAGE.

Asignación de privilegios con GRANT

Se utiliza el comando GRANT para otorgar permisos.

GRANT SELECT, INSERT ON TABLE inventario TO rol_ventas;
GRANT ALL PRIVILEGES ON TABLE pedidos TO usuario_admin;
GRANT USAGE ON SCHEMA tienda TO PUBLIC;

Revocación de privilegios con REVOKE

Para eliminar permisos concedidos previamente.

REVOKE DELETE ON TABLE clientes FROM rol_temporal;
REVOKE ALL ON FUNCTION calcular_iva() FROM PUBLIC;

Eliminación de roles de usuario

Un rol puede eliminarse si no es dueño de objetos o posee privilegios asignados. Primero se deben transferir la propiedad y revocar permisos.

REASSIGN OWNED BY usuario_antiguo TO nuevo_propietario;
DROP OWNED BY usuario_antiguo;
DROP ROLE usuario_antiguo;

Etiquetas: PostgreSQL Subconsultas serial GRANT REVOKE

Publicado el 7-13 13:28

Friki Work