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