Solved

How to return a temporary table from a mysql5 function?

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

790 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