如何基于外键是另一个表上的主键自动填充行中的列

Question

我正在为QGIS开发一个sql数据库。我有8个表:其中3个是基表，其他是关系表。一个关系表"tabela_is_po“有4个字段：

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

基表"tabela_objecto_area“有7个字段：

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)

在"tabela_objecto area“中，当我填充indice_sequencial_po整数(外键)时，我希望表能够根据indice_sequencial_po中填充的数字自动从关系表"tabela_is_po”中获取数据。

我试过触发器功能，但从来没有很好地工作过。

tabela_objecto_area代码：

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
);

触发器功能：

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();

Answer 1

使用视图可能会更好。有关概述，请参阅https://en.wikipedia.org/wiki/Relational_model，基本上每个项目在数据库中应该只出现一次。

对于数据输入，使用像django (https://www.djangoproject.com/)这样的框架来创建表和输入数据的结构通常更容易，然后可以在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);