From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Ross Boylan <ross(at)biostat(dot)ucsf(dot)edu> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: coalesce in plpgsql, and other style questions |
Date: | 2012-06-13 18:54:32 |
Message-ID: | CAHyXU0z-T9gnMNe4ZnSO7=118epFJXWgjdaoMEMO5m1sBjKebQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Wed, Jun 13, 2012 at 1:34 PM, Ross Boylan <ross(at)biostat(dot)ucsf(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2012-06-13 19:10:18 | Re: coalesce in plpgsql, and other style questions |
Previous Message | Bruce Momjian | 2012-06-13 18:43:10 | Re: Xen Open Source White Paper? |