Re: Returning empty on insert

On 5/16/19 7:26 AM, Winanjaya Amijoyo wrote:
> and yes .. I need both inserted pid and updated pid

The INSERT pid is going to be 'swallowed' by the CTE that is why the:

SELECT pid FROM s UNION SELECT pid FROM i

Which also means the UPDATE RETURNING pid will be equal to it.

>
> On Thu, May 16, 2019 at 9:25 PM Winanjaya Amijoyo
> <winanjaya(dot)amijoyo(at)gmail(dot)com <mailto: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 <mailto: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 <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com