Re: coalesce in plpgsql, and other style questions

On Wed, 2012-06-13 at 09:02 -0500, Merlin Moncure wrote:
> On Tue, Jun 12, 2012 at 12:46 PM, Ross Boylan <ross@biostat.ucsf.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