Skip site navigation (1) Skip section navigation (2)

Re: coalesce in plpgsql, and other style questions

From: Ross Boylan <ross(at)biostat(dot)ucsf(dot)edu>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: ross(at)biostat(dot)ucsf(dot)edu, pgsql-novice(at)postgresql(dot)org
Subject: Re: coalesce in plpgsql, and other style questions
Date: 2012-06-13 18:34:25
Message-ID: 1339612465.5384.173.camel@corn.betterworld.us (view raw or flat)
Thread:
Lists: pgsql-novice
On Wed, 2012-06-13 at 09:02 -0500, Merlin Moncure wrote:
> On Tue, Jun 12, 2012 at 12:46 PM, Ross Boylan <ross(at)biostat(dot)ucsf(dot)edu> wrote:
> > I just wrote my first pl/pgsql function, and would appreciate any
> > comments people have on it.  I'll be writing a bunch of similar
> > functions, with semantics "give me the id of the object if exists,
> > otherwise create it and give me the id."
> >
> > My solution seems excessively procedural to me.  I thought I could get
> > the right semantics with something like
> > select coalesce((select id from mytable where name='foo'),
> >        (insert into mytable ('name') values('foo') returning id))
> > but I could not get that to work in plgsql.
> 
> for posterity:
> 
> with a as (select id from mytable where name='foo'),
> b as
> (
>   insert into mytable (name)
>     select 'foo' where not exists (select 1 from a)
>    returning id
> )
> select * from a union all select * from b;
Oh my!

Is that legal plpgsql code, or just regular (postgres) sql?

Also, what's CTE (below)?

Thanks.
Ross
> 
> I definitely appreciate the desire to avoid procedural implementations
> of things like this.  Just be advised that this is a postgresql-ism
> (data modifying 'with' is not standard syntax).   This also (as Jeff
> notes) has no bearing on the race to the id: you must be prepared to
> retry the above statement in face of concurrent attempts to insert to
> the same unique value  unless you have taken a lock to guard against
> this.  I don't think it's possible to work that lock into the CTE.
> 
> merlin


In response to

Responses

pgsql-novice by date

Next:From: Bruce MomjianDate: 2012-06-13 18:42:36
Subject: Re: Xen Open Source White Paper?
Previous:From: Merlin MoncureDate: 2012-06-13 14:02:22
Subject: Re: coalesce in plpgsql, and other style questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group