Re: Returning empty on insert

and yes .. I need both inserted pid and updated pid

On Thu, May 16, 2019 at 9:25 PM Winanjaya Amijoyo <
winanjaya(dot)amijoyo(at)gmail(dot)com> wrote:

> see enclosed screenshot..
>
> I thought, the record still locked that's why it returns empty..
>
> On Thu, May 16, 2019 at 9:21 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
>> 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(dot)klaver(at)aklaver(dot)com
>>
>