Re: coalesce in plpgsql, and other style questions

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