pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to make a IN without a table... ?
14+ messages / 4 participants
[nested] [flat]
* How to make a IN without a table... ?
@ 2003-06-04 14:59 David Pradier <dpradier@apartia.fr>
0 siblings, 1 reply; 14+ messages in thread
From: David Pradier @ 2003-06-04 14:59 UTC (permalink / raw)
To: pgsql-sql
Hi everybody,
i ran today in a problem when doing some (too much for me) advanced sql...
What i want to do is something like this:
SELECT
my_var1,
my_var2,
my_function(my_var1, my_var2)
FROM (
SELECT
'1',
'2',
'3',
'4'
) AS my_var1_values,
(
SELECT
'1',
'2',
'3',
'4'
) AS my_var2_values
In short, i want to calculate the result of the function my_function for
some values of my_var1, cross by some values of my_var2.
These values are not taken in a table, but put in directly.
They are a lot, so i would prefer not to write the whole thing, line
after line. (Let's say 10 values for the first, and 40 for the second =>
400 lines of code to maintain...)
I really don't see how to do this :-/
Any help is heartfully welcome,
David
--
dpradier@apartia.fr 01.46.47.21.33
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How to make a IN without a table... ?
@ 2003-06-08 11:11 Bruno Wolff III <bruno@wolff.to>
parent: David Pradier <dpradier@apartia.fr>
0 siblings, 0 replies; 14+ messages in thread
From: Bruno Wolff III @ 2003-06-08 11:11 UTC (permalink / raw)
To: pgsql-sql
On Wed, Jun 04, 2003 at 16:59:02 +0200,
David Pradier <dpradier@apartia.fr> wrote:
>
> In short, i want to calculate the result of the function my_function for
> some values of my_var1, cross by some values of my_var2.
> These values are not taken in a table, but put in directly.
> They are a lot, so i would prefer not to write the whole thing, line
> after line. (Let's say 10 values for the first, and 40 for the second =>
> 400 lines of code to maintain...)
>
> I really don't see how to do this :-/
You could programatically generate the sql code and use union
(e.g. (select 1 union select 2 union select 3 union select 4))
to join the rows together or you could write a set returning
function.
^ permalink raw reply [nested|flat] 14+ messages in thread
* How to make a IN without a table... ?
@ 2003-06-12 09:19 David Pradier <dpradier@apartia.fr>
0 siblings, 1 reply; 14+ messages in thread
From: David Pradier @ 2003-06-12 09:19 UTC (permalink / raw)
To: pgsql-sql
Hi everybody,
i ran today in a problem when doing some (i mean too much for me) advanced sql...
What i want to do is something like this:
SELECT
my_var1,
my_var2,
my_function(my_var1, my_var2)
FROM (
SELECT
'1',
'2',
'3',
'4'
) AS my_var1_values,
(
SELECT
'1',
'2',
'3',
'4'
) AS my_var2_values
In short, i want to calculate the result of the function my_function for
some values of my_var1, cross by some values of my_var2.
These values are not taken in a table, but put in directly.
They are a lot, so i would prefer not to write the whole thing, line
after line. (Let's say 10 values for the first, and 40 for the second =>
400 lines of code to maintain...)
I really don't see how to do this :-/
Any help is heartfully welcome,
David
--
dpradier@apartia.fr 01.46.47.21.33
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How to make a IN without a table... ?
@ 2003-06-12 11:16 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 14+ messages in thread
From: Christoph Haller @ 2003-06-12 11:16 UTC (permalink / raw)
To: pgsql-sql; dpradier@apartia.fr
>
> i ran today in a problem when doing some (i mean too much for me)
advanced sql...
>
> What i want to do is something like this:
>
> SELECT
> my_var1,
> my_var2,
> my_function(my_var1, my_var2)
> FROM (
> SELECT
> '1',
> '2',
> '3',
> '4'
> ) AS my_var1_values,
> (
> SELECT
> '1',
> '2',
> '3',
> '4'
> ) AS my_var2_values
>
> In short, i want to calculate the result of the function my_function
for
> some values of my_var1, cross by some values of my_var2.
> These values are not taken in a table, but put in directly.
> They are a lot, so i would prefer not to write the whole thing, line
> after line. (Let's say 10 values for the first, and 40 for the second
=>
> 400 lines of code to maintain...)
>
> I really don't see how to do this :-/
>
What about using a TEMP TABLE?
And COPY <temp-table> FROM STDIN offers a real fast way to populate.
BTW, why using SQL at all? Couldn't perl do the job much easier?
Regards, Christoph
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How to make a IN without a table... ?
@ 2003-06-12 11:49 Bruno Wolff III <bruno@wolff.to>
parent: David Pradier <dpradier@apartia.fr>
0 siblings, 0 replies; 14+ messages in thread
From: Bruno Wolff III @ 2003-06-12 11:49 UTC (permalink / raw)
To: pgsql-sql
On Thu, Jun 12, 2003 at 11:19:14 +0200,
David Pradier <dpradier@apartia.fr> wrote:
>
> In short, i want to calculate the result of the function my_function for
> some values of my_var1, cross by some values of my_var2.
> These values are not taken in a table, but put in directly.
> They are a lot, so i would prefer not to write the whole thing, line
> after line. (Let's say 10 values for the first, and 40 for the second =>
> 400 lines of code to maintain...)
If there aren't a huge number of values you can use union. (If there
are a large number then you probably want to load temp tables using
copy.) For example:
select myval1, myval2, f(myval1,myval2)
from
(select '1' union select '2' union select '3') as tab1 (myval1),
(select '1' union select '2' union select '3') as tab2 (myval2);
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How to make a IN without a table... ?
@ 2003-06-12 11:50 David Pradier <dpradier@apartia.fr>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 2 replies; 14+ messages in thread
From: David Pradier @ 2003-06-12 11:50 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
On Thu, Jun 12, 2003 at 01:16:27PM +0200, Christoph Haller wrote:
> >
> > i ran today in a problem when doing some (i mean too much for me)
> advanced sql...
> >
> > What i want to do is something like this:
> >
> > SELECT
> > my_var1,
> > my_var2,
> > my_function(my_var1, my_var2)
> > FROM (
> > SELECT
> > '1',
> > '2',
> > '3',
> > '4'
> > ) AS my_var1_values,
> > (
> > SELECT
> > '1',
> > '2',
> > '3',
> > '4'
> > ) AS my_var2_values
> >
> > In short, i want to calculate the result of the function my_function
> for
> > some values of my_var1, cross by some values of my_var2.
> > These values are not taken in a table, but put in directly.
> > They are a lot, so i would prefer not to write the whole thing, line
> > after line. (Let's say 10 values for the first, and 40 for the second
> =>
> > 400 lines of code to maintain...)
> >
> > I really don't see how to do this :-/
> >
> What about using a TEMP TABLE?
I avoid as hell to use temporary tables. This is part of a complex
database, with more than 250 different tables. So i don't even want to
think about adding temporary tables, brrr !
> And COPY <temp-table> FROM STDIN offers a real fast way to populate.
> BTW, why using SQL at all? Couldn't perl do the job much easier?
I guess i wasn't clear enough.
"my_function" here is a function which calculate some results about some
datas from the database.
Example : a function which would calculate the sum of the money (which
would be stored in the database by movement, ie amount + date + cause +
account) spent by user my_var1 during period my_var2.
So no, perl can't do this.
Regards, David
--
dpradier@apartia.fr 01.46.47.21.33
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How to make a IN without a table... ?
@ 2003-06-12 12:09 Bruno Wolff III <bruno@wolff.to>
parent: David Pradier <dpradier@apartia.fr>
1 sibling, 1 reply; 14+ messages in thread
From: Bruno Wolff III @ 2003-06-12 12:09 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
On Thu, Jun 12, 2003 at 13:50:27 +0200,
David Pradier <dpradier@apartia.fr> wrote:
>
> I guess i wasn't clear enough.
> "my_function" here is a function which calculate some results about some
> datas from the database.
> Example : a function which would calculate the sum of the money (which
> would be stored in the database by movement, ie amount + date + cause +
> account) spent by user my_var1 during period my_var2.
>
> So no, perl can't do this.
I think the idea was that perl could generate multiple query statements
for you. This would be slower than doing as one query, but if the database
is local there wouldn't be much difference.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How to make a IN without a table... ?
@ 2003-06-12 12:10 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 14+ messages in thread
From: Christoph Haller @ 2003-06-12 12:10 UTC (permalink / raw)
To: pgsql-sql; dpradier@apartia.fr
> > > These values are not taken in a table, but put in directly.
>
> I guess i wasn't clear enough.
> "my_function" here is a function which calculate some results about
some
> datas from the database.
> Example : a function which would calculate the sum of the money (which
> would be stored in the database by movement, ie amount + date + cause
+
> account) spent by user my_var1 during period my_var2.
>
I am not sure I'm getting it right.
The first statement suggests the data is coming from outside the
database.
The second suggests it is derived from the database though.
And now it sounds like you could use table functions.
Can you confirm that?
Regards, Christoph
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How to make a IN without a table... ?
@ 2003-06-12 12:21 David Pradier <dpradier@apartia.fr>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 14+ messages in thread
From: David Pradier @ 2003-06-12 12:21 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
On Thu, Jun 12, 2003 at 07:09:29AM -0500, Bruno Wolff III wrote:
> On Thu, Jun 12, 2003 at 13:50:27 +0200,
> David Pradier <dpradier@apartia.fr> wrote:
> >
> > I guess i wasn't clear enough.
> > "my_function" here is a function which calculate some results about some
> > datas from the database.
> > Example : a function which would calculate the sum of the money (which
> > would be stored in the database by movement, ie amount + date + cause +
> > account) spent by user my_var1 during period my_var2.
> >
> > So no, perl can't do this.
>
> I think the idea was that perl could generate multiple query statements
> for you. This would be slower than doing as one query, but if the database
> is local there wouldn't be much difference.
Hmmm yes...
I'll try this too, good idea :-)
David
--
dpradier@apartia.fr 01.46.47.21.33
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How to make a IN without a table... ?
@ 2003-06-12 12:24 David Pradier <dpradier@apartia.fr>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 14+ messages in thread
From: David Pradier @ 2003-06-12 12:24 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
On Thu, Jun 12, 2003 at 02:10:06PM +0200, Christoph Haller wrote:
> > > > These values are not taken in a table, but put in directly.
> >
> > I guess i wasn't clear enough.
> > "my_function" here is a function which calculate some results about
> some
> > datas from the database.
> > Example : a function which would calculate the sum of the money (which
>
> > would be stored in the database by movement, ie amount + date + cause
> +
> > account) spent by user my_var1 during period my_var2.
> >
> I am not sure I'm getting it right.
> The first statement suggests the data is coming from outside the
> database.
> The second suggests it is derived from the database though.
> And now it sounds like you could use table functions.
> Can you confirm that?
>
> Regards, Christoph
The arguments of the functions come from outside the database, but the
function itself uses the datas in the database.
Like "Tell me how much i spent between the 4th and the 7th og this month
?" uses 4 and 7 as arguments, but need to fetch the datas in the
database.
Clear enough ? :-)
What do you mean by table function ?
Regards, David
--
dpradier@apartia.fr 01.46.47.21.33
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How to make a IN without a table... ?
@ 2003-06-12 12:37 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 14+ messages in thread
From: Christoph Haller @ 2003-06-12 12:37 UTC (permalink / raw)
To: pgsql-sql; dpradier@apartia.fr
>
> Like "Tell me how much i spent between the 4th and the 7th og this
month
> ?" uses 4 and 7 as arguments, but need to fetch the datas in the
> database.
> Clear enough ? :-)
Yes.
>
> What do you mean by table function ?
Table functions are aka SetReturningFunctions.
Refer to
http://techdocs.postgresql.org/guides/SetReturningFunctions
Regards, Christoph
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How to make a IN without a table... ?
@ 2003-06-12 12:49 David Pradier <dpradier@apartia.fr>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 14+ messages in thread
From: David Pradier @ 2003-06-12 12:49 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
On Thu, Jun 12, 2003 at 02:37:00PM +0200, Christoph Haller wrote:
> >
> > Like "Tell me how much i spent between the 4th and the 7th og this
> month
> > ?" uses 4 and 7 as arguments, but need to fetch the datas in the
> > database.
> > Clear enough ? :-)
> Yes.
> >
> > What do you mean by table function ?
>
> Table functions are aka SetReturningFunctions.
> Refer to
> http://techdocs.postgresql.org/guides/SetReturningFunctions
Very interesting, i'll dig into it.
Thanks a lot, Christoph.
--
dpradier@apartia.fr 01.46.47.21.33
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How to make a IN without a table... ?
@ 2003-06-13 21:04 scott.marlowe <scott.marlowe@ihs.com>
parent: David Pradier <dpradier@apartia.fr>
1 sibling, 1 reply; 14+ messages in thread
From: scott.marlowe @ 2003-06-13 21:04 UTC (permalink / raw)
To: David Pradier <dpradier@apartia.fr>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
On Thu, 12 Jun 2003, David Pradier wrote:
> On Thu, Jun 12, 2003 at 01:16:27PM +0200, Christoph Haller wrote:
> > >
> > > i ran today in a problem when doing some (i mean too much for me)
> > advanced sql...
> > >
> > > What i want to do is something like this:
> > >
> > > SELECT
> > > my_var1,
> > > my_var2,
> > > my_function(my_var1, my_var2)
> > > FROM (
> > > SELECT
> > > '1',
> > > '2',
> > > '3',
> > > '4'
> > > ) AS my_var1_values,
> > > (
> > > SELECT
> > > '1',
> > > '2',
> > > '3',
> > > '4'
> > > ) AS my_var2_values
> > >
> > > In short, i want to calculate the result of the function my_function
> > for
> > > some values of my_var1, cross by some values of my_var2.
> > > These values are not taken in a table, but put in directly.
> > > They are a lot, so i would prefer not to write the whole thing, line
> > > after line. (Let's say 10 values for the first, and 40 for the second
> > =>
> > > 400 lines of code to maintain...)
> > >
> > > I really don't see how to do this :-/
> > >
> > What about using a TEMP TABLE?
>
> I avoid as hell to use temporary tables. This is part of a complex
> database, with more than 250 different tables. So i don't even want to
> think about adding temporary tables, brrr !
Just FYI, temporary tables in pgsql are invisible to other connections
even if they have the same name, and are auto-dropped when the connection
is dropped.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How to make a IN without a table... ?
@ 2003-06-16 07:27 David Pradier <dpradier@apartia.fr>
parent: scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 0 replies; 14+ messages in thread
From: David Pradier @ 2003-06-16 07:27 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
> > I avoid as hell to use temporary tables. This is part of a complex
> > database, with more than 250 different tables. So i don't even want to
> > think about adding temporary tables, brrr !
>
> Just FYI, temporary tables in pgsql are invisible to other connections
> even if they have the same name, and are auto-dropped when the connection
> is dropped.
Well, i didn't know this.
I will check how it works.
Thanks a lot, Scott.
--
dpradier@apartia.fr 01.46.47.21.33
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2003-06-16 07:27 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-04 14:59 How to make a IN without a table... ? David Pradier <dpradier@apartia.fr>
2003-06-08 11:11 ` Bruno Wolff III <bruno@wolff.to>
2003-06-12 09:19 How to make a IN without a table... ? David Pradier <dpradier@apartia.fr>
2003-06-12 11:49 ` Bruno Wolff III <bruno@wolff.to>
2003-06-12 11:16 Re: How to make a IN without a table... ? Christoph Haller <ch@rodos.fzk.de>
2003-06-12 11:50 ` David Pradier <dpradier@apartia.fr>
2003-06-12 12:09 ` Bruno Wolff III <bruno@wolff.to>
2003-06-12 12:21 ` David Pradier <dpradier@apartia.fr>
2003-06-13 21:04 ` scott.marlowe <scott.marlowe@ihs.com>
2003-06-16 07:27 ` David Pradier <dpradier@apartia.fr>
2003-06-12 12:10 Re: How to make a IN without a table... ? Christoph Haller <ch@rodos.fzk.de>
2003-06-12 12:24 ` David Pradier <dpradier@apartia.fr>
2003-06-12 12:37 Re: How to make a IN without a table... ? Christoph Haller <ch@rodos.fzk.de>
2003-06-12 12:49 ` David Pradier <dpradier@apartia.fr>
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