[postgis-users] UPDATE a value using trigger and ST_Contains

[islanis at infomed.sld.cu]

i put t1 and t2 cause the really real names of the tables are long,  
but i put here the real situation,
the upper table is this:
---------------------------------------------------------------------------------------
CREATE TABLE "sigfre_cub_Adm_munic"
(
   gid serial NOT NULL,
   nombre character varying(254),
   cabeceramu character varying(254),
   provincia character varying(254),
   km2 numeric,
   canthabt character varying(254),
   cant_casas integer,
   cod_munic character varying(254),
   cod_prov character varying(254),
   the_geom geometry,
   prov_id integer NOT NULL DEFAULT 0,
   CONSTRAINT "sigfre_cub_Adm_munic_pkey" PRIMARY KEY (gid),
   CONSTRAINT foreign_key01 FOREIGN KEY (prov_id)
       REFERENCES "sigfre_cub_Adm_Prov" (gid) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
   CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =  
'MULTIPOLYGON'::text OR the_geom IS NULL),
   CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326)
)
WITH (
   OIDS=FALSE
);
ALTER TABLE "sigfre_cub_Adm_munic" OWNER TO postgres;

-- Index: "sigfre_cub_Adm_munic_the_geom_gist"

-- DROP INDEX "sigfre_cub_Adm_munic_the_geom_gist";

CREATE INDEX "sigfre_cub_Adm_munic_the_geom_gist"
   ON "sigfre_cub_Adm_munic"
   USING gist
   (the_geom);



---------------------------------------------------------------------------------------
and the lower table is this:
---------------------------------------------------------------------------------------
CREATE TABLE "sigfre_cub_Ren_Inv_Biogas_Porcino"
(
   gid serial NOT NULL,
   "código" character varying(3),
   empresa character varying(10),
   nombre character varying(10),
   "ubicación" character varying(20),
   func character varying(5),
   tbiomasa character varying(16),
   uso character varying(10),
   tbiodigest character varying(10),
   prdiariam3 integer,
   "no" smallint,
   municipio character varying(45),
   estado_tec character varying(30),
   poblac_ben integer,
   capacidad integer,
   the_geom geometry,
   munic_id integer,
   CONSTRAINT "sigfre_cub_Ren_Inv_Biogas_Porcino_pkey" PRIMARY KEY (gid),
   CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
   CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =  
'POINT'::text OR the_geom IS NULL),
   CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326)
)
WITH (
   OIDS=FALSE
);
ALTER TABLE "sigfre_cub_Ren_Inv_Biogas_Porcino" OWNER TO postgres;

-- Index: "sigfre_cub_Ren_Inv_Biogas_Porcino_the_geom_gist"

-- DROP INDEX "sigfre_cub_Ren_Inv_Biogas_Porcino_the_geom_gist";

CREATE INDEX "sigfre_cub_Ren_Inv_Biogas_Porcino_the_geom_gist"
   ON "sigfre_cub_Ren_Inv_Biogas_Porcino"
   USING gist
   (the_geom);


-- Trigger: trigger_actualizar_munic_id on "sigfre_cub_Ren_Inv_Biogas_Porcino"

-- DROP TRIGGER trigger_actualizar_munic_id ON  
"sigfre_cub_Ren_Inv_Biogas_Porcino";

CREATE TRIGGER trigger_actualizar_munic_id
   AFTER INSERT OR UPDATE OR DELETE
   ON "sigfre_cub_Ren_Inv_Biogas_Porcino"
   FOR EACH ROW
   EXECUTE PROCEDURE actualizamunic_id();
---------------------------------------------------------------------------------------
And the procedure actualizamunic_id() is this:
---------------------------------------------------------------------------------------
-- Function: actualizamunic_id()

-- DROP FUNCTION actualizamunic_id();

CREATE OR REPLACE FUNCTION actualizamunic_id()
   RETURNS trigger AS
$BODY$
BEGIN

IF  
ST_Contains(sigfre_cub_Adm_munic.the_geom,ST_Centroid(NEW.the_geom)) =  
TRUE THEN
	NEW."munic_id" = sigfre_cub_Adm_munic.gid;
	END IF;

RETURN NEW;

END;
$BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
ALTER FUNCTION actualizamunic_id() OWNER TO postgres;
---------------------------------------------------------------------------------------
with all this i get the error:

ERROR: falta una entrada para la tabla <<t1>> en la clausula FROM

i'll try the rutine that you gave to me Andy, i'll tell to you the  
result of that, thanks to all.
"Andy Colson" <andy at squeakycode.net> escribió:

> On 02/21/2013 08:53 PM, Andy Colson wrote:
>>>
>>>
>>>   select t1.gid from t1 where  
>>> ST_Contains(t1.the_geom,ST_Centroid(NEW.the_geom)) info  
>>> NEW."munic_id";
>>
>>
>> oops.  s/info/into/
>>
>> -Andy
>>
>
> double oops.  The into goes first:
>
> select t1.gid into NEW."munic_id" from t1 where  
> ST_Contains(t1.the_geom,ST_Centroid(NEW.the_geom));
>
> I really hope I eventually get this right :-)
>
> -Andy
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>



----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.


--

Este mensaje le ha llegado mediante el servicio de correo electronico que ofrece Infomed para respaldar el cumplimiento de las misiones del Sistema Nacional de Salud. La persona que envia este correo asume el compromiso de usar el servicio a tales fines y cumplir con las regulaciones establecidas

Infomed: http://www.sld.cu/