Обсуждение: How to make a IN without a table... ?
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
> > 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
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);
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
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.
> > > 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
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
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
> > 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
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
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.
> > 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