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 <artur...@gmail.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: jvenegasp...@gmail.com
