Re: Duda de SQL.

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