The view level interface can be used for the definition of rules whose bodies includes only imported database predicates (by using the relation level interface) described above and aggregate predicates (defined below). In this case, the rule is translated into a complex database query, which is then executed taking advantage of the query processing ability of the database system.
One can use the view level interface through the predicate db_query/2:
The compiler is a simple extension of  which generates SQL queries with bind variables and handles NULL values as described below (see section 7.3.7). It allows negation, the expression of arithmetic functions, and higher-order constructs such as grouping, sorting, and aggregate functions.
Database goals are translated according to the following rules from :
In the following, we show the definition of a simple join view between the two database predicates emp and dept.
Assuming the declarations:
| ?- db_import('EMP'('ENAME','JOB','SAL','COMM','DEPTNO'),emp). | ?- db_import('DEPT'('DEPTNO','DNAME','LOC'),dept). use: | ?- db_query(rule1(Ename,Dept,Loc), (emp(Ename,_,_,_,Dept),dept(Dept,Dname,Loc))). yes | ?- rule1(Ename,Dept,Loc).
generates the SQL statement:
SELECT rel1.ENAME , rel1.DEPTNO , rel2.LOC FROM emp rel1 , DEPT rel2 WHERE rel2.DEPTNO = rel1.DEPTNO; Ename = CLARK Dept = 10 Loc = NEW YORK
Backtracking can then be used to retrieve the next row of the view.
generates the SQL statement:
SELECT rel1.ENAME , rel1.DEPTNO , NULL FROM emp rel1 , DEPT rel2 WHERE rel1.ENAME = :BIND1 AND rel2.DEPTNO = rel1.DEPTNO AND rel2.LOC IS NULL;
The view interface also supports aggregate functions predicates sum, avg, count, min and max. For example
| ?- db_query(a(X),(X is avg(Sal,A1 ^ A2 ^ A4 ^ A5 ^ emp(A1,A2,Sal,A4,A5)))). yes. | ?- a(X). generates the query : SELECT AVG(rel1.SAL) FROM emp rel1; X = 2023.2 yes
A more complicated example:
| ?- db_query(harder(A,B,D,E,S), (emp(A,B,S,E,D), not dept(D,P,C), not (A = 'CAROL'), S > avg(Sal,A1 ^ A2 ^ A4 ^ A5 ^ A6 ^ A7 ^( emp(A1,A2,Sal,A4,A5), dept(A5,A7,A6), not (A1 = A2))))). | ?- harder(A,B,D,E,S).
generates the SQL query:
SELECT rel1.ENAME , rel1.JOB , rel1.DEPTNO , rel1.COMM , rel1.SAL FROM emp rel1 WHERE NOT EXISTS (SELECT * FROM DEPT rel2 WHERE rel2.DEPTNO = rel1.DEPTNO) AND rel1.ENAME <> 'CAROL' AND rel1.SAL > (SELECT AVG(rel3.SAL) FROM emp rel3 , DEPT rel4 WHERE rel4.DEPTNO = rel3.DEPTNO AND rel3.ENAME <> rel3.JOB); A = SCOTT B = ANALYST D = 50 E = NULL(null1) S = 2300
All database rules defined by db_query can be queried with any mode: For example:
generates the query:
SELECT rel1.ENAME , rel1.JOB , rel1.DEPTNO , NULL , rel1.SAL FROM emp rel1 WHERE rel1.JOB = :BIND1 AND rel1.COMM IS NULL AND NOT EXISTS (SELECT * FROM DEPT rel2 WHERE rel2.DEPTNO = rel1.DEPTNO ) AND rel1.ENAME <> 'CAROL' AND rel1.SAL > (SELECT AVG(rel3.SAL) FROM emp rel3 , DEPT rel4 WHERE rel4.DEPTNO = rel3.DEPTNO AND rel3.ENAME <> rel3.JOB ); A = SCOTT D = 50 S = 2300; no
Notice that at each call to a database relation or rule, the communication takes place through bind variables. The corresponding restrictive SQL query is generated, and if this is the first call with that adornment, it is cached. A second call with same adornment would try to use the same database cursor if still available, without parsing the respective SQL statement. Otherwise, it would find an unused cursor and retrieve the results. In this way efficient access methods for relations and database rules can be maintained throughout the session.