From: "Otto Blomqvist" <o.blomqvist@secomintl.com>
Subject: Problems with Set Returning Functions (SRFs)
Date: Wed, 6 Apr 2005 11:53:45 -0700
Organization: Hub.Org Networking Services
Lines: 100
Message-ID: <d31b80$1qit$1@news.hub.org>
To: pgsql-general@postgresql.org, pgsql-sql@postgresql.org

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