On 5/16/19 7:10 AM, Winanjaya Amijoyo wrote:
Hi David,
thanks for your advise, as I am new with postgresql..
I try to use LOCK as below, but it does not returning pid?
what I missed?
I'm not sure which pid you are referring to, the INSERT or UPDATE or both?
Can you show the output of the query?
BEGIN TRANSACTION;
LOCK TABLE test IN ACCESS EXCLUSIVE MODE;
WITH s AS (
SELECT pid FROM test WHERE area = 'test4'
), i AS (
INSERT INTO test (area)
SELECT 'test4'
WHERE NOT EXISTS (SELECT 1 FROM s)
RETURNING pid
)
UPDATE area
SET last_update = CURRENT_TIMESTAMP
WHERE pid = (SELECT pid FROM s UNION SELECT pid FROM i)
RETURNING pid;
COMMIT TRANSACTION;
--
Adrian Klaver
adrian.kla...@aklaver.com
