MySQL view with a function to create an input variable.

Question

Is it possible to create an SQL view in MySQL that takes an input variable say as a function argument? I have found this caption from the MySQL web site but am not sure how to use it as I am quite new to SQL functions. When I run this in the MySQL command prompt ,it gives me errors. Also I am not sure if this is even what I am looking for?

create function book_subject
returns varchar(64) as
return @book_subject;

create view thematical_books as
select title, author
from books
where subject = book_subject();

Answer 1

You are getting errors because the CREATE FUNCTION syntax is incorrect (gotta love those MySQL manual user comments!). The correct syntax for creating this function is as follows:

CREATE FUNCTION book_subject()
RETURNS VARCHAR(64)
RETURN @subject;

The CREATE VIEW syntax is correct.

In order to use the view, you'll need to set the variable @book_subject before you select from the view:

SET @book_subject = 'Epic Poems';

Then when you do a:

SELECT * 
FROM   thematical_books;

It will return the title and author of all of the books that have a subject of 'Epic Poems'

This is a trick to get around the restriction of MySQL views that "The SELECT statement [of the view] cannot refer to system or user variables." You use a function that just returns the variable, and that function gets called each time the view is used.

Answer 2

This is about as close as you are likely to get. There isn't an official way to get any arguments passed into a view (because how do you supply the argument when the view is referenced in the FROM clause). Using a session global variable and a function as shown is about the only way to achieve the effect. It is sneaky and bug-prone - not good attributes for clean maintainable code.