MySQL: create function that returns an array

Question

CREATE Function getNearIds (myLocationLon double, myLocationLat double) RETURNS WHAT

I just want to return an array of integers, after a long search: some say use Group_concat to make the list as a comma separated list then use find_in_set when calling the function. this works ok but in HQL (Hibernate query language) it doesn't.

so I need a clear way for this function to return an array of integers, what if i want to return a list of items from items table, is this possible.

I think this is very simple to do in Oracle DB or even SQL server. why is it very tough in MySQL.

please help.

marc_s · Accepted Answer · 2016-12-24 09:47:43Z

2

Why not just use a stored procedure instead? You could simply have it return a result set that had rows with just one column.

Stored functions can't do what you want to do - well, I suppose you could do that group concat thing but why bother? Create the stored procedure and have it return the ints you want.

Something like:

CREATE PROCEDURE getNearIds 
     (myLocationLon double, myLocationLat double)
BEGIN
   # whatever your logic is to get the ids... 
   # e.g.,
   select id from locations where [whatever "near" means]
END

edited Dec 24, 2016 at 9:47

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 24, 2011 at 23:26

itsmatt

31.5k11 gold badges103 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ahmed Saleh Over a year ago

first of all thanks for your concern . i agree with you a stored procedure yet i have to define an argument in the hear that is "OUT" so what data type will it have. in another words who would I the caller of the proc get these ids in your select statement . sorry I'm basicly an oracle developer who can newly to my sql to find interesting restrictions yet a simpler database engine to easy the development.

itsmatt Over a year ago

Not sure I understand your question, but there's no "OUT" in this example - it's a proc that returns a result set - essentially a collection of rows that each have one column that is, presumably, of type int - though I guess those IDs could be anything, right? It would depend upon your DB table, but I'm assuming the IDs are integers. If you rephrase your question, maybe that will help me understand more what you mean.

Ahmed Saleh Over a year ago

Hey, This is interesting. You said ** it's a proc that returns a result set**.... can a proc return something. well this is really new to me please send me a sample on how to use your sample proc above. and thanks very much for this info

itsmatt Over a year ago

CALL getNearIds (10.5, 10.6); is the syntax. Check out mysqltutorial.org/… for more info on stored procs.

Shafi Over a year ago

@itsmatt, I need to call the procedure from php script. Then how I could get the array, that should be returned? Can you give me a snip of code?

Collectives™ on Stack Overflow

MySQL: create function that returns an array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related