Using a stored procedure as a "dynamic" view?

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?

From stackoverflow

• 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.

• 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.

• 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.

• 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.