DATABASE() function within a view - behaviour defined?
Hi According to the definition of the DATABASE() built in function, "Within a stored routine, the default database is the database that the routine is associated with". Is this the defined behavior for a view as well? I didn't see view mentioned under the definition of "stored routine". It does work, but I didn't know if it was a defined behavior I could rely on.
In the following example, a multi tenant saas solution is discussed. The design uses a separate db per tenant. Each tenant db name includes the tenant id. For example fieldsupport_tenant001.
It's unfortunate, but for a variety of reasons, there also needed to be one database which is a true multi tenant db. It includes one multi tenant table, "locations", which contains a tenant id column.
The following view is intended to be deployed in each tenant db, to restrict the availability of the locations data to the correct tenant. This view attempts to leverage the existing approach to tenant isolation, and avoid introducing a second, separate approach:
create view fieldsupport_tenant001.filtered_locations as select * from multitenant.locations where TenantId = substr(database(),14);
FYI a view is used here rather than stored proc, as it is needed for joins.
Thanks
Answer Answered by Oleksandr Byelkin in this comment.
Why you think if something written about stored procedures it can be related to views. views do not set current database:
create view v1 as select database() as db; select * from v1; db test create database test2; use test2; select * from test.v1; db test2