Trigger for select function

Question

How I can convert this query to trigger:

select concat (
    categories.abreviation , '/' ,
    to_char(date_op,'yy'),'/',
    to_char(date_op,'mm'),'/',
    trim(to_char(compteur_op_cat,'000'))
) as num_bord
from
    operation op
    inner join
    categories on categories.id_cat=op.id_cat ; 

Trigger for table operation, before select return num_bord as column. I need a trigger to concat num_bord for some reason:

1. Performance (note that I have num_bord and num_op for concat() ).
2. Search with LIKE easily.

Answer 1

First, you can simplify your SELECT.

select concat_ws ('/'
        , c.abreviation
        , to_char(date_op,'yy/mm')
        , to_char(compteur_op_cat, 'FM000')
       ) AS num_bord
from   operation o
join   categories c USING (id_cat);

But there are no "triggers on SELECT". You may be looking for a VIEW or a MATERIALIZED VIEW.

The key to performance with LIKE is indexing:

• PostgreSQL LIKE query performance variations

Explain

• Use concat_ws(), since you want to insert separators.

• The FM modifier avoids leading spaces for numbers.

  • Remove blank-padding from to_char() output

Aside: it's abbreviation, not abreviation.