0

In Dbeaver 22.2.5 I try to create a function for Mysql 8.0.21, which returns temp table, like

DELIMITER $$

DROP FUNCTION IF EXISTS getUserPermissionTickets$$
CREATE FUNCTION getUserPermissionTickets(permission_id INT, user_id INT)
RETURNS @resultTable TABLE (item varchar(255))
BEGIN
      insert  into @resultTable(item) values('Value')
END  $$

But I got errors :

Error occurred during SQL query execution Reason: SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@resultTable TABLE (item varchar(255)) BEGIN insert into @resultTable(it' at line 2

I see next : https://prnt.sc/QPvtSV7ZpsIS

How to fix it ?

Thanks in advance!

user207421
  • 305,947
  • 44
  • 307
  • 483
Petro Gromovo
  • 1,755
  • 5
  • 33
  • 91
  • 7
    MYSQL does not have table variables or table value functions..step back a bit and explain what you are trying to do. – P.Salmon Aug 22 '23 at 14:53
  • `RETURNS {STRING|INTEGER|REAL|DECIMAL}` these are all that is allowed – RiggsFolly Aug 22 '23 at 14:56
  • Also, what are you expecting to return from a function that only does an INSERT – RiggsFolly Aug 22 '23 at 14:57
  • 3
    I guess you're trying to copy a definition from some other SQL dialect that allows returning tables from functions. Unfortunately, this isn't possible in MySQL. – Barmar Aug 22 '23 at 15:17
  • I need to write Mysql 8 function/procedure which make union of data from several tables and return some result set(like custom table with given structure). Please reference to such example... – Petro Gromovo Aug 23 '23 at 04:44

0 Answers0