Trigger and another field

Question

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. 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 Trigger, but is there a more optimized way to make the recording, taking advantage of Trigger and taking the load of PHP?

Answer 1

You don’t need a TRIGGER replication for this. In your case, a TRIGGER it doesn’t seem to make any sense and it’s certainly just complicating the thing.

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

You mentioned the use of INSERT/RETURNING, and based on that, I believe you’re using the SERIAL in your primary keys.

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

CREATE TABLE tb_teste ( id BIGSERIAL, nome TEXT );

Exit:

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.

Note that the SEQUENCE by name tb_teste_id_seq was created to control the field id table tb_teste.

You can also use the function pg_get_serial_sequence() to consult which SEQUENCE is behind the control of a certain auto-increment field:

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

Exit:

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

From the name of SEQUENCE control of the auto-increment field, it is possible to obtain by means of the function currval(), the last identifier inserted in the table tb_teste, look at you:

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

I assume your 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
);

Consulting SEQUENCES:

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

Exit:

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

Registering Pessoas and Telefones:

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

Exit:

| 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, aggregating the phones in the same record:

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;

Exit:

| 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 on Sqlfiddle.com