You didn't explain how you intend to use this function, but I'll take
a guess. Here's a different approach:
A postgresql function can be declared with the "immutable" attribute:
"IMMUTABLE indicates that the function always returns the same result
when given the same argument values; that is, it does not do database
lookups or otherwise use information not directly present in its
argument list. If this option is given, any call of the function with
all-constant arguments can be immediately replaced with the function
value."
If your computationally_demanding_function matches these constraints,
then you need do nothing but create your function as immutable, e.g.:
CREATE FUNCTION computationally_demanding_function(int) returns int as '
dohardstuff
' LANGUAGE whateverlanguage IMMUTABLE;
I'm sorry you don't like the manual, but it *is* the definitive source:
http://www.postgresql.org/docs/8.1/interactive/sql-createfunction.html
If, alternatively, you want to do your own hacking: Postgresql
functions, at least in some procedural languages, can store "global"
data, i.e. data that persists across function calls, for the length of
your session.
So, suppose I do(in python/pl, since that's what I know well; similar
facilities are available in perl/pl and TCL/pl, but not in pgsql/pl):
create or replace function foo(int) returns int as '
def computationally_demanding_function(x):
y = do_hard_stuff();
return y
x = args[0]
if SD.has_key(x):
return SD[x]
else:
y = computationally_demanding_function(x)
SD[x] = y
return y
' LANGUAGE plpythonu;
Here, the first time foo is called with argument 34, it will perform
the long computation. Thereafter, in this session, it will just used
the cached value of y. For each *different* argument, it will
calculate computationally_demanding_function only once.
E.g.:
select name, location, shortest_route(location) from stations;
if shortest_route takes a lot of computation, and many rows of "stations"
have the same location.
Here's the doc for the python procedural language:
http://www.postgresql.org/docs/8.1/interactive/plpython.html
I hope this helps.
-- George Young
On Thu, 9 Mar 2006 13:37:30 -0500 (EST)
<kynn@panix.com> threw this fish to the penguins:
> Is there a way to pass a relation (or view) as one of the arguments to
> a stored procedure? Consider the following pseudo-code:
>
> function foo ( table T, int X ) {
> int Y = computationally_demanding_function( X );
> foreach record R in T {
> insert some_other_function( R, Y ) in RESULTS;
> }
> return RESULTS;
> }
>
> Since the computation of Y is time-consuming, I don't want to repeat
> it for every record in the input table T. This rules out an
> implementation in which foo takes a single record as its first
> argument, and is applied to each record in some input table through a
> SELECT statement.
>
> Alternatively, one could define foo to take, as arguments, a single
> record and a precomputed Y = computationally_demanding_function( X ),
> but this requires users to know about
> computationally_demanding_function, which may be completely extraneous
> to their needs. All they know is that they have a relation T and a
> parameter X, and want to perform a particular operation, which depends
> expensively on X, on each record of T.
>
> I'd find it surprising that procedures for a RDBMS would not accept
> relations as parameters, but I can't find any info on this.
>
> I also know about arrays, but it seems like working with arrays is a
> real bear. Just getting documentation on them is already a major
> undertaking. I've spent about one hour trying to find out how one
> iterates over an array if one doesn't know its length beforehand (or
> how to determine its length in the first place). I don't even yet
> know how to initialize an array from the rows of a 1-column table; I
> imagine finding the answer to that will be another multi-hour
> assignment. These are the most basic questions I can imagine for an
> array data structure, but finding answers for them appears to be
> *insanely difficult*. What am I missing?
>
> Thanks!
>
> kj
>
> P.S. Practically every time I post to this list I ask for good sources
> of documentation (because obviously the ones I use are terrible).
> Invariably I am referred to the online PostgreSQL documentation, but
> as the questions above show, the online documentation is not working
> for me. I guess I refuse to believe that questions as elementary and
> fundamental as mine are not documented anywhere...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
