Solved

How to return a temporary table from a mysql5 function?

Posted on 2006-10-30
4
485 Views
Last Modified: 2008-01-09
Hi,

I have a simple requirement.

I want to write an mysql5 function that creates a temporary table and returns the contents of the temporary table. In the past I have seen this kind of thing done in sybase and with the advent of stored function support in mysql5 expected to be able to do it there.

However I cannot find any examples of doing this in a mysql5 function. I have experimented with the syntax of the function but keep getting errors when I try and save it using navicat mysql5 tool?

Is it possible to accomplish what I want in mysql5? If so I would appreciate a very simple example showing the correct syntax of a working function.

regards

Paul
0
Comment
Question by:ucacppw
  • 3
4 Comments
 
LVL 14

Expert Comment

by:racek
ID: 17833853
Statements that return a result set cannot be used within a stored function. This includes SELECT  statements that do not use INTO to fetch column values into variables, SHOW  statements, and other statements such as EXPLAIN. For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs (ER_SP_NO_RETSET_IN_FUNC). For statements that can be determined only at runtime to return a result set, a PROCEDURE %s can't return a result set in the given context error occurs (ER_SP_BADSELECT).

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
0
 

Author Comment

by:ucacppw
ID: 17834398
This suggests to me that in mysql5 it is not possible to create a function with "RETURNS TABLE(.....)" in its definition. Is that your understanding as well?
0
 
LVL 14

Accepted Solution

by:
racek earned 500 total points
ID: 17834461
YES :-(
0
 
LVL 14

Expert Comment

by:racek
ID: 17834471
... but if you can select from temp table after executing strored procedure or function
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now