The writings of Merlin Moncure, professional database developer, about work, life, family, and everything else.

Sunday, December 16, 2007

12 Days of Christmas, PostgreSQL style

In case you have trouble remembering the lyrics...

select v || case
when v=1 then ' partridge in a pear tree'
when v=2 then ' turtle doves'
when v=3 then ' french hens'
when v=4 then ' calling birds'
when v=5 then ' GO-O-OLDEN RINGS!'
when v=6 then ' swans a swimming'
when v=7 then ' geese a laying'
when v=8 then ' maids a milking'
when v=9 then ' ladies dancing'
when v=10 then ' lords a leaping'
when v=11 then ' pipers piping'
when v=12 then ' drummers drumming'
end from
(select generate_series(generate_series(1,12), 1, -1) as v) q;

Happy Holidays!

[update: now the lyrics are in the right order for day #2 and beyond...also decided to correctly spell 'pear' against my better judgement]


Gurjeet said...

Wow!!! This is twisted code!!! I didn't know you could pass a set-result as a parameter to a function!

I am going to try this with user-defined scalar functions..

Postgres amazes me time and again!!! I swear..

Tom Lane said...

er ... "pear tree"

Einhverfr said...

you may find it useful to reverse the sequence for each day...


(select generate_series(generate_series(1,12), 1, -1) as v) q

Merlin Moncure said...

right...I should have thought of that.

davidfetter said...
This comment has been removed by a blog administrator.
Merlin Moncure said...


I am deleting your comment from my blog. Language like that is completely inappropriate here, especially in the context of Christmas. If you'd like to discuss politics here in the comments, that's fine, but please do so politely.

davidfetter said...


That you're supporting a guy who cuddles up to Klansmen and censoring comments designed to inform people about him tells me a lot about you. Nothing good, I'm sorry to say. Put it back.

Merlin Moncure said...

[corrected to clarify an important issue]
David, you are welcome to post on my blog on whatever topic you like, including politics. I'm considering a new post to do exactly that, and am only reluctant to do so because this feed is picked up by planetpostgresql.

Your comment wasn't deleted because I disagree (although I do strongly), but because of the language. You are welcome to repost your comment in full, minus the n-word (in fairness to David, this was not used in a derogatory way, but a slam in racist attitudes).

Also, I am a little dismayed that, in addition to having hijacked a thread about Christmas into a topic about racist politics, you have called my character in question. I respect you professionally and don't think what you did was mean spirited in the least, but what's the deal? What exactly are you trying to accomplish here?

me said...

Instead of WHEN v=blah, you should do


Merlin Moncure said...

you are correct -- hm. have we met?