On Tue, Nov 16, 2010 at 23:29, Osvaldo Kussama <osvaldo.kuss...@gmail.com>wrote:
> O teste está errado. o campo não informado não contém NULL. Teste se é
> igual ao OLD.
>
> Veja:
> bdteste=# CREATE TEMP TABLE teste (id serial primary key, x int, x100 int);
> NOTICE: CREATE TABLE will create implicit sequence "teste_id_seq" for
> serial column "teste.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "teste_pkey" for table "teste"
> CREATE TABLE
> bdteste=#
> bdteste=# CREATE OR REPLACE FUNCTION funcao_teste() RETURNS trigger AS
> bdteste-# $$
> bdteste$# BEGIN
> bdteste$# RAISE NOTICE '% - id: % - x: % - x100: %', TG_OP, NEW.id,
> NEW.x, NEW.x100;
> bdteste$# IF (TG_OP = 'INSERT') THEN
> bdteste$# IF (NEW.x IS NOT NULL) THEN
> bdteste$# NEW.x100 = NEW.x*100;
> bdteste$# ELSE
> bdteste$# NEW.x = NEW.x100/100;
> bdteste$# END IF;
> bdteste$# ELSE
> bdteste$# IF (TG_OP = 'UPDATE') THEN
> bdteste$# IF (NEW.x <> OLD.x) THEN
> bdteste$# NEW.x100 = NEW.x*100;
> bdteste$# ELSE
> bdteste$# IF (NEW.x100 <> OLD.x100) THEN
> bdteste$# NEW.x = NEW.x100/100;
> bdteste$# END IF;
> bdteste$# END IF;
> bdteste$# END IF;
> bdteste$# END IF;
> bdteste$# RETURN NEW;
> bdteste$# END;
> bdteste$# $$ LANGUAGE plpgsql;
> CREATE FUNCTION
> bdteste=#
> bdteste=# CREATE TRIGGER funcao_teste BEFORE INSERT OR UPDATE ON teste
> FOR EACH ROW EXECUTE PROCEDURE funcao_teste();
> CREATE TRIGGER
> bdteste=#
> bdteste=# INSERT INTO teste(x) VALUES(1);
> NOTICE: INSERT - id: 1 - x: 1 - x100: <NULL>
> INSERT 0 1
> bdteste=# INSERT INTO teste(x100) VALUES (200);
> NOTICE: INSERT - id: 2 - x: <NULL> - x100: 200
> INSERT 0 1
> bdteste=# SELECT * FROM teste;
> id | x | x100
> ----+---+------
> 1 | 1 | 100
> 2 | 2 | 200
> (2 rows)
>
> bdteste=#
> bdteste=# UPDATE teste SET x = 3 WHERE x = 2;
> NOTICE: UPDATE - id: 2 - x: 3 - x100: 200
> UPDATE 1
> bdteste=# SELECT * FROM teste;
> id | x | x100
> ----+---+------
> 1 | 1 | 100
> 2 | 3 | 300
> (2 rows)
>
> bdteste=#
> bdteste=# UPDATE teste SET x100 = 200 WHERE x = 3;
> NOTICE: UPDATE - id: 2 - x: 3 - x100: 200
> UPDATE 1
> bdteste=# SELECT * FROM teste;
> id | x | x100
> ----+---+------
> 1 | 1 | 100
> 2 | 2 | 200
> (2 rows)
>
> Osvaldo
>
Resolvido, era isso. Osvaldo muito obrigado mesmo pela ajuda, foi decisiva.
Aqui fica a versão final:
--------
--SELECT DropGeometryColumn('sch_temp','teste','geom_23030');
--SELECT DropGeometryColumn('sch_temp','teste','geom_4258');
--DROP TABLE sch_temp.teste;
CREATE TABLE sch_temp.teste (gid serial primary key, longitude double
precision);
SELECT AddGeometrycolumn
('sch_temp','teste','geom_23030',23030,'LINESTRING',2);
SELECT AddGeometrycolumn
('sch_temp','teste','geom_4258',4258,'LINESTRING',2);
CREATE OR REPLACE FUNCTION funcao_teste() RETURNS trigger AS
$$
BEGIN
IF (TG_OP = 'INSERT') THEN
IF (NEW.geom_23030 IS NOT NULL) THEN
NEW.geom_4258 = ST_Transform((NEW.geom_23030), 4258);
ELSE
NEW.geom_23030 = ST_Transform((NEW.geom_4258), 23030);
END IF;
ELSE
IF (TG_OP = 'UPDATE') THEN
IF (st_asewkt(NEW.geom_23030) <> st_asewkt(OLD.geom_23030)) THEN
NEW.geom_4258 = ST_Transform((NEW.geom_23030),
4258);
ELSE
IF (st_asewkt(NEW.geom_4258) <>
st_asewkt(OLD.geom_4258)) THEN
NEW.geom_23030 =
ST_Transform((NEW.geom_4258), 23030);
END IF;
END IF;
END IF;
END IF;
NEW.longitude = ST_Length(NEW.geom_23030);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS funcao_teste ON sch_temp.teste;
CREATE TRIGGER funcao_teste BEFORE INSERT OR UPDATE ON sch_temp.teste FOR
EACH ROW EXECUTE PROCEDURE funcao_teste();
*Testando INSERT:*
INSERT INTO sch_temp.teste(geom_23030)
VALUES(ST_GeomFromText('SRID=23030;LINESTRING(232400 4548000,700882
4548000)'));
INSERT INTO sch_temp.teste(geom_4258) VALUES
(ST_GeomFromText('SRID=4258;LINESTRING(-6 38,-1 38)'));
SELECT gid, longitude, ST_AsText(geom_23030), ST_AsText(geom_4258) FROM
sch_temp.teste;
--1;468482;"LINESTRING(232400 4548000,700882
4548000)";"LINESTRING(-6.18474396455383 41.0373694063174,-0.610778537766755
41.0566010640838)"
--2;439031.558882987;"LINESTRING(236688.97999065
4210267.75646057,675714.208368959 4207910.10029429)";"LINESTRING(-6 38,-1
38)"
*Testando UPDATE:*
UPDATE sch_temp.teste SET geom_23030 =
ST_GeomFromText('SRID=23030;LINESTRING(250000 4700000,705000 4700000)')
WHERE gid = 1;
UPDATE sch_temp.teste SET geom_4258 =
ST_GeomFromText('SRID=4258;LINESTRING(-5 37,-1 37)') WHERE gid = 2;
SELECT gid, longitude, ST_AsText(geom_23030), ST_AsText(geom_4258) FROM
sch_temp.teste;
--1;455000;"LINESTRING(250000 4700000,705000
4700000)";"LINESTRING(-6.03947581959512 42.4101587355779,-0.509472631922339
42.4233762846083)"
--2;355923.537746918;"LINESTRING(322150.259231365
4096946.77669013,678073.796964852 4096949.86875352)";"LINESTRING(-5 37,-1
37)"
Cumprimentos,
Eloi Ribeiro
GIS Analyst
39,45º -4,40º
http://eloiribeiro.wordpress.com
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
