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;
Is that legal plpgsql code, or just regular (postgres) sql?
Also, what's CTE (below)?
> 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.
In response to
pgsql-novice by date
|Next:||From: Bruce Momjian||Date: 2012-06-13 18:42:36|
|Subject: Re: Xen Open Source White Paper?|
|Previous:||From: Merlin Moncure||Date: 2012-06-13 14:02:22|
|Subject: Re: coalesce in plpgsql, and other style questions|