On error use information_schema in MySql function

Question

Can you tell me what's wrong with this MySQL function?

I followed the link How can I “select *” from a table in MySQL but omit certain columns?to define a function in MySQL. But I got an error "no database selected".

DELIMITER $$
CREATE FUNCTION getTableColumns(_schemaName varchar(100), _tableName varchar(100), _omitColumns varchar(200)) RETURNS varchar(5000)
BEGIN
    SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '_omitColumns,', '')
    FROM information_schema.columns 
    WHERE table_schema = _schemaName AND table_name = _tableName
    INTO results;
    RETURN results;
END$$

Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.

score 0 · Accepted Answer · answered Nov 11 '16 at 05:33

0

just add database name with function name

your_database_name.getTableColumns(......

answered Nov 11 '16 at 05:33

jai dutt

780
6
13

You are right! Thanks! Is it possible to have a global function across databases? – Bowen Liu Nov 11 '16 at 15:34
No function are always under some database but if you want to use shared function you can use [comman schema] (http://dba.stackexchange.com/questions/50678/mysql-possibility-to-create-global-routines-stored-procedures-and-or-functions) – jai dutt Nov 11 '16 at 17:47

On error use information_schema in MySql function

1 Answers1