0

I need to update and/or insert in a Postgres DB, a String value with letters and numbers, which must be incremented of +1 (through php strings functions) everytime there is an UPDATE.

I need to LOCK this table, in order for the php to complete its flow in inserting or updating it avoiding others that open the page to receive the same result of the SELECT.

The second arrived, would wait for the first to finish.

The second arrived, will ALWAYS make an UPDATE. This update could generate a FILENAME0023, if there were 22 updates after the first insertion.

There could be more connections at the same time, I need to reserve the first result of the SELECT for the first one who connected to this php page.

The flow would be:

LOCK
    SELECT column FROM table WHERE column = 'FILENAME0001';

    IF NOT EXIST { INSERT INTO table (column) VALUES ($my_column); }
    ELSE { UPDATE table SET column = '$my_new_column' WHERE column = '$my_column'; }
UNLOCK

The variable $my_new_column is a SUBSTR php function that would cut the number part of the string and will then be incremented of +1.

This link is helping me a lot: THIS. But it does not contain everything.

I also tried working with stored procedure: LINK But I should work on the php when it is an update because I cannot increment a DB value like shown HERE, because I do not have a INT but a string and I can not change this

Anyone who can help me? I'd like to share my code, but believe me I'd rather start fresh, all the codes I tried are leading to nowhere.

Community
  • 1
  • 1
aPugLife
  • 989
  • 2
  • 14
  • 25

1 Answers1

0

you should try to use 

`INSERT ON ON DUPLICATE KEY UPDATE`

Here link for more example about it

Evinn
  • 153
  • 1
  • 11
  • I am in version 9.4 of postgre (not mysql) and do not have this command. I might create a function for that, but the value must be increased from PHP because is a string that contains numbers. Besides, updating is quite simple, it is the locking that is giving me issue in developing it – aPugLife Dec 14 '16 at 12:28
  • this is mysql syntax not postgresql – e4c5 Dec 15 '16 at 06:49