Using a stored procedure as a "dynamic" view?

Question

I want to create a "view" to eliminate the same three-line sub-query from about 90 queries in an app I'm working on.

The problem is the sub-query contains a condition based on a variable.

SELECT * FROM items WHERE id NOT IN (
  SELECT item_id FROM excluded_items WHERE user_id = 123
);

If it wasn't variable, I could simply make a view and be done with it.

I'm not sure what to do in this case though. Adopting the same mentality behind a view I'm tempted to make a stored procedure that returns the desired record set, so that it could be called something like this:

SELECT * FROM user_items(123);

Now I have a single place to update this item exclusion and any further conditions, however I'm not sure how indexing is affected if I want to join the results of that SP against other tables?

So is this good/bad practice? Is there another way to do it, or should I just suck it up and keep replicating this sub-query?

Answer 1

As usual your mileage may vary. If you are worried about this being a good practice in terms of your code syntax, I don't think it matters. It is a pretty normal thing to use a stored procedure to return record sets from and if it saves you development time - then why not do it? However, if you have determined that the cost to your query execute times is impacted in such a negative way that your business costs more than your productivity as a programmer, then by all means don't go with stored procedures.

I have heard a lot of banter over the years about stored procedures from people calling them evil to best practices. The conclusion that I have come to is as always use the right tool for the job.

To determine how the change exactly affects performance, execute a few test queries using:

EXPLAIN ANALYZE SELECT * FROM items WHERE id NOT IN (
  SELECT item_id FROM excluded_items WHERE user_id = 123
);

and then

EXPLAIN ANALYZE SELECT * FROM user_items(123);

Then compare the execution times and the query plans. I think you will then be able to make a more informed decision.

Answer 2

I think the stored procedure solution is more DRY and really improves readability. Although I certainly prefer to use views where possible (especially with PostgreSQL's powerful rules), I just can't think of a nicer way of expressing this.

Answer 3

Having the same SQL in 90 places can be solved the client side too. For example, create a function that builds the SQL string:

public string SqlItemsForUser(int iUserId) {
    return "SELECT * FROM items WHERE id NOT IN ( " +
        "SELECT item_id FROM excluded_items WHERE user_id = " +
        Convert.ToString(iUserId) + ");";
}

You can call this function in 90 places, so if you have to change the subquery, you only have to change it in one place.

Answer 4

Did you try something like

create view user_items as (
  select i.*, u.id as user_id
    from (items i cross join users u)
      left join excluded_items e
      on (i.id = e.item_id
        and u.id = e.user_id)
    where e.item_id is null
);

already? I tested it with PostgreSQL 8.3, which is able to pull the condition on the user_id into the cross join if you use the view in simple queries like

select *
  from user_items ui
  where user_id = 1;

If your queries using this view become too complicated for the query optimizer to find the possibility to pull the condition on user_id into the cross join and the full cross join is calculated, then you can still play with some parameters of the query optimizer to get it pulled in again.