Bem, já consegui alguns progressos com a função.
No entanto tenho o seguinte erro:
ERROR: no se puede hacer ALTER TABLE en «postfire_study_area» porque está
siendo usada por consultas activas en esta sesión
CONTEXTO: sentencia SQL: «ALTER TABLE sch_forestal.postfire_study_area ADD
COLUMN area bigint»
Fiz as seguintes alterações:
> CREATE OR REPLACE FUNCTION fun_dimensoes()
> RETURNS trigger AS
> $BODY$
> DECLARE
>
esquema varchar(20);
tabela varchar(50);
> tipo varchar(20);
> srid integer;
> BEGIN
>
esquema := TG_TABLE_SCHEMA;
tabela := TG_TABLE_NAME;
> tipo := (SELECT "type" FROM geometry_columns
> WHERE f_table_schema = TG_TABLE_SCHEMA
> AND f_table_name = TG_TABLE_NAME);
> srid := (SELECT srid FROM geometry_columns
> WHERE f_table_schema = TG_TABLE_SCHEMA
> AND f_table_name = TG_TABLE_NAME);
>
> IF (tipo = 'POLYGON' OR tipo = 'MULTIPOLYGON') THEN
> IF NOT EXISTS(SELECT column_name FROM information_schema.columns
> WHERE table_schema = TG_TABLE_SCHEMA
> AND table_name = TG_TABLE_NAME
> AND column_name='area') THEN
>
EXECUTE 'ALTER TABLE ' || quote_ident(esquema) || '.' ||
quote_ident(tabela) || ' ADD COLUMN area bigint';
> IF (srid = 23030 OR srid = 25830) THEN
>
EXECUTE 'UPDATE ' || quote_ident(esquema) || '.' || quote_ident(tabela) ||
' SET area = ST_Area(geom)::bigint';
> ELSIF (srid = 4326) THEN
>
EXECUTE 'UPDATE ' || quote_ident(esquema) || '.' || quote_ident(tabela) ||
' SET area = ST_Area(Geography(geom))::bigint';
> END IF;
> ELSE
> IF (srid = 23030 OR srid = 25830) THEN
> NEW.area = ST_Area(NEW.geom)::bigint;
> ELSIF (srid = 4326) THEN
> NEW.area = ST_Area(Geography(NEW.geom))::bigint;
> END IF;
> END IF;
> END IF;
>
> RETURN NEW;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
> COST 100;
>
> CREATE TRIGGER trg_postfire_study_area_UPDATE_dimensoes
> BEFORE INSERT OR UPDATE
> ON sch_forestal.postfire_study_area
> FOR EACH ROW
> EXECUTE PROCEDURE fun_dimensoes();
>
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
