Thread: How to make a IN without a table... ?

How to make a IN without a table... ?

From
David Pradier
Date:
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:

SELECTmy_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


Re: How to make a IN without a table... ?

From
Christoph Haller
Date:
>
> 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




Re: How to make a IN without a table... ?

From
Bruno Wolff III
Date:
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);
 


Re: How to make a IN without a table... ?

From
David Pradier
Date:
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


Re: How to make a IN without a table... ?

From
Bruno Wolff III
Date:
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.


Re: How to make a IN without a table... ?

From
Christoph Haller
Date:
> > > 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




Re: How to make a IN without a table... ?

From
David Pradier
Date:
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


Re: How to make a IN without a table... ?

From
David Pradier
Date:
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


Re: How to make a IN without a table... ?

From
Christoph Haller
Date:
>
> 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




Re: How to make a IN without a table... ?

From
David Pradier
Date:
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


Re: How to make a IN without a table... ?

From
"scott.marlowe"
Date:
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.



Re: How to make a IN without a table... ?

From
David Pradier
Date:
> > 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