PostgreSQL query decomposition

Question

I fail to decompose simple SQL queries. I use PostgreSQL but my question is also related to other RDBMS.

Consider the following example. We have table orders and we want to find first order after which total amount exceeded some limit:

drop table if exists orders cascade;

/**
Table with clients' orders
*/
create table orders(
date timestamp,
amount integer
/**
Other columns omitted
*/
);

/**
Populate with test data
*/
insert into orders(date,amount) 
values
('2011-01-01',50),
('2011-01-02',49),
('2011-01-03',2),
('2011-01-04',1000);

/**
Selects first order that caused exceeding of limit 
*/
create view first_limit_exceed
as
select min(date) from
(
    select  o1.date
    from orders o1,
         orders o2
    where o2.date<=o1.date
    group by o1.date
    having sum(o2.amount) > 100
) limit_exceed;

/**
returns "2011-01-03 00:00:00"
*/
select * from first_limit_exceed;

Now let's make the problem a little harder. Consider we want to find total amount only for rows that satisfy some predicate. We have a lot of such predicates and creating separate version of view first_limit_exceed would be terrible code duplication. So we need some way to create parameterized view and pass either filtered set of rows or predicate itself to it. In Postgres we can use query language functions as parameterized views. But Postgres does not allow function to get as argument neither set of row nor another function. I still can use string interpolation on client's side or in plpgsql function, but it is error-prone and hard to test and debug. Any advice?

maybe this is dumb, but you could have a view whose columns were the results of predicates (so each column type is boolean). then adding a new predicate would involve (1) extending the view with a new predicate and (2) changing the code to select on the new column name. that keeps the query logic fairly simple, but places the predicates within sql. — andrew cooke, Aug 12 '11 at 16:57

score 2 · Answer 1 · answered Aug 12 '11 at 16:20

2

In PostgreSQL 8.4 and later:

SELECT  *
FROM    (
        SELECT  *,
                SUM(amount) OVER (ORDER BY date) AS psum
        FROM    orders
        ) q
WHERE   psum > 100
ORDER BY
        date
LIMIT 1

Add any predicates you want into the inner query:

SELECT  *
FROM    (
        SELECT  *,
                SUM(amount) OVER (ORDER BY date) AS psum
        FROM    orders
        WHERE   date >= '2011-01-03'
        ) q
WHERE   psum > 100
ORDER BY
        date
LIMIT 1

answered Aug 12 '11 at 16:20

Quassnoi

413,100
91
616
614

Thank you for quick reply, but I think you misunderstood my question. My main concern was code duplication. Consider you have 10 predicates. You can copypaste your query 10 times and manually substitute predicate. It is not so bad when your queries are small, but what if they are hundreds lines length? Copypasting hundred lines ten times will make you code unmaintainable spagetti. – dvv Aug 12 '11 at 16:35
@dvv: neither a set nor a predicate is a first class object in `SQL`. You cannot pass them as arguments to a static query. You may want to compose a dynamic query (which would add parsing overhead), however, if maintainability is more important than speed that would be a nice solution. – Quassnoi Aug 12 '11 at 16:53
@dvv: In `PostgreSQL` you can serialize sets as arrays of table tuples or used-defined records or strings and pass them between functions. However, this would be even more expensive that parsing the queries, also, this would require your sets to be in a predefined format. – Quassnoi Aug 12 '11 at 16:54

score -1 · Answer 2 · answered Aug 12 '11 at 16:32

It sounds a bit like you're trying to put too much code into the database. If you are interested in the rows of a certain relation that satisfy a particular predicate, just execute a select statement with an appropriate where clause in the client code. Having views that take predicates as parameters is reinventing the wheel that sql already solves nicely.

On the other hand, I can see an argument for storing queries themselves in the database, so that they can be composed into larger reports. This two is still better handled by application code. I might approach a problem like that by using a library that's good at dynamic sql generatation, (for example sqlalchemy), and then storing the query representations (sqlalchemy expression objects are 'pickleable') as blobs in the database.

To put it another way, databases are representers of facts, You store knowledge in them. applications have the duty of acting on user requests, When you find yourself defining transformations on the data, that's really more a matter of anticipating and implementing the requests of actual users, rather than just faithfully preserving knowledge.

Views are best used when the schema inevitably changes, so you can leave older applications that don't need to know about the new schema in a working state.

PostgreSQL query decomposition

2 Answers2