pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Problem with looping on a table function result
2+ messages / 2 participants
[nested] [flat]

* Problem with looping on a table function result
@ 2003-07-31 22:32  Alexander M. Pravking <fduch@antar.bryansk.ru>
  0 siblings, 0 replies; 2+ messages in thread

From: Alexander M. Pravking @ 2003-07-31 22:32 UTC (permalink / raw)
  To: pgsql-sql

I've met the following problem.
I had successfully written a function divide_into_days(timestamp, timestamp)
which returns setof (timestamp, timestamp) pairs - a list of days the
given interval is divided into.

What I want is to use each record from resultset to pass to another
function, something like:

SELECT	days.*, summary_stats(days.day_start, days.day_end)
FROM	divide_into_days('2003-06-01', '2003-07-01') days;

The problem is that summary_stats function returns a record, so I have
to use SELECT * FROM summary_stats(...). I can't use the following too:

SELECT	*
FROM	summary_stats(days.day_start, days.day_end) stats,
	divide_into_days('2003-06-01', '2003-07-01') days;

(there was a discussion a few days ago about using subselects,
but here's a slightly different case).

I wonder if where's a way to do the trick without writing one more
PL/PgSQL table function doing FOR row IN SELECT ... LOOP or using
client-side loop?

Thanks for your help.

-- 
Fduch M. Pravking



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Problem with looping on a table function result
@ 2003-08-04 09:26  Christoph Haller <ch@rodos.fzk.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Christoph Haller @ 2003-08-04 09:26 UTC (permalink / raw)
  To: pgsql-sql; fduch@antar.bryansk.ru

>
> I've met the following problem.
> I had successfully written a function divide_into_days(timestamp,
timestamp)
> which returns setof (timestamp, timestamp) pairs - a list of days the
> given interval is divided into.
>
> What I want is to use each record from resultset to pass to another
> function, something like:
>
> SELECT        days.*, summary_stats(days.day_start, days.day_end)
> FROM  divide_into_days('2003-06-01', '2003-07-01') days;
>
> The problem is that summary_stats function returns a record, so I have

> to use SELECT * FROM summary_stats(...). I can't use the following
too:
>
> SELECT        *
> FROM  summary_stats(days.day_start, days.day_end) stats,
>       divide_into_days('2003-06-01', '2003-07-01') days;
>
> (there was a discussion a few days ago about using subselects,
> but here's a slightly different case).
>
> I wonder if where's a way to do the trick without writing one more
> PL/PgSQL table function doing FOR row IN SELECT ... LOOP or using
> client-side loop?
>
Did you see

http://techdocs.postgresql.org/guides/SetReturningFunctions

Regards, Christoph





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-08-04 09:26 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-31 22:32 Problem with looping on a table function result Alexander M. Pravking <fduch@antar.bryansk.ru>
2003-08-04 09:26 Re: Problem with looping on a table function result Christoph Haller <ch@rodos.fzk.de>

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