0

Given below is the syntax for the MariaDB's CREATE FUNCTION:

CREATE [OR REPLACE]
    [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
    [AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
    RETURN func_body


func_parameter:
    param_name type


type:
    Any valid MariaDB data type


characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'


func_body:
    Valid SQL procedure statement

The type parameter accepts any valid MariaDB data type. I tried to return a set:

delimiter //
create function get_a_set ()
  returns set
  begin
    RETURN SET('a' , 'b');
  end //

delimiter ;

But I am getting a syntax error in the following line: RETURN SET('a', 'b');

The exact error is given below:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'begin
    RETURN SET('a' , 'b');
  end' at line 3

Any help would be greatly appreciated. Thanks!

  • A SET isn't actually a type. – danblack Feb 27 at 6:52
  • I referred to this page: data type MariaDB has set data type. – KAUSHAL KISHORE Feb 27 at 6:56
  • Set is bit-mapped BIGINT with each bit aliased with some string literal. It cannot be a function return type because bits aliases are not a part of this datatype, they are a part of table definition which is absent in function returned value. – Akina Feb 27 at 7:02
  • So is there any way in which I can return a set of values from a function in MariaDB? – KAUSHAL KISHORE Feb 27 at 7:04
  • 1
    Study to understand: create table test1 (val set ('a','b','c')); create table test2 (val set ('x','y','z')); insert into test1 (val) values ('a,c'); insert into test2 (val) select val from test1; show warnings; select * from test2; insert into test2 (val) select val+0 from test1; select * from test2; – Akina Feb 27 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.