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 | - - - - - - - - - - - - - - - - - - - - - - - - - -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
