Trigger and another field

Question

Trigger and another field

Navigation

#1 by (0 votes)

0

In postgres I have two tables, test1 (id_test1, name) and test2 (id_test2, test1_id_test1, phone)

In the PHP form I have the field name and phone number. When I write name in test1, a trigger replicates the primary key in test2 (test1_id_test1)

CREATE TRIGGER insert_test2
  AFTER INSERT
  ON test1
  FOR EACH ROW
  EXECUTE PROCEDURE update_test2();

>

CREATE OR REPLACE FUNCTION update_test2()
  RETURNS trigger AS
$BODY$BEGIN
INSERT INTO test2
    (test1_id_test1)
VALUES
    (new.id_test1);
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE COST 100;

With a SQL INSERT with RETURNING I can record the phone in test2 and so I could even dismiss the trigger, but is there a more optimized way to do the recording, taking advantage of the trigger and taking the PHP load?

postgresql trigger

asked by anonymous 06.07.2018 / 17:22

1 answer

Google Maps - Remove polyline if you already have another These dependencies were not found:

score 0 · Answer 1

You do not need a% replication% for this. In your case, a TRIGGER seems to make no sense at all and is certainly only complicating the thing.

If your application is doing TRIGGER of the record in one table, you can perfectly make INSERT of other records in the other, as long as you do everything within a single transaction.

You mentioned using INSERT , and based on that, I believe that you are using INSERT/RETURNING on your primary keys.

In SERIAL , every time you create a field of type PostgreSQL , SMALLSERIAL or SERIAL , a BIGSERIAL is implicitly created to control the auto-increment of this field, for example:

CREATE TABLE tb_teste ( id BIGSERIAL, nome TEXT );

Output:

NOTICE:  CREATE TABLE will create implicit sequence "tb_teste_id_seq" for serial column "tb_teste.id"
Query returned successfully with no result in 377 msec.

Notice that SEQUENCE name SEQUENCE was created to control the tb_teste_id_seq field of the id table.

You can also use tb_teste to see which pg_get_serial_sequence() is behind the control of a given auto-increment field:

SELECT pg_get_serial_sequence( 'tb_teste', 'id' );

Output:

| pg_get_serial_sequence |
|------------------------|
| public.tb_teste_id_seq |

From the% control name of the auto-increment field, you can get through the SEQUENCE , the last identifier inserted in the SEQUENCE table, see:

INSERT INTO tb_teste ( nome ) VALUES ( 'foobar' ); 
SELECT currval('tb_teste_id_seq');

I assume its structure is something like:

CREATE TABLE tb_pessoa
(
  id BIGSERIAL PRIMARY KEY,
  nome TEXT
);

CREATE TABLE tb_telefone
(
  id BIGSERIAL PRIMARY KEY,
  id_pessoa INTEGER NOT NULL REFERENCES tb_pessoa( id ),
  numero TEXT
);

Querying currval() :

SELECT
    pg_get_serial_sequence( 'tb_pessoa', 'id' ),
    pg_get_serial_sequence( 'tb_telefone', 'id' );

Output:

|  pg_get_serial_sequence |    pg_get_serial_sequence |
|-------------------------|---------------------------|
| public.tb_pessoa_id_seq | public.tb_telefone_id_seq |

Registering tb_teste and SEQUENCES :

-- CADASTRANDO MARIA COM 3 TELEFONES
BEGIN;
INSERT INTO tb_pessoa ( nome ) VALUES ( 'MARIA' );
INSERT INTO tb_telefone ( id_pessoa, numero ) VALUES ( currval('tb_pessoa_id_seq'), '9999-1234' );
INSERT INTO tb_telefone ( id_pessoa, numero ) VALUES ( currval('tb_pessoa_id_seq'), '8181-1020' );
INSERT INTO tb_telefone ( id_pessoa, numero ) VALUES ( currval('tb_pessoa_id_seq'), '9911-3344' );
COMMIT;    

-- CADASTRANDO JOAO COM 2 TELEFONES
BEGIN;
INSERT INTO tb_pessoa ( nome ) VALUES ( 'JOAO' );
INSERT INTO tb_telefone ( id_pessoa, numero ) VALUES ( currval('tb_pessoa_id_seq'), '9799-3579' );
INSERT INTO tb_telefone ( id_pessoa, numero ) VALUES ( currval('tb_pessoa_id_seq'), '9191-9020' );
COMMIT;

-- CADASTRANDO JESUS COM 2 TELEFONES
BEGIN;
INSERT INTO tb_pessoa ( nome ) VALUES ( 'JESUS' );
INSERT INTO tb_telefone ( id_pessoa, numero ) VALUES ( currval('tb_pessoa_id_seq'), '8591-0666' );
INSERT INTO tb_telefone ( id_pessoa, numero ) VALUES ( currval('tb_pessoa_id_seq'), '9581-0001' );
COMMIT;

Consulting:

SELECT
  p.id,
  p.nome,
  tel.numero
FROM
  tb_pessoa AS p
JOIN
  tb_telefone AS tel ON ( tel.id_pessoa = p.id );

Output:

| id |  nome |    numero |
|----|-------|-----------|
|  1 | MARIA | 9999-1234 |
|  1 | MARIA | 8181-1020 |
|  1 | MARIA | 9911-3344 |
|  2 |  JOAO | 9799-3579 |
|  2 |  JOAO | 9191-9020 |
|  3 | JESUS | 8591-0666 |
|  3 | JESUS | 9581-0001 |

Or, adding the phones in the same registry:

SELECT
  p.id,
  p.nome,
  string_agg( tel.numero, ' / ' )
FROM
  tb_pessoa AS p
JOIN
  tb_telefone AS tel ON ( tel.id_pessoa = p.id )
GROUP BY
  p.id,
  p.nome;

Output:

| id |  nome |                        string_agg |
|----|-------|-----------------------------------|
|  1 | MARIA | 9999-1234 / 8181-1020 / 9911-3344 |
|  2 |  JOAO |             9799-3579 / 9191-9020 |
|  3 | JESUS |             8591-0666 / 9581-0001 |

See working in SQLFiddle.com