Re: Ayuda con inquietud

mmm puede ser, pero le apuesto más a guardar la ultima fecha_hora y posicion del evento asi no tienes q ir a buscar en la otra tabla (lo de la foranea es por temas de actualización de llave primaria o borrado físico sobre GPS). Cabe corregir a la function (sea q la metas en un trigger o no), que deba consultar si la fecha a ingresar es mayor a la ultima fecha (la ultima fecha se encuentra en la tabla de GPS y para cuando no tenga registros para ese GPS seria la fecha que se va a insertar en la tabla posiciones),  tal como lo dice Mario:
- Te comento de mi experiencia, que si trabajas con reportes de GPS,
chequees la fecha sea igual o mayor a la anterior que tienes en la tabla
de ultimos reportes. Algunos equipos pueden disparar reportes retrasados
(no se si es tu caso) y hacer updates de fecha y hora de reportes
anteriores a la última.
Sobre todo IGUAL o mayor, ya que cualquier otro cambio que quieras hacer
sin tocar la fecha (Cambio de estado o lo que fuere) no te dejará.

Por ultimo, debes tener en cuenta que a la hora de hacer reportes por fecha_hora por GPS o fecha_hora de los GPS será necesario 1 indice más en la fecha_hora de positions y GPS. No es necesario escribir el indice en las PK ya que estas lo crean automáticamente. ultfechahora tampoco requiere indice ya que consultarás por la PK y no por la ultima fecha del evento del GPS (solo se consulta por ultfechahora para efectos de comparación, ya que la busqueda la debe hacer por la PK)  Entonces tu DDL quedaría:

  gps
-----------
id serial PK
name text
type text
active boolean
posicion varchar
ultfechahora timestamp  --última fechahora del gps.

 positions 
--------------
id serial PK
gps_id integer FK (gps.id)
posicion point
velocidad real                         
altura real
date timestamp
grados real
satelites integer

-- CREATE INDEX "gps_position_id" ON "gps" ("position_id"); este indice se va porq lo debe crear la PK
CREATE INDEX "positions_gps_id" ON "positions" ("gps_id");
CREATE INDEX "positions_date" ON "positions" ("date");

CREATE OR REPLACE FUNCTION public.fn_guardar_posicion (

  gps_id_ integer,
  posicion_ varchar,
  velocidad_ real,
  altura_ real,
  fecha_ timestamp,
  grados_ real,
  satelites_ integer
)
RETURNS integer AS
$body$
DECLARE
        retorno INTEGER;
BEGIN   
   

        INSERT INTO positions
        (
          gps_id,
         
posicion,
          velocidad,
          altura,
          fecha,
          grados,
          satelites
        )
        VALUES (
          gps_id_,
          posicion_,
          velocidad_,
          altura_,
          fecha_,
          grados_,
          satelites_
        )RETURNING id INTO retorno;
--si se insertó con éxito, actualice la ultima fecha del GPS   
    if (retorno is not null) then
       

       UPDATE gps
        SET
          posicion = posicion_,
          ultfechahora = fecha_
        WHERE id = gps_id_ and ultfechahora is not null and ultfechahora < fecha_ ; --esto tambien funciona no actualizando cuando la fecha es menor a la ultima

    end if;   
;

        RETURN retorno;
END;
$body$
LANGUAGE 'plpgsql';

>________________________________
> De: Jorge Toro <jolthgs(at)gmail(dot)com>
>Para: Alejandro Carrillo <fasterzip(at)yahoo(dot)es>
>CC: Lazáro Rubén García Martínez <lgarciam(at)vnz(dot)uci(dot)cu>; Foro Postgres <pgsql-es-ayuda(at)postgresql(dot)org>
>Enviado: Lunes 9 de julio de 2012 11:13
>Asunto: Re: [pgsql-es-ayuda] Ayuda con inquietud
>
>
>Hola Alejandro, gracias por tu ayuda.
>
>
>Lo que entiendo de tu propuesta es hacer:
>
>
>  gps
>-----------
>id serial PK
>name text
>type text
>active boolean
>position_id integer  -- índice a la última posición del gps.
>
>
>
>
> positions 
>--------------
>id serial PK
>gps_id integer FK (gps.id)
>posicion point
>velocidad real                         
>altura real
>date timestamp
>grados real
>satelites integer
>
>
>
>
>CREATE INDEX "gps_position_id" ON "gps" ("position_id");
>CREATE INDEX "positions_gps_id" ON "positions" ("gps_id");
>
>
>
>
>Gracias de antemano.
>
>
>Jorge Alonso Toro
>Ing. Teleinformático.
>
>http://jolthgs.wordpress.com/
>www.devmicrosystem.com
>--------------------------------------------------------------
>Powered By Debian.
>Developer Bullix GNU/Linux.
>--------------------------------------------------------------
>-----BEGIN PGP SIGNATURE-----
>Version: GnuPG v1.4.6 (GNU/Linux)
>
>iD8DBQBIWWH6q7mzdgTzI5ARAkX5AJ9TR6hL2ocLMOUDRfhts8DlVl+jpwCeNw5x
>p4+4FNUHPDUx1lU9F8WSKCA=
>=zRhQ
>-----END PGP SIGNATURE-----
>Este correo esta protegido bajo los términos de la Licencia
Atribución-Compartir Obras Derivadas Igual a 2.5 Colombia de Creative
Commons. Observé la licencia visitando este sitio http://creativecommons.org/licenses/by-sa/2.5/co/.
>
>
>
>El 9 de julio de 2012 10:34, Alejandro Carrillo <fasterzip(at)yahoo(dot)es> escribió:
>
>Creo que lo mejor es hacer un índice único en la tabla GPS (llave primaria), un campo donde esté la última posición del GPS y otro índice en la llave foránea de GPS en la tabla positions. Y crear una function asi:
>>
>>
>>CREATE OR REPLACE FUNCTION public.fn_guardar_posicion (
>>
>>  gps_id_ integer,
>>  posicion_ varchar,
>>  velocidad_ real,
>>  altura_ real,
>>  fecha_ timestamp,
>>  grados_ real,
>>  satelites_ integer
>>)
>>RETURNS integer AS
>>$body$
>>DECLARE
>>        retorno INTEGER;
>>BEGIN   
>>   
>>
>>        INSERT INTO positions
>>        (
>>          gps_id,
>>         
posicion,
>>          velocidad,
>>          altura,
>>          fecha,
>>          grados,
>>          satelites
>>        )
>>        VALUES (
>>          gps_id_,
>>          posicion_,
>>          velocidad_,
>>          altura_,
>>          fecha_,
>>          grados_,
>>          satelites_
>>        )RETURNING id INTO retorno;
>>    if (id is null) then
>>       UPDATE gps
>>        SET
>>          posicion = posicion_,
>>        WHERE id = gps_id_;
>>
>>    end if;   
>>;
>>
>>        RETURN retorno;
>>END;
>>$body$
>>LANGUAGE 'plpgsql';
>>
>>
>>Así no necesitas tener más tablas adicionales. No olvidar hacer vacuum full a las tablas periódicamente en horario donde haya pocas transacciones.
>>
>>
>>
>>
>>>________________________________
>>> De: Lazáro Rubén García Martínez <lgarciam(at)vnz(dot)uci(dot)cu>
>>>Para: Jorge Toro <jolthgs(at)gmail(dot)com>; Foro Postgres <pgsql-es-ayuda(at)postgresql(dot)org>
>>>Enviado: Lunes 9 de julio de 2012 10:21
>>>Asunto: RE: [pgsql-es-ayuda] Ayuda con inquietud
>>>
>>>
>>>Entonces podrias tener dos tablas positions, una que se llame hist_positions por ejemplo, y la otra positions, sobre positions almacenas la última ubicación de los gps y sobre hist_positions el historial de posiciones de los gps, para almacenar las posiciones de los gps utililizas una función, y para llevar el historial de los gps, puedes utilizar un trigger sobre positions. Acá te dejo el código de todo, solo falta la referencia de la clave foránea. Espero que te sirva.
>>>
>>>PD: Pudieran existir otras soluciones.
>>>
>>>--Crear la tabla positions
>>>CREATE TABLE public.positions (
>>>  id
SERIAL,
>>>  gps_id INTEGER,
>>>  posicion VARCHAR,
>>>  velocidad REAL,
>>>  altura REAL,
>>>  fecha TIMESTAMP WITHOUT TIME ZONE,
>>>  grados REAL,
>>>  satelites INTEGER,
>>>  CONSTRAINT positions_pkey PRIMARY KEY(id)
>>>) WITHOUT OIDS;
>>>
>>>--Crear la tabla hist_positions
>>>CREATE TABLE public.hist_positions (
>>>  id SERIAL,
>>>  gps_id INTEGER,
>>>  posicion VARCHAR,
>>>  velocidad REAL,
>>>  altura REAL,
>>>  fecha TIMESTAMP WITHOUT TIME ZONE,
>>>  grados REAL,
>>>  satelites INTEGER,
>>>  CONSTRAINT hist_positions_pkey PRIMARY KEY(id)
>>>) WITHOUT OIDS;
>>>
>>>--Función para guardar en positions
>>>CREATE OR REPLACE FUNCTION public.fun_guardar_posicion (
>>>  gps_id_ integer,
>>>  posicion_ varchar,
>>>  velocidad_ real,
>>>  altura_ real,
>>>  fecha_ timestamp,
>>>  grados_ real,
>>>  satelites_ integer
>>>)
>>>RETURNS
integer AS
>>>$body$
>>>DECLARE
>>>        retorno INTEGER;
>>>BEGIN
>>>    UPDATE positions
>>>    SET
>>>      posicion = posicion_,
>>>      velocidad = velocidad_,
>>>      altura = altura_,
>>>      fecha = fecha_,
>>>      grados = grados_,
>>>      satelites = satelites_
>>>    WHERE gps_id = gps_id_
>>>    RETURNING id INTO retorno;
>>>
>>>        IF (retorno IS NULL) THEN
>>>        INSERT INTO positions
>>>        (
>>>          gps_id,
>>>          posicion,
>>>          velocidad,
>>>          altura,
>>>          fecha,
>>>          grados,
>>>         
satelites
>>>        )
>>>        VALUES (
>>>          gps_id_,
>>>          posicion_,
>>>          velocidad_,
>>>          altura_,
>>>          fecha_,
>>>          grados_,
>>>          satelites_
>>>        )RETURNING id INTO retorno;
>>>    END IF;
>>>
>>>        RETURN retorno;
>>>END;
>>>$body$
>>>LANGUAGE 'plpgsql';
>>>
>>>--Función ejecutada por el trigger para guardar en el historial de positions
>>>CREATE OR REPLACE FUNCTION public.fun_trg_hist_positions (
>>>)
>>>RETURNS trigger AS
>>>$body$
>>>DECLARE
>>>BEGIN
>>>  INSERT INTO hist_positions
>>>  (
>>>    gps_id,
>>>    posicion,
>>>    velocidad,
>>>    altura,
>>> 
  fecha,
>>>    grados,
>>>    satelites
>>>  )
>>>  VALUES (
>>>    NEW.gps_id,
>>>    NEW.posicion,
>>>    NEW.velocidad,
>>>    NEW.altura,
>>>    NEW.fecha,
>>>    NEW.grados,
>>>    NEW.satelites
>>>  );
>>>
>>>  RETURN NULL;
>>>END;
>>>$body$
>>>LANGUAGE 'plpgsql';
>>>
>>>--Trigger utilizado
>>>CREATE TRIGGER trg_hist_positions AFTER INSERT OR UPDATE
>>>ON public.positions FOR EACH ROW
>>>EXECUTE PROCEDURE public.fun_trg_hist_positions();
>>>
>>>Saludos a todos.
>>>________________________________________
>>>From: pgsql-es-ayuda-owner(at)postgresql(dot)org [pgsql-es-ayuda-owner(at)postgresql(dot)org] On Behalf Of Jorge Toro [jolthgs(at)gmail(dot)com]
>>>Sent: Monday, July 09, 2012 9:43 AM
>>>To: Foro Postgres
>>>Subject: Re: [pgsql-es-ayuda] Ayuda con inquietud
>>>
>>>Hola Lazáro, gracias por responder.
>>>
>>>Sí gps.id<http://gps.id> es la referencia a una tabla "gps" que se usa para registrar cada uno de los GPS que se registran para ser aceptados por el servidor.
>>>Esta tabla "gps" tiene datos como: id, name, type, active. de cada uno de los GPS. Y la tabla "position" se encarga de almacenar los reportes (históricos) hechos por cada GPS.
>>>
>>>Y lo que quiero lograr es, tener a la mano siempre que consulte un dispositivo, toda la última información de dicho dispositivo que se encuentre en la tabla "position".
>>>
>>>Pero no quiero realizar una consulta directa a  la tabla "position" porque son alrededor de 1000 GPS reportando cada minuto y esta
tabla "position" tenderá a ser demasiado grande con el pasar del tiempo.
>>>
>>>
>>>Por tu ayuda muchas gracias,
>>>
>>>
>>>Jorge Alonso Toro
>>>Ing. Teleinformático.
>>>
>>>http://jolthgs.wordpress.com/
>>>www.devmicrosystem.com<http://www.devmicrosystem.com>
>>>--------------------------------------------------------------
>>>Powered By Debian.
>>>Developer Bullix GNU/Linux.
>>>--------------------------------------------------------------
>>>-----BEGIN PGP SIGNATURE-----
>>>Version: GnuPG v1.4.6 (GNU/Linux)
>>>
>>>iD8DBQBIWWH6q7mzdgTzI5ARAkX5AJ9TR6hL2ocLMOUDRfhts8DlVl+jpwCeNw5x
>>>p4+4FNUHPDUx1lU9F8WSKCA=
>>>=zRhQ
>>>-----END PGP SIGNATURE-----
>>>Este correo esta protegido bajo los términos de la Licencia Atribución-Compartir Obras Derivadas Igual a 2.5 Colombia de Creative Commons. Observé la licencia visitando este
sitio http://creativecommons.org/licenses/by-sa/2.5/co/.
>>>
>>>
>>>El 9 de julio de 2012 09:17, Lazáro Rubén García Martínez <lgarciam(at)vnz(dot)uci(dot)cu<mailto:lgarciam(at)vnz(dot)uci(dot)cu>> escribió:
>>>La columna gps_id es única
>>>
>>>
>>>________________________________
>>>Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU!
>>>http://www.antiterroristas.cu
>>>http://justiciaparaloscinco.wordpress.com
>>>
>>>Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE ENCUENTRAN INJUSTAMENTE EN
PRISIONES DE LOS EEUU!
>>>http://www.antiterroristas.cu
>>>http://justiciaparaloscinco.wordpress.com
>>>-
>>>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
>>>
>>>
>>>
>
>
>