On Wed, Jun 13, 2012 at 1:34 PM, Ross Boylan <ross@biostat.ucsf.edu> wrote:
> On Wed, 2012-06-13 at 09:02 -0500, Merlin Moncure wrote:
>> > 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?
It is both (but only in 9.1+, sorry!). It's plain SQL, so is
acceptable in any place sql is allowed -- directly from the client,
sql functions, plpgsql functions, etc. The ability to chain
'returning' into other queries via 'with' was a new feature which we
call 'data modifying with' added as of postgresql 9.1. Vanilla CTEs
aka common table exrpressions aka WITH statements -- were added in
8.4 but you can only use them with select statements.
Aside: I encourage you to continue with pl/pgsql. It's the secret sauce.
merlin