I've seen other people with this question and today it hit me full force
because I need to expand a string and then return all the results.
the problem is that string parsing is best done in python, but as far as I
could figure out, python only allows 1 result returned.
The problem:
I have data in my system such as - Am29LV320M[T|B][120|90]EI(T|V)
this is shortcut data, with [] meaning one of the following is required and
( ) meaning that any of those are optional.
That datum Am29LV320M[T|B][120|90]EI(T|V) is therefore equal to the
following:
Am29LV320MT120EIT
Am29LV320MT120EIV
Am29LV320MT120EI
Am29LV320MB120EIT
Am29LV320MB120EIV
Am29LV320MB120EI
Am29LV320MT90EIT
Am29LV320MT90EIV
Am29LV320MT90EI
Am29LV320MB90EIT
Am29LV320MB90EIV
Am29LV320MB90EI
and obviously it can get much larger because you can have x number of
options and choices in the options.
This kind of parsing can't be easily done by plpgsql, so I wrote it in
plPython. Python breaks it up very nicely, the problem is now I have a
resultset and nothing to do with it.
Answer:
Temporary Tables. While I don't know if this is a feature by design or not
(I can hear the argument both ways), if you create a temp table in the
python function, it is accessible to the entire session, including any
functions that called it. therefore my solution in this case is to create a
temp table and populate it in the python function and return a useless
value. The calling function can then access the temp table and return any
kind of resultset it wants.