SQL table macros 14: parameterized views

Posted on by

Developers like to reuse code. In SQL, views are reusable queries with lots of advantages, but one drawback: no parameters to limit the queries to only those rows we want. Sure, we can add a WHERE clause when selecting from a view, but the view might return lots of unnecessary data before the WHERE clause filters the rows.

As Andrej Pashchenko recently wrote, SQL table macros can get the results we would want from parameterized views (if they existed), but they do not have all the advantages of real views: see https://blog.sqlora.com/en/parameterized-views-with-sql-macros-part-2/ He wants to combine the advantages of views and macros, and so do I.

(To skip the explanations and see what I came up with, go to My proposal.)

I’ll concentrate on these advantages of views:

  1. We can include them in queries anywhere a table could appear.
  2. The *_DEPENDENCIES views show the “dependency chain” between them and other objects.

Here is a super simple example:

create or replace view dept_emp as
  select deptno, dname, loc, empno, ename, job, mgr, sal, comm
  from dept
  left join emp using(deptno);

select name, type, referenced_name, referenced_type
from user_dependencies where name = 'DEPT_EMP';

NAME        TYPE    REFERENCED_NAME    REFERENCED_TYPE
DEPT_EMP    VIEW    DEPT               TABLE
DEPT_EMP    VIEW    EMP                TABLE

Now, what if we want to filter on DEPTNO? We can just copy the query from the view and add a filter:

create or replace function dept_emp_macro(p_deptno number)
return varchar2 sql_macro is
begin
  return '
select deptno, dname, loc, empno, ename, job, mgr, sal, comm
from dept
left join emp using(deptno)
WHERE DEPTNO = P_DEPTNO';
end dept_emp_macro;
/

select * from dept_emp_macro(10);

DEPTNO DNAME      LOC      EMPNO ENAME  JOB        MGR  SAL COMM
    10 ACCOUNTING NEW YORK  7839 KING   PRESIDENT      5000
    10 ACCOUNTING NEW YORK  7782 CLARK  MANAGER   7839 2450
    10 ACCOUNTING NEW YORK  7934 MILLER CLERK     7782 1300

select name, type, referenced_name, referenced_type
from user_dependencies where name = 'DEPT_EMP_MACRO';

NAME              TYPE        REFERENCED_NAME    REFERENCED_TYPE    
DEPT_EMP_MACRO    FUNCTION    STANDARD           PACKAGE

With the macro, we have a parameterized query but we have lost the information about the dependency chain in USER_DEPENDENCIES, so the second advantage of views is lost. Not only that, but the first advantage is limited as well: currently SQL macros inside WITH clauses are not permitted, and WITH clauses inside SQL macros cannot use scalar parameters!

My proposal

  1. I write and test a complete query. A WITH clause at the beginning contains hard-coded parameters.
  2. I create a view based on that query, including the hard_coded parameters.
  3. A generic SQL macro returns the view text after stripping off the hard-coded WITH clause.
  4. I call the macro using a new WITH clause, that can include whatever values or bind variables I want.
create or replace function parameterized_view(
  p_view dbms_tf.table_t,
  P_PARMS dbms_tf.table_t
)
return clob sql_macro is
  l_view_text long;
begin
  select text into l_view_text from user_views
  where view_name = trim('"' from p_view.table_name);
  l_view_text :=
    regexp_replace(
      regexp_replace(l_view_text, 'with\s*p_parms[^)]*\)\s*', '', 1, 0, 'i'),
      '^,', 'with '
    );
  return l_view_text;
end parameterized_view;
/

create or replace view dept_emp_pv as
with P_PARMS as (select 'RESEARCH' dname, 'CLERK' job from dual)
, d as (
  select deptno, dname, loc from dept
  where dname = (select dname from P_PARMS)
)
, e as (
  select deptno, empno, ename, job, mgr, sal, comm
  from emp
  where job = (select job from P_PARMS)
)
select * from d left join e using(deptno);

with PARMS as (select 'SALES' dname, 'SALESMAN' job from dual)
select * from parameterized_view(dept_emp_pv, PARMS);

DEPTNO DNAME    LOC        EMPNO    ENAME     JOB         MGR     SAL    COMM
    30 SALES    CHICAGO    7499     ALLEN     SALESMAN   7698    1600     300
    30 SALES    CHICAGO    7521     WARD      SALESMAN   7698    1250     500
    30 SALES    CHICAGO    7654     MARTIN    SALESMAN   7698    1250    1400
    30 SALES    CHICAGO    7844     TURNER    SALESMAN   7698    1500

Note that in the view, the “parameters” clause must have the same name as the second parameter in the macro: P_PARMS. When I call the macro, I can give the clause any name I want, since I pass the name to the macro.

Remember, DEPT_EMP_PV is the identifier of a table or view, so if it is invalidated then the query will automatically be reparsed and the new text will be picked up from the data dictionary. The dependency chain will work even though the macro itself has not been invalidated.

To conclude, using one 16-line generic macro we can have almost all the advantages of views while adding support for parameters! The views do have to be written with this usage in mind, and they are meant to be used as main queries, not subqueries.

Posts on SQL table macros

Leave a comment