MySQL Use table name for function

Question

When we use a statement like select count(*) from TABLE, the function count() automatically knows which table it is counting. Is it possible to grab the table and use it in a user defined function.

   drop function if exists related_count;
   create function related_count(parent int(11)) returns int(11) deterministic 
   begin 
    declare count int(11) default 0;
    set count=(select count(*) from TABLENAME where id=parent);
    return count;
   end;

So that I can use it like this:

select count(*),related_count(id) from TABLENAME

So that I can use the same function regardless of table instead of defining multiple functions because of multiple tables.

Is there a way to switch between select count(*) from TABLENAME1 where id=parent or select count(*) from TABLENAME2 where id=parent dependent on a variable related_count('TABLE1',id)

you can use dynamic query, and fyi dynamic query is not feasible in function, however you can use stored procedure to make use of dynamic query — Rajeev Ranjan
– Rajeev Ranjan, Commented Apr 28, 2018 at 16:42

Bill Karwin · Accepted Answer · 2018-04-28 17:20:19Z

The comment above from @RajeevRanjan mentions using dynamic SQL. This won't work, but if it did it would look like this:

create function related_count(tablename varchar(64), parent int) returns int reads sql data
begin
  declare count int default 0;
  set @sql = concat('select count(*) into count from `', tablename, '` where id = ', parent);
  prepare stmt from @sql;
  execute stmt;
  return count;
end

However, this is not allowed:

ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger

The reason it doesn't work is that your stored function could be called by an expression in an SQL statement that is itself a dynamic SQL execution. I guess MySQL only allows one level "deep" of prepare/execute. You can't make a prepared query run another prepared query.

To do this, you'd have to hard-code each table name like the following:

create function related_count(tablename varchar(64), parent int) returns int reads sql data
begin
  declare count int default null;
  if     tablename = 'foo' then set count = (select count(*) from foo where id = parent);
  elseif tablename = 'bar' then set count = (select count(*) from bar where id = parent);
  elseif tablename = 'baz' then set count = (select count(*) from baz where id = parent);
  end if;
  return count;
end

This also has an advantage that it isn't an SQL injection vulnerability, whereas the PREPARE/EXECUTE solution (if it had worked) would be.

PS: A function that reads from other tables is not deterministic.

Collectives™ on Stack Overflow

MySQL Use table name for function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related