Alvaro,
I followed your advice, but using PL/Python. I succeeded, but only with
great difficulty.
To close this off, I'll write these down, together with the work-arounds.
Some of this info would be of use if it were in the documentation.
Problems
--------
1) (documentation)
The doc says Python functions can't be called directly from Python!
But more accurately, PostgreSQL functions written in Python can't be
called from Python.
2) Tragically, the plpy.execute() function flattens composite fields to
string. In my case a work-around was simple. For others, this will
be a killer.
3) PL/Python doesn't permit functions that return RECORD or SETOF RECORD.
But I want to return a generic table... (or do I?)
Work-arounds
------------
1) Python is one of those languages that allow functions to be defined
within the body of another function.
So within the body of a PostgreSQL function definition, you can build a
little Python environment containing everything you would expect:
functions, classes, etc, and proceed to do serious programming.
One can basically treat the body of a PL/Python function as an individual
Python script, with the 'plpy' module already included.
2) I wrote about this in a different posting, that nobody has yet answered.
In my case, I was able to parse the string without too much trouble.
But this is really a crying shame.
3a) So PL/Python doesn't return SETOF RECORD. The trick I implemented was
to call it from a PL/pgSQL which created a temp table, which was filled
by the Python. This function in turn could return the set of generic
records using RETURN NEXT.
It would be nice PL/Python could return RECORD--I don't know why it
shouldn't.
3b) In my case though, It didn't matter. I ended up needing functions that
returned tables of a set of known types anyway. But the set is large, and
unfortunately, there is no way to pass the return type of a function as a
parameter. It has to be known at create time.
The solution was, to write a function that did a CREATE FUNCTION to create
a function of the desired types. This way for each known table, only one
SELECT needs to be done, to create the needed function for that table.
Thanks!
--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| e-Science / AstroGrid-D Zi. 35 Bg. 20
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -
