How to automatically fill columns on a row based on the foreign key being the primary key on another table

Question

I'm working on a sql database for QGIS. I have 8 tables: 3 three of them are the base tables and the others are relational tables. One relational table, "tabela_is_po", has 4 fields:

indice_sequencial_po (integer and Primary Key),
tema (character varying),
subtema (character varying),
designacao (character varying)

And the base table, "tabela_objecto_area", has 7 fields:

identificador integer ,
dtcc character varying(4),
planta_po boolean,
indice_sequencial_po integer (foreign key),
tema_po character varying(254),
subtema_po character varying(254),
designacao_po character varying(254)

In "tabela_objecto area", when I fill, indice_sequencial_po integer (foreign key), I want the table to be able to automatically get the data from the relational table "tabela_is_po", based on the number that is filled in indice_sequencial_po.

I've tried the trigger function but it never worked well.

tabela_objecto_area code:

CREATE TABLE  pdm2019.tabela_objecto_area
(
identificador integer NOT NULL DEFAULT nextval('pdm2019.tabela_objecto_area_identificador_seq'::regclass),
dtcc character varying(4) NOT NULL DEFAULT '0101',
planta_po boolean NOT NULL,
indice_sequencial_po integer,
tema_po character varying(254),
subtema_po character varying(254),
designacao_po character varying(254),
CONSTRAINT tabela_objecto_area_pkey PRIMARY KEY (identificador)
)
WITH (
  OIDS=FALSE
);

Trigger function:

CREATE OR REPLACE FUNCTION tema_e_subtema_automatico() RETURNS TRIGGER AS $tema_e_subtema_automatico$
    BEGIN
    IF NEW.pdm2019.tabela_objecto_area.indice_sequencial_po IS NOT NULL THEN
       INSERT INTO pdm2019.tabela_objecto_area(tema_po,subtema_po,designacao_po) SELECT tema,subtema,designacao FROM pdm2019.tabela_is_po WHERE indice_sequencial_po = NEW.pdm2019.tabela_objecto_area.indice_sequencial_po;
    END IF;
    END;
$tema_e_subtema_automatico$ LANGUAGE plpgsql;

CREATE TRIGGER tema_e_subtema_automatico AFTER INSERT OR UPDATE ON pdm2019.tabela_objecto_area
    FOR EACH ROW EXECUTE PROCEDURE tema_e_subtema_automatico();

score 0 · Answer 1 · answered Jun 19 '19 at 15:25

You would probably be better off using a view. See https://en.wikipedia.org/wiki/Relational_model for an overview, basically each item should appear only once in the database.

For data entry, it's often easier to use a framework like django (https://www.djangoproject.com/) to create the structure of your tables and input data, which you can then view in qgis.

    CREATE TABLE  pdm2019.tabela_objecto_area
    (
    identificador integer NOT NULL DEFAULT nextval('pdm2019.tabela_objecto_area_identificador_seq'::regclass),
    dtcc character varying(4) NOT NULL DEFAULT '0101',
    planta_po boolean NOT NULL,
    indice_sequencial_po integer,
    -- tema_po character varying(254), -- use a view to populate these
    -- subtema_po character varying(254), -- use a view to populate these
    -- designacao_po character varying(254), -- use a view to populate these
    CONSTRAINT tabela_objecto_area_pkey PRIMARY KEY (identificador)
    )
    WITH (
      OIDS=FALSE
    );

    CREATE VIEW v_tabela_objecto_area as (
    select a.*, b.tema_po, b.subtema_po, b.designacao_po 
    from tabela_objecto_area a, tabela_is_po b
    where a.indice_sequencial_po = b.indice_sequencial_po);

How to automatically fill columns on a row based on the foreign key being the primary key on another table

1 Answers1