I have been trying to accomplish an automatic update of a table based on a column value. Here is the example. The idea is that the user will enter their phone, but not their country like this:

Based on the "mobile_phone" information I want to assign their country automatically with this logic:

• WHERE left(mobile_phone,2) = '51' then 'PE'
• WHERE left(mobile_phone,2)='56' then 'CL'
• WHERE left(mobile_phone,2)='57' then 'CO'

And based on that logic the table should update AUTOMATICALLY with this information:

I was thinking to create a trigger like this, but the problem is that I don't know how to create the function that will update the column country:

CREATE TRIGGER update_country AFTER INSERT ON info
FOR EACH ROW EXECUTE PROCEDURE function_name()

Thanks in advance for your help.

CodePudding user response：

Since your records to be updated are already found within your database, you should first use an UPDATE statement to update your current rows. You can do it using a CASE expression that will assign the "country" value conditionally with respect to the "mobile_phone".

UPDATE "info"
SET country = CASE WHEN LEFT(mobile_phone,2) = '51' THEN 'PE'
                   WHEN LEFT(mobile_phone,2) = '56' THEN 'CL'
                   WHEN LEFT(mobile_phone,2) = '57' THEN 'CO' END;

Then create the corresponding trigger, that will update your record before the insertion. It splits into:

• the function "update_country()", which returns a trigger that uses NEW to access values from the inserting row and update its country value
• the trigger creation definition (which you included in the post as well), that uses "update_country()" to update records featured in the insertion statement.

CREATE OR REPLACE FUNCTION update_country()
RETURNS TRIGGER AS
'
BEGIN
    NEW.country = CASE WHEN LEFT(NEW.mobile_phone,2) = ''51'' THEN ''PE''
                       WHEN LEFT(NEW.mobile_phone,2) = ''56'' THEN ''CL''
                       WHEN LEFT(NEW.mobile_phone,2) = ''57'' THEN ''CO'' END;
    RETURN NEW;
END;
'
LANGUAGE plpgsql;

CREATE TRIGGER update_country_trigger
BEFORE INSERT ON "info"
FOR EACH ROW
EXECUTE PROCEDURE update_country();

Check the demo here.

CodePudding user response：

You can use Generated Columns. With Generated Columns you can not do
update info set country = 'x'; or
insert into info(country) value ('x');
https://www.postgresql.org/docs/current/ddl-generated-columns.html

BEGIN;
CREATE temp TABLE info (
    mobile_phone text
    , country text GENERATED ALWAYS AS ( CASE WHEN
    LEFT (mobile_phone , 2) = '51' THEN
        'PE'
    WHEN
    LEFT (mobile_phone , 2) = '52' THEN
        'XY'
    WHEN
    LEFT (mobile_phone , 2) = '53' THEN
        'AB'
    WHEN
    LEFT (mobile_phone , 2) = '54' THEN
        'CD'
    WHEN
    LEFT (mobile_phone , 2) = '55' THEN
        'EF'
    WHEN
    LEFT (mobile_phone , 2) = '56' THEN
        'CL'
    WHEN
    LEFT (mobile_phone , 2) = '57' THEN
        'CO'
    ELSE
        'NA'
    END) STORED
) ON COMMIT DROP;
INSERT INTO info (mobile_phone)
    VALUES ('51682815')
    , ('51261728')
    , ('56682815')
    , ('56261728')
    , ('57682815')
    , ('57261728');
TABLE info;
END;