Re: coalesce in plpgsql, and other style questions

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;

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