Re: Duda de SQL.

Arturo Espinosa escribió:
> Hola amigos. Soy Arturo Espinosa, de México.

¡Hola Arturo! Supongo que eres el mismo Arturo Espinosa que estuvo como
charlista en un Encuentro de Linux en Concepción, Chile, hace más de una
década. ¡Gusto saludarte!

> En segunda instancia, tengo una duda de SQL sobre Postgres, espero que me
> puedan ayudar. He encontrado una solución a mi problema, pero no sé si hay
> alguna manera más elegante de resolverlo. El mensaje se alarga un poco a
> partir de aquí, pero he querido incluirles todo el material necesario para
> que se les facilite emitir su opinión, que agradezco mucho.

El código de la estructura que enviaste no funcionaba por falta de
algunos detalles menores; yo agregué lo siguiente:

create type charp_account_status as enum ('stimulated', 'satisfied');
create type imr_record_status as enum ('clobbered', 'decimated');
create type imr_gender as enum ('martian', 'selenite');
create type imr_persona_type as enum ('dual-headed', 'triple-breasted');
create type imr_account_type as enum ('illegal', 'money laundering');

create sequence persona_persona_id_seq;
create sequence file_file_id_seq;

Y con eso ya puedo ejecutar tus CREATE TABLE. A continuación generé
unos pocos datos de ejemplo, para probar las consultas. Usé lo
siguiente:

-- genera 100.000 personas
INSERT INTO persona (persona_id, name, type, p_status, inst_id)
SELECT g, 'persona ' || g,
(CASE g % 2 WHEN 0 THEN 'dual-headed' ELSE 'triple-breasted' END)::imr_persona_type,
'clobbered', 1
FROM generate_series(1, 100000) g;

-- genera 500.000 archivos
INSERT INTO file (file_id, fname, created, mime_type_id, inst_id)
SELECT g, 'file ' || g,
now() + (random() * 1000) * interval '1 day',
1, 1
FROM generate_series(1, 500000) g;

-- asocia algunos de esos 500.000 archivos con algunas de esas 100.000
-- personas, al azar
insert into persona_photo (persona_id, file_id, inst_id)
SELECT least(greatest((g + (random() * 10) - 5)::int % 100000, 1), 100000),
g, 1
FROM generate_series(1, 500000) g;

En este punto me di cuenta que las tablas account etc no tenían mucha
importancia para la parte SQL del puzle, así que no les puse más
atención.

Luego probé tu consulta:

> SELECT
> persona_id,
> inst_id,
> fname
> FROM
> (SELECT
> persona_id,
> inst_id,
> MAX(created)
> FROM
> persona_photo
> NATURAL JOIN file
> GROUP BY
> inst_id,
> persona_id
> ) AS q
> NATURAL JOIN file;

Lo primero que hice fue agregar un WHERE persona_id = X en el select más
externo. Acá lo que sucedió fue que obtuve un resultado de 500.000
registros, que no era lo que yo esperaba en absoluto; así que decidí
ignorar tu consulta y empezar desde cero.

La primera recomendación es olvidarte totalmente del NATURAL JOIN. En
opinión de varios, el NATURAL es un constructo un poco peligroso, porque
a poco andar puede resultar que si agregas alguna columna a alguna tabla
la consulta mágicamente empieza a dar resultados totalmente erróneos
porque algún NATURAL considera columnas que antes no eran parte de la
condición de join. Es mejor usar ON (c1 = c2) o por último USING (c),
donde debes enumerar explícitamente los campos que se usan en el join,
con lo cual el peligro ya no existe.

A mí me parece que la forma más simple es partir buscando las fotos de
la persona, y traer la última:

SELECT persona_id, created
FROM persona_photo JOIN file USING (file_id)
WHERE persona_id = 48281
ORDER BY created DESC
LIMIT 1

Luego puedes hacer un CTE con esta consulta, es decir meterlo en una
cláusula WITH, y hacer un LEFT JOIN con la tabla persona:

WITH photos_of_a_persona AS (
SELECT persona_id, created
FROM persona_photo JOIN file USING (file_id)
WHERE persona_id = 48282
ORDER BY created DESC LIMIT 1)
SELECT *
FROM persona LEFT JOIN photos_of_a_persona USING (persona_id)
WHERE persona_id = 48282;

Para meter eso en plpgsql debería ser cosa de simplemente reemplazar el
ID de persona con el $1 de la función.

A continuación borré las fotos del individuo 48282 para asegurarme que
funciona cuando alguien no tiene fotos -- y todo parece andar bien.

Saludos y éxito

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda(at)postgresql(dot)org)
Para cambiar tu suscripción:
http://www.postgresql.org/mailpref/pgsql-es-ayuda