pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpassing a multiple join to a function?
3+ messages / 2 participants
[nested] [flat]
* passing a multiple join to a function?
@ 2007-12-17 13:06 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 1 reply; 3+ messages in thread
From: Louis-David Mitterrand @ 2007-12-17 13:06 UTC (permalink / raw)
To: pgsql-sql
Hi,
I've got this ugly case statement that I'd like to hide in a function:
select
...
case when
d.start_date <= CURRENT_DATE and
case when w.show_type in ('cinéma','livre')
then d.start_date >= CURRENT_DATE - 21
else (d.end_date >= CURRENT_DATE or d.end_date is null) end
then '0_actualite'
when
d.start_date > CURRENT_DATE
then '1_agenda'
else '2_archive'
end
as timing
...
from story s
join show w on (s.id_show = w.id_show)
join show_date d on (d.id_show = w.id_show and
d.start_date = (select d2.start_date from show_date d2 where d2.id_show = w.id_show order by d2.end_date >= CURRENT_DATE desc,
d2.start_date limit 1)
)
...
I could very well create a show_timing(int) function that accepts an
id_show and performs its own, additional, multiple join complex query on
story, show_date, and show.
Is there a way of feeding enough data to the function to avoid another
query?
Thanks,
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: passing a multiple join to a function?
@ 2007-12-17 17:27 Rodrigo De León <rdeleonp@gmail.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 1 reply; 3+ messages in thread
From: Rodrigo De León @ 2007-12-17 17:27 UTC (permalink / raw)
To: pgsql-sql
On 12/17/07, Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote:
> I've got this ugly case statement that I'd like to hide in a function:
Why don't you hide the entire query in a VIEW?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: passing a multiple join to a function?
@ 2007-12-23 16:37 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Louis-David Mitterrand @ 2007-12-23 16:37 UTC (permalink / raw)
To: pgsql-sql
On Mon, Dec 17, 2007 at 12:27:34PM -0500, Rodrigo De León wrote:
> On 12/17/07, Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote:
> > I've got this ugly case statement that I'd like to hide in a function:
>
> Why don't you hide the entire query in a VIEW?
That is probably the best solution. Thanks
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-12-23 16:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-17 13:06 passing a multiple join to a function? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-12-17 17:27 ` Rodrigo De León <rdeleonp@gmail.com>
2007-12-23 16:37 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
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