pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblems with Set Returning Functions (SRFs)
5+ messages / 4 participants
[nested] [flat]
* Problems with Set Returning Functions (SRFs)
@ 2005-04-06 18:53 Otto Blomqvist <o.blomqvist@secomintl.com>
2005-04-06 20:30 ` Re: Problems with Set Returning Functions (SRFs) Tom Lane <tgl@sss.pgh.pa.us>
2005-04-11 10:07 ` Re: [SQL] Problems with Set Returning Functions (SRFs) Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 2 replies; 5+ messages in thread
From: Otto Blomqvist @ 2005-04-06 18:53 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problems with Set Returning Functions (SRFs)
2005-04-06 18:53 Problems with Set Returning Functions (SRFs) Otto Blomqvist <o.blomqvist@secomintl.com>
@ 2005-04-06 20:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-06 22:31 ` Re: [GENERAL] Problems with Set Returning Functions (SRFs) Otto Blomqvist <o.blomqvist@secomintl.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2005-04-06 20:30 UTC (permalink / raw)
To: Otto Blomqvist <o.blomqvist@secomintl.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql
"Otto Blomqvist" <o.blomqvist@secomintl.com> writes:
> 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
In 8.0 I think it'd work to do
select (x).f1, (x).f2, (x).f3 from
(select testpassbyval(1, number1) as x from test) ss;
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] Problems with Set Returning Functions (SRFs)
2005-04-06 18:53 Problems with Set Returning Functions (SRFs) Otto Blomqvist <o.blomqvist@secomintl.com>
2005-04-06 20:30 ` Re: Problems with Set Returning Functions (SRFs) Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-04-06 22:31 ` Otto Blomqvist <o.blomqvist@secomintl.com>
2005-04-11 02:06 ` Re: [GENERAL] Problems with Set Returning Functions (SRFs) Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 5+ messages in thread
From: Otto Blomqvist @ 2005-04-06 22:31 UTC (permalink / raw)
To: pgsql-sql
Ofcourse this works perfectly !
Thanks a lot Tom !
Just curious, is this (x)-"trick" in the postgres manual somewhere ? Just
just common SQL guru knowledge ? ;)
/Otto Blomqvist
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:5852.1112819444@sss.pgh.pa.us...
> "Otto Blomqvist" <o.blomqvist@secomintl.com> writes:
> > 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
>
> In 8.0 I think it'd work to do
>
> select (x).f1, (x).f2, (x).f3 from
> (select testpassbyval(1, number1) as x from test) ss;
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] Problems with Set Returning Functions (SRFs)
2005-04-06 18:53 Problems with Set Returning Functions (SRFs) Otto Blomqvist <o.blomqvist@secomintl.com>
2005-04-06 20:30 ` Re: Problems with Set Returning Functions (SRFs) Tom Lane <tgl@sss.pgh.pa.us>
2005-04-06 22:31 ` Re: [GENERAL] Problems with Set Returning Functions (SRFs) Otto Blomqvist <o.blomqvist@secomintl.com>
@ 2005-04-11 02:06 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Fuhr @ 2005-04-11 02:06 UTC (permalink / raw)
To: Otto Blomqvist <o.blomqvist@secomintl.com>; +Cc: pgsql-sql
On Wed, Apr 06, 2005 at 03:31:45PM -0700, Otto Blomqvist wrote:
>
> Just curious, is this (x)-"trick" in the postgres manual somewhere ? Just
> just common SQL guru knowledge ? ;)
I think the relevant documentation is "Field Selection" in the
"Value Expressions" section of the "SQL Syntax" chapter.
http://www.postgresql.org/docs/8.0/interactive/sql-expressions.html#AEN1642
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Problems with Set Returning Functions (SRFs)
2005-04-06 18:53 Problems with Set Returning Functions (SRFs) Otto Blomqvist <o.blomqvist@secomintl.com>
@ 2005-04-11 10:07 ` Sean Davis <sdavis2@mail.nih.gov>
1 sibling, 0 replies; 5+ messages in thread
From: Sean Davis @ 2005-04-11 10:07 UTC (permalink / raw)
To: Otto Blomqvist <o.blomqvist@secomintl.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql
On Apr 6, 2005, at 2:53 PM, Otto Blomqvist wrote:
>
> 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.
>
How about (untested):
select f1, f2, f3 from (
select testpassbyval(1,(
select number1 from test)
)
) a;
Sean
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-04-11 10:07 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-06 18:53 Problems with Set Returning Functions (SRFs) Otto Blomqvist <o.blomqvist@secomintl.com>
2005-04-06 20:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-06 22:31 ` Otto Blomqvist <o.blomqvist@secomintl.com>
2005-04-11 02:06 ` Michael Fuhr <mike@fuhr.org>
2005-04-11 10:07 ` Sean Davis <sdavis2@mail.nih.gov>
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