DATABASE() function within a view - behaviour defined?

You are viewing an old version of this question. View the current version here.

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

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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.