Geek Answers Handbook

MySQL view with function to create an input variable

Is it possible to create a SQL view in MySQL that takes an input variable as an argument to a function? I found this header from the MySQL website, but am not sure how to use it since I am completely new to SQL functions. When I run this on the MySQL command line, it gives me errors. Also, I'm not sure if this is even what I'm 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();
+4
source share
2 answers

You get errors because the CREATE FUNCTION syntax is incorrect (you must love these MySQL user comments manually!). 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.

To use the view, you need to set the @book_subject variable before choosing from the view:

 SET @book_subject = 'Epic Poems';

Then when you do:

 SELECT * FROM thematical_books;

He will return the title and author of all books devoted to "Epic verses"

This is a trick to get around the limitation of MySQL views: "SELECT [views] cannot reference system or user variables." You use a function that simply returns a variable, and that function is called every time the view is used.

+5
source

It is about as close as you can get. There is no official way to get any arguments passed to the view (since how do you specify the argument when the link is referenced in the FROM clause). Using the global session variable and function as shown is the only way to achieve the effect. These are impenetrable and error prone - not very good attributes for pure supported code.

+1
source

More articles:


All Articles