Problems with Set Returning Functions (SRFs)

Helloo !

We have a database that contains data that we need to Parse.

Ideally I would like write a C-function, ParseData, and run

select ParseData([data_column]) from datatable where date='2005-05-05';

and have it return 5 columns with the parsed data. Each row in Data_column
will potentially create multiple output-rows.
I did some research and SRF seems to be the solution (?). After playing
around with the TestPassByVal example on the postgres

website (http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html) I'v
ran into troubles.

Here is the type definion

CREATE TYPE __testpassbyval AS (f1 integer, f2 integer, f3 integer);

CREATE OR REPLACE FUNCTION testpassbyval(integer, integer) RETURNS SETOF
__testpassbyval
AS 'filename', 'testpassbyval'
LANGUAGE C IMMUTABLE STRICT;

First paramter is the number of rows the function returns. Second Parameter
is the multiplier.

First we Try

secom=# select testpassbyval(2, 5);
testpassbyval
---------------
(5,10,15)
(5,10,15)
(2 rows)

Then we can extract the columns using

secom=# select f1, f2, f3 from testpassbyval(2, 5);
f1 | f2 | f3
----+----+----
5 | 10 | 15
5 | 10 | 15
(2 rows)

So far so good.

But What I want is to feed the testpassbyval function with data from a
column (data_column)

Creating a test table with column data_column having integers from 1 trew 9
we get

secom=# select testpassbyval(2, data_column) from datatable;
testpassbyval
---------------
(1,2,3)
(1,2,3)
(2,4,6)
(2,4,6)
(3,6,9)
(3,6,9)
(4,8,12)
(4,8,12)
(5,10,15)
(5,10,15)
(6,12,18)
(6,12,18)
(7,14,21)
(7,14,21)
(8,16,24)
(8,16,24)
(9,18,27)
(9,18,27)
(18 rows)

Looking good. Now I try to extract the columns

secom=# select f1, f2, f3 from testpassbyval(1, (Select number1 from test));
ERROR: more than one row returned by a subquery used as an expression

This is where I fail. Am I even on the right path here ? Writing the actual
parsing function will be easy once I have a working concept.

Any ideas ?

Thanks a lot

/Otto Blomqvist

I'm Running PSQL 8.0.0 on Linux 8.0