Re: Duda de SQL.

Excelente, gracias.

2015-01-21 14:01 GMT-06:00 jvenegasperu . <jvenegasperu(at)gmail(dot)com>:

> Hola Arturo
>
> puedes hacerte de estos libros te sorprendera todo lo que se puede lograr
> con postgres
>
>
> http://www.amazon.com/PostgreSQL-Admin-Cookbook-Simon-Riggs/dp/1849510288#reader_1849510288
>
> http://www.amazon.com/PostgreSQL-9-High-Availability-Cookbook/dp/1849516960/ref=sr_1_7?s=books&ie=UTF8&qid=1421870303&sr=1-7&keywords=postgresql#reader_1849516960
>
> saludos
>
>
>
> El 21 de enero de 2015, 12:33, Arturo Espinosa <arturoea(at)gmail(dot)com>
> escribió:
>
> Hola amigos. Soy Arturo Espinosa, de México.
>>
>> Primeramente, me gustaría recibir recomendaciones de su parte de libros,
>> en inglés o español, para aprender SQL y de preferencia con Postgres. Yo sé
>> algo de SQL, pero cuando las cosas se complican sólo un poco, empiezo a
>> flaquear.
>>
>> 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.
>>
>> En una parte de una base de datos que estoy desarrollando tengo tres
>> relaciones para representar personas con fotografía. Cada persona puede
>> tener una cantidad variable de fotografías, y cada fotografía es un archivo
>> que tiene asociado una fecha de creación. Aquí las partes relevantes de la
>> estructura (he omitido enumeraciones, secuencias y foreign key constraints):
>>
>> CREATE TABLE public.persona (
>> persona_id INTEGER NOT NULL DEFAULT
>> nextval('public.persona_persona_id_seq'),
>> inst_id INTEGER NOT NULL,
>> type imr_persona_type NOT NULL,
>> prefix VARCHAR,
>> name VARCHAR NOT NULL,
>> paterno VARCHAR,
>> materno VARCHAR,
>> gender imr_gender,
>> remarks VARCHAR,
>> p_status imr_record_status NOT NULL,
>> CONSTRAINT persona_pk PRIMARY KEY (persona_id, inst_id)
>> );
>>
>> CREATE TABLE public.persona_photo (
>> persona_id INTEGER NOT NULL,
>> file_id INTEGER NOT NULL,
>> inst_id INTEGER NOT NULL,
>> CONSTRAINT persona_photo_pk PRIMARY KEY (persona_id,
>> file_id, inst_id)
>> );
>>
>> CREATE TABLE public.file (
>> file_id INTEGER NOT NULL DEFAULT
>> nextval('public.file_file_id_seq'),
>> inst_id INTEGER NOT NULL,
>> fname VARCHAR NOT NULL,
>> created TIMESTAMP NOT NULL,
>> mime_type_id INTEGER NOT NULL,
>> CONSTRAINT file_pk PRIMARY KEY (file_id, inst_id)
>> );
>>
>> CREATE TABLE public.account (
>> persona_id INTEGER NOT NULL,
>> inst_id INTEGER NOT NULL,
>> username VARCHAR(20) NOT NULL,
>> passwd VARCHAR(32) NOT NULL,
>> account_type imr_account_type NOT NULL,
>> status charp_account_status NOT NULL,
>> CONSTRAINT account_pk PRIMARY KEY (persona_id, inst_id)
>> );
>>
>> account es un subtipo de persona (relación uno-a-cero-o-uno).
>>
>> Ahora, tengo un stored procedure para listar a los usuarios del sistema.
>> La idea es que de entre los resultados se reporte el nombre del archivo de
>> su fotografía (file.fname). Como cada persona puede tener varias
>> fotografías asociadas, se toma la de más reciente creación. También, he
>> usado un LEFT JOIN para que el campo "picture" de un usuario dado sea NULL
>> en caso de que no tenga ninguna fotografía asociada.
>>
>> Aquí el query que preparé interactivamente con psql antes de meterlo en
>> el resto de mi stored procedure:
>>
>> 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;
>>
>> Aquí el código de mi stored procedure:
>>
>> --- Dado un usurio del sistema identificado por _uid, obtener la lista de
>> usuarios pertenecientes a su instancia cuyo
>> --- status no sea DELETED. Se devuelven el id del usuario, tipo de
>> cuenta, username, nombre opcional del archivo que
>> --- representa su fotografía más reciente, anotaciones y datos personales.
>>
>> CREATE FUNCTION rp_user_list_get(_uid charp_user_id)
>> RETURNS TABLE( persona_id integer, type imr_account_type, username
>> varchar, picture varchar, remarks varchar,
>> prefix varchar, name varchar, paterno varchar,
>> materno varchar, status charp_account_status,
>> gender imr_gender ) AS
>> $BODY$
>>
>> SELECT a.persona_id, a.account_type, a.username, f.fname, p.remarks,
>> p.prefix, p.name, p.paterno, p.materno, a.status, p.gender
>> FROM account AS a1
>> JOIN account AS a USING (inst_id)
>> JOIN persona AS p ON (a.persona_id = p.persona_id)
>> LEFT JOIN (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) AS f
>> ON (a.inst_id = f.inst_id AND a.persona_id =
>> f.persona_id)
>> WHERE a1.persona_id = $1 AND a.status <> 'DELETED';
>>
>> $BODY$
>> LANGUAGE sql STABLE;
>>
>> charp_user_id es un domain a tipo integer simplemente. El argumento _uid
>> que recibe la función es el account_id del usuario que está pidiendo la
>> lista de usuarios desde el frontend. La base de datos está diseñada para
>> soportar varias instancias del negocio a nivel de datos (algo muy típico de
>> plataformas SaaS), por lo que se usa la llave foránea inst_id en todas las
>> tablas para identificar a las instancias (hay una relación "inst" que no he
>> incluido aquí por brevedad).
>>
>> La parte que no me gusta son los dos subqueries anidados para poder
>> extraer la fotografía más reciente, y que en el subquery 'q' haga yo un
>> natural join con file, para luego hacer otro en el subquery 'f'.
>>
>> Alguna sugerencia? Es este un buen query, o hay algo más eficiente o que
>> exprese más claramente lo que se desea obtener?
>>
>> Como me gustarí un recetario de SQL para aprender las mejores prácticas
>> para ciertos tipos de casos; si tienen recomendaciones de lecturas
>> parecidas, se las agradeceré.
>>
>> Saludos,
>> Arturo
>>
>>
>>
>>
>>
>
>
> --
> José Mercedes Venegas Acevedo
> cel claro 940180540
>
> mails: jvenegasperu(at)gmail(dot)com
>