Обсуждение: automatically lower string and remove unnecessary whitespace when INSERT INTO table

Hi, I have table which has two column ( column a & b ). One of the
column has type string..... that is column_a.
Assume somebody insert this value to my table:
INSERT INTO my_table ( column_a ) VALUES ( '   Bla   bla    bla   ' );

I want that string value ( '   Bla  bla    bla   ' ) to be lowered
before insert into my table and remove unnecessary space. So the value
will be 'bla bla bla'. The space is just one.  There will be no space in
front and back of the string. I don't want double space between word
too. How do I accomplish that?????

This is my best effort:
CREATE RULE string_insert AS
         ON INSERT TO my_table
         DO
         UPDATE my_table SET column_a = lower(column_a);

However this way is not efficient because it will update all row. I just
want to update only row which has just been inserted into table. And how
do you remove the double space between words? I know there is function
trim to remove leading and trailing space. But space more than one
between two words?????

Thank you.

Regards,

Akbar

Hi Akbar,
What you are looking are trigers.

 CREATE TRIGGER <triger_name> BEFORE INSERT OR UPDATE
  ON <table_name> FOR EACH ROW EXECUTE PROCEDURE
<function_name>(<function params>);

To find out how to write functions, which can be used in triger, read
the documentation.

Kaloyan



Akbar wrote:

> Hi, I have table which has two column ( column a & b ). One of the
> column has type string..... that is column_a.
> Assume somebody insert this value to my table:
> INSERT INTO my_table ( column_a ) VALUES ( '   Bla   bla    bla   ' );
>
> I want that string value ( '   Bla  bla    bla   ' ) to be lowered
> before insert into my table and remove unnecessary space. So the value
> will be 'bla bla bla'. The space is just one.  There will be no space
> in front and back of the string. I don't want double space between
> word too. How do I accomplish that?????
>
> This is my best effort:
> CREATE RULE string_insert AS          ON INSERT TO my_table
>         DO                     UPDATE my_table SET column_a =
> lower(column_a);
>
> However this way is not efficient because it will update all row. I
> just want to update only row which has just been inserted into table.
> And how do you remove the double space between words? I know there is
> function trim to remove leading and trailing space. But space more
> than one between two words?????
>
> Thank you.
>
> Regards,
>
> Akbar
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>