Database Reference
In-Depth Information
The previous code results in
3
as the output.
The highest Fibonacci number we can calculate with this function is 4785. If a value
of the parameter is larger than that, the result will not fit into the 1000 length decimal
we declared to return.
Just for the record, in the Fibonacci sequence each element in the sequence is the
sum of the previous 2 elements. Thus, the first few elements of the sequence should
be 0,1,1,2,3,5,8,13,21,34, and so on. There are a few PostgreSQL Fibonacci se-
quence functions out there on the interwebs, but they use the dreaded recursive
method. In this case recursion is a Bad Thing™.
In this function, we also introduced default values to the variables in the declarations
section. When the function is invoked, the variables will be initially set to these val-
ues.
Also take a quick gander at the statement
SELECT b,a+b INTO a,b
. This state-
ment makes two variable assignments at the same time. It avoids the use of a third
variable while acting on both
a
and
b
.
For some additional looping syntax, take a look at the PostgreSQL documentation
page
at
Looping through query results
Before we embark on this journey through query result loops, I think it is fair to warn
you that if you are using this method you are probably Doing It Wrong™. This is
one of the most processor and memory intensive operations that PostgreSQL offers.
There are exceedingly few reasons to iterate through a result set on the database
server that offset this cost. I would encourage you to think very hard about how to im-
plement the same idea using a function, values list in a query, temporary table, and
permanent table, or precompute the values in any way possible to avoid this opera-
tion. So, do you still think you have an overwhelming reason to use this technique?
Ok, read on.
The following is the simple version:
