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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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