Outdated Version
You are viewing an older version of this section. View current production version.
ELT
Returns the Nth value in its list of arguments.
ELT (pos, val, [val, [val...]])
Arguments
- pos: one-indexed argument position
- val: any number of values.
Examples
memsql> select elt(2, 'foo', 'bar', 'baz');
+-----------------------------+
| elt(2, 'foo', 'bar', 'baz') |
+-----------------------------+
| bar |
+-----------------------------+
memsql> select elt(product_code, 'housewares', 'textiles', 'appliances', 'lamps')
-> as product_type from products limit 6;
+-----------------------------+
| product_type |
+-----------------------------+
| housewares |
| lamps |
| lamps |
| housewares |
| textiles |
| appliances |
+-----------------------------+
This feature can be combined with views to enable row-level permissions. In this example, persons with the ‘employee’ role can only see themselves, while ‘managers’ can see all records:
memsql> create table employees (
-> id int primary key,
-> fullname varchar(64),
-> account_name varchar(64),
-> role enum('employee', 'manager'),
-> salary int
-> );
memsql> create table permissions (
-> account_name varchar(64),
-> emp_id int,
-> primary key (account_name, emp_id)
-> );
memsql> create view employee_view as
-> select e.* from employees e, permissions p
-> where e.id = p.emp_id
-> and p.account_name = current_user();
memsql> insert into employees values
-> (1, 'SYSTEM', 'root@%', 'manager', 0),
-> (2, 'Kevin Kelvin', 'kelvin@%', 'manager', 5000),
-> (3, 'Oliver Heavyside', 'oliver@%', 'employee', 3000);
memsql> insert into permissions values
-> ('root@%', 1), ('root@%', 2), ('root@%', 3),
-> ('kelvin@%', 2), ('kelvin@%', 3),
-> ('oliver@%', 3);
-- as root:
memsql> select current_user(), * from employee_view;
+----------------+----+------------------+--------------+----------+--------+
| current_user() | id | fullname | account_name | role | salary |
+----------------+----+------------------+--------------+----------+--------+
| root@% | 1 | SYSTEM | root@% | manager | 0 |
| root@% | 2 | Kevin Kelvin | kevin@% | manager | 5000 |
| root@% | 3 | Oliver Heavyside | oliver@% | employee | 3000 |
+----------------+----+------------------+--------------+----------+--------+
-- as kelvin:
mysql> select current_user(), * from employee_view;
+----------------+----+------------------+--------------+----------+--------+
| current_user() | id | fullname | account_name | role | salary |
+----------------+----+------------------+--------------+----------+--------+
| kelvin@% | 2 | Kevin Kelvin | kevin@% | manager | 5000 |
| kelvin@% | 3 | Oliver Heavyside | oliver@% | employee | 3000 |
+----------------+----+------------------+--------------+----------+--------+
-- as oliver:
mysql> select current_user(), * from employee_view;
+----------------+----+------------------+--------------+----------+--------+
| current_user() | id | fullname | account_name | role | salary |
+----------------+----+------------------+--------------+----------+--------+
| oliver@% | 3 | Oliver Heavyside | oliver@% | employee | 3000 |
+----------------+----+------------------+--------------+----------+--------+