Factorial using CTE in PostgreSQL

Not so long ago I used Common Table Expressions for Fibonacci Numbers calculation.

Today I had a conversation with one client about SQL in general and about PosgreSQL dialect in particular. We talked about SQL’s Turing completeness also. Well my opponent is sure that SQL (Postgres dialect either) is not Turing Complete. But I know for sure that if SQL supports CTE it is Turing Complete. Well, I’m sure about it because some time ago at Oscon 2009 David Fetter said so. And my confidence in this man is boundless. :)

Anyway, my client proposed to implement Factorial calculation on a pure SQL. I choose Postgres dialect. He agreed.

That was his first mistake! He didn’t know that Postgres has built in “!” and “!!” operators for this purpose. :)

But to be more convincing, I have wrote this code:

WITH RECURSIVE fact(i, f) AS (
    VALUES (2, 1)
UNION ALL
    SELECT i + 1, i * f FROM fact
)
SELECT f FROM fact LIMIT 10;
About these ads

Nerd.

Tagged with: , , , , ,
Posted in Coding, PostgreSQL
4 comments on “Factorial using CTE in PostgreSQL
  1. [...] Posted by pashagolub under Coding, PostgreSQL | Tags: CTE, factorial, PostgreSQL, SQL, trick |Leave a Comment  [...]

  2. Well done Pavel! You even did it tail-recursive :)

  3. [...] WITH RECURSIVE fact(i, f) AS ( VALUES (2, 1) UNION ALL SELECT i + 1, i * f FROM fact ) SELECT f FROM fact LIMIT 10; Filed under: Coding, PostgreSQL Tagged: CTE, factorial, oscon, PostgreSQL, SQL, trick    PostgreSQL Read the original post on Planet PostgreSQL… [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
Map of wandering
Follow

Get every new post delivered to your Inbox.

Join 83 other followers

%d bloggers like this: