CODEWITHSUNDEEP
mysqlsql
Sayalic
Sayalic

Reputation: 7650

How write a MySQL function which reference a field from a table?

I have a SQL:

SELECT * FROM table WHERE col_1 = a OR col_2 = a OR col_3 = a

I don't want to type a lot of col_1 = a OR col_2 = a OR col_3 = a, so I create a function:

CREATE FUNCTION is_col_equal(a VARCHAR(20))
  RETURNS BOOLEAN
  RETURN col_1 = a or col_2 = a or col_3 = a

But when I execute SELECT * FROM table WHERE is_col_equal(a), it gives Unknown column 'col_1' in 'field list'.

So, how write a MySQL function which reference a field from a table?

Upvotes: 0

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can reference the column. But you have to define what the function does. If you want the function to return a value if any of the three columns are a, then you can calculate that as a value and return it:

CREATE FUNCTION is_col_equal(in_a VARCHAR(20))
RETURNS BOOLEAN
BEGIN
    SELECT @bool = MAX(col_1 = in_a or col_2 = in_a or col_3 = in_a)
    FROM t;
    RETURN @bool
END;

What MySQL cannot do is return the rows that match your condition. That does not seem to be your intention, given that you are declaring the function to return a scalar value.

Upvotes: 1

Related Questions