Excelente, gracias.
2015-01-21 14:01 GMT-06:00 jvenegasperu . <jvenegasp...@gmail.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 <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 >
