SQL Analytics, With Clause and Column Level Grants

From the database geek.

Postgres 8.4 is just around the corner. I haven’t had much time lately to keep up with Postgres. I’ve had too much to do in the Oracle world (new job, writing, etc). However, I do try to keep up with a few email groups just to keep touch with what’s coming. Here are three things in Postgres 8.4 that I think will be interesting to a lot of people. Two of them bring Postgres that much closer to Oracle and one of them is useful (at times).

SQL Analytics

SQL Analytic Functions, also called Window Functions, allow you to execute functions across multiple sets of data within a single query.

For example, you can query the max value (or sum, average, whatever) of a field for multiple sort orders in a single query. In the past, you would need multiple queries with different sort orders.

A simple example:

select department_id, 
       job_id,
       max(salary) over (order by department_id) dept_max,
       max(salary) over (order by job_id) job_max
from employees

This example returns a row for very row in the employees table and includes the department, job and the max salary for both department and job. This would be useful as a subselect or wrapped in a parent query.

Something very cool about the Postgres implementation of analytics is that any user defined aggregate function can be used as an analytic function just by adding the OVER() keyword. That is cool!

With Clause

The with clause has been with us in the Oracle world since Oracle 9. It basically allows you to materialize a query as a temp table (in memory) which allows you to reuse that data over and over without re-querying each time. This issue has been solved in the past by actually creating a temp table, inserting into it and then using that temp table in further queries. The WITH clause makes this faster and much more readable.

A simple example:

WITH with_query AS (select * from departments) 
SELECT last_name, first_name, wq.department_name
  FROM employees emp
  JOIN with_query wq
  ON (emp.department_id = wq.department_id)

In this example, I materialize “select * from departments” as my temp table. I reference that table in the query below. Obviously, it doesn’t make a lot of sense for a query as simple as this but think about some of you larger queries and see where you could use it.

Postgres 8.4 also adds a new WITH RECURSIVE clause. This is a SQL standard implementation of dealing with tree structures (think Oracle’s CONNECT BY).

Column Level Grants

Finally, a cool feature, although I haven’t had a need for it as of yet, is column level grants. In 8.4, you will be able to grant insert, update or select at the column level. You can give someone the ability to select only a sub-set of columns (or even insert/update). Actually, I guess I have had the need for this but I have (in Oracle) used views and instead of triggers.

I can see where this will be useful in a postgres environment although it would be less useful in an Oracle environment.

That’s it for now.

LewisC

Technorati : , ,

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

5 Comments »