List:General Discussion« Previous MessageNext Message »
From:sguazt sguazt Date:March 16 2005 4:54pm
Subject:RE: Queries inside UDF
View as plain text  
Hi!

>From: Tom Crimmins <mysql2@stripped>
>To: sguazt sguazt <sguazt@stripped>
>CC: mysql@stripped
>Subject: RE: Queries inside UDF
>Date: Wed, 16 Mar 2005 10:09:16 -0600
>
...
>
>Can you explain exactly what you are using this for? What benefit does this
>provide over just executing the query?
>
>You can either execute SELECT foobar() or SELECT COUNT(*) FROM tblfoobar,
>and
>you don't have to do anything to make the latter work. Creating the UDF 
>just
>
>seems like a lot of extra work. Maybe this is my ignorance, but I don't see
>much use for executing a query within a UDF esspecially if you are using 
>4.1
>
>with subqueries.

ok sorry if I submitted a stupid exampe :P
but I wanted to keep the stuff simple.
My real case is a little more complicated ...

First of all, unfornately I've to use MySQL 4.0.21.
The purpose of creating a UDF, in my case, is doing something that in my 
opinion cannot be done in a query.

For example, if you have to implement in SQL the following pseudo-code:

start = <a unix timestamp>;
duration = 100;
stop = null;
increment = 0;

while ( true ) {
  start2 = 0;
  stop2 = 0;

  stop = start + ( duration - increment );

  rs = doQuery( "SELECT start,stop FROM t1 INNER JOIN t2 ON t1.c1=t2.c1 .... 
WHERE t1.start>=" + start + " AND t1.stop<=" + stop );
  if ( rs == null ) {
    delta = 0;

    start2 = rs[0]['start'];
    stop2 = rs[0]['stop'];

    if ( start < start2 ) {
      delta += start2 - start;
    }
    increment += delta;
    start = stop2
  }
  else {
    break;
  }
}
return stop;

what do you write?

The main problem is that you can establish a-priori when you stop the main 
while ... :(
I thought to use a temporary table but the problem remains ...

Furthermore that code produces an info that is to be used by another (more 
complex) query.

So the only solutions are:
1. use a high-level language (C,perl,Java,PHP)  and implement the entire 
logic (not only the code above) in that language (but this seems to be too 
slow ... I did it before exploring the UDF world ;) )

2. try to write a UDF that implement the code above; so you create a UDF, 
e.g. named foobar, you can use inside other query: SELECT ...,foobar( start, 
duration ) AS stop FROM <a very long JOIN> ...

Any idea?

bye!!!

-- Marco

>--
>Tom Crimmins
>Interface Specialist
>Pottawattamie County, Iowa
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>

_________________________________________________________________
Ricerche online più semplici e veloci con MSN Toolbar! 
http://toolbar.msn.it/

Thread
Queries inside UDFsguazt sguazt16 Mar
RE: Queries inside UDFTom Crimmins16 Mar
  • RE: Queries inside UDFsguazt sguazt16 Mar
    • RE: Queries inside UDFSGreen16 Mar
      • RE: Queries inside UDFsguazt sguazt16 Mar
        • RE: Queries inside UDFSGreen16 Mar
RE: Queries inside UDFsguazt sguazt17 Mar
  • Re: Queries inside UDFPhilippe Poelvoorde17 Mar
    • Re: Queries inside UDFsguazt sguazt17 Mar
      • Re: Queries inside UDFPhilippe Poelvoorde21 Mar
        • Re: Queries inside UDFsguazt sguazt25 Mar