?
Solved

How to return a temporary table from a mysql5 function?

Posted on 2006-10-30
4
Medium Priority
?
495 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

801 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