Solved

How to return a temporary table from a mysql5 function?

Posted on 2006-10-30
4
480 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
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 …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

15 Experts available now in Live!

Get 1:1 Help Now