From: Otto Blomqvist <o.blomqvist@secomintl.com>
To: pgsql-general@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Problems with Set Returning Functions (SRFs)
Date: Wed, 6 Apr 2005 11:53:45 -0700
Message-ID: <d31b80$1qit$1@news.hub.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: o.blomqvist@secomintl.com, pgsql-general@postgresql.org
Subject: Re: Problems with Set Returning Functions (SRFs)
In-Reply-To: <d31b80$1qit$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox