How to update column for each SELECT in a given table?

Question

How to update column for each SELECT in a given table?

Navigation

#1 by (1 votes)

1

I tried to create a trigger for a table using it so that it would be activated after every select , but by searching a little I discovered that it is not possible to create triggers for selections, only for update, insert ) and delete.

My application is an api and the same one is presenting problems in Benchmark test; every time I make a select I have another function that performs an update and increments a column of that selected record. The problem is that many updates at the same time are causing this error:

sorry, too many clients already

That's when I thought of creating a% direct% of the database so I would not have to run a trigger via code every update .

This was the code I created in select to try to increment the column:

create table urls(
    id integer not null,
    url varchar(255) not null,
    encoded varchar(255) not null UNIQUE,
    clicks integer DEFAULT 0,
    created_at timestamp not null DEFAULT current_timestamp,
    constraint pk_urls_id primary key (id)
);


CREATE OR REPLACE FUNCTION increment_clicks_counter()
    returns trigger as
        $BODY$
            BEGIN
                UPDATE urls SET clicks = clicks + 1 WHERE encoded = OLD.encoded;
            END;
        $BODY$
language 'plpgsql';

CREATE trigger increment_clicks AFTER SELECT ON urls
    for each ROW EXECUTE procedure increment_clicks_counter();

How can I create a routine to increment a column at each selection of a given table?

This is the Benchmark I am running on plpgsql :

func BenchmarkAPI(b *testing.B) {
    // Valores que estão presentes na coluna 'encoded' da tabela urls
    random := []string{
        "A", "B", "C", "D", "H", "F", "E", "G",
        "8", "5", "9", "6", "7", "2",
    }

    for n := 0; n < b.N; n++ {
        url := fmt.Sprintf("http://localhost:5000/%s", random[rand.Intn(len(random))])

        _, err := http.Get(url)
        if err != nil {
            b.Log(err.Error())
        }
    }
}

sql postgresql golang plpgsql

asked by anonymous 13.06.2018 / 18:49

1 answer

Image uploading with html5 in crossfade Return an array from a function in javascript

score 1 · Accepted Answer

Unfortunately (or fortunately), PostgreSQL does not exist TRIGGERS of type AFTER SELECT .

However, you can solve your problem by "encapsulating" your table in a stored procedure written in PL/pgSQL .

This stored procedure would be able to retrieve the data from the table and then write that activity to another specific table, the table could not be directly accessed and its data would only be retrieved through this function encapsulation.

Consider the following structure / data:

CREATE TABLE tb_pessoa
(
    id BIGINT PRIMARY KEY,
    nome TEXT NOT NULL,
    nascimento DATE NOT NULL,
    sexo VARCHAR(1) NOT NULL
);

CREATE TABLE tb_log
(
    id BIGSERIAL PRIMARY KEY,
    tabela TEXT NOT NULL,
    operacao TEXT NOT NULL,
    diahora TIMESTAMP NOT NULL 
);

INSERT INTO tb_pessoa ( id, nome, nascimento, sexo ) VALUES ( 1, 'JOAO',  '1980.02.10', 'M' );
INSERT INTO tb_pessoa ( id, nome, nascimento, sexo ) VALUES ( 2, 'MARIA', '1966.05.10', 'F' );
INSERT INTO tb_pessoa ( id, nome, nascimento, sexo ) VALUES ( 3, 'JOSE',  '1973.07.10', 'M' );
INSERT INTO tb_pessoa ( id, nome, nascimento, sexo ) VALUES ( 4, 'ANA',   '1984.03.10', 'F' );
INSERT INTO tb_pessoa ( id, nome, nascimento, sexo ) VALUES ( 5, 'JESUS', '1970.12.10', 'M' );

This encapsulation function would look something like this:

CREATE OR REPLACE FUNCTION fc_wrapper_pessoa()
RETURNS SETOF tb_pessoa AS
$$
DECLARE
    rec tb_pessoa%rowtype;
BEGIN
    FOR  rec IN SELECT * FROM tb_pessoa
    LOOP
        return next rec;
    END LOOP;

    INSERT INTO tb_log ( tabela, operacao, diahora ) VALUES ( 'tb_pessoa', 'SELECT', now() );

    RETURN;
END
$$
LANGUAGE 'plpgsql';

Queries on the tb_pessoa table would all be done through this function, for example:

SELECT * FROM fc_wrapper_pessoa();
SELECT * FROM fc_wrapper_pessoa() WHERE id = 5;
SELECT * FROM fc_wrapper_pessoa() WHERE nome = 'JESUS';
SELECT * FROM fc_wrapper_pessoa() WHERE nascimento BETWEEN '1960-01-01' AND '1969-12-31';

And finally, for each query done in the tb_pessoa table, a record would be written to the tb_log table:

SELECT * FROM tb_log WHERE tabela = 'tb_pessoa' AND operacao = 'SELECT';

Output:

| id |    tabela | operacao |                     diahora |
|----|-----------|----------|-----------------------------|
| 11 | tb_pessoa |   SELECT | 2018-06-14T16:06:18.073404Z |
| 12 | tb_pessoa |   SELECT | 2018-06-14T16:06:18.073404Z |
| 13 | tb_pessoa |   SELECT | 2018-06-14T16:06:18.073404Z |
| 14 | tb_pessoa |   SELECT | 2018-06-14T16:06:18.073404Z |

If what matters to you is how many queries the table has experienced:

SELECT count(1) FROM tb_log WHERE tabela = 'tb_pessoa' AND operacao = 'SELECT';

Output:

| count |
|-------|
|     4 |

SQLFiddle