You can hugely simplify the query using DISTINCT ON:
SELECT DISTINCT ON (client, practice, account_id, encounter_id, charge_id)
id, charge_id, post_date
FROM charges
ORDER BY client, practice, account_id, encounter_id, charge_id, post_date DESC, id DESC;
Will be considerably faster in any case. Detailed explanation in this related answer on SO:
Select first row in each GROUP BY group?
The last ORDER BY expression id DESC is optional to break ties if rest should not be unambiguous, yet. May not be needed.
Support this with a matching multicolumn index:
CREATE INDEX charges_latest_idx ON charges
(client, practice, account_id, encounter_id, charge_id, post_date DESC, id DESC);
Whether such an index will be useful depends on undisclosed details.
Note in particular, that sort order has to match the query. In Postgres 9.2 or later this may even function as covering index, depending on undisclosed details.
Depending on undisclosed details, a materialized view might be a candidate, too. The more write operations the smaller the likelihood this would help. The same goes for the covering index.
First, stop using SELECT * in your views. I talk about this quite a bit here:
Next, run sp_refreshview or sp_refreshsqlmodule against each view that references a table (or another view!) that you have changed, e.g.:
EXEC sp_refreshview N'dbo.viewname';
If you want to generate a script that refreshes all of the views in the system:
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'
EXEC sp_refreshsqlmodule N'''
+ QUOTENAME(s.name) + '.' + QUOTENAME(v.name) + ''';'
FROM sys.schemas AS s
INNER JOIN sys.views AS v
ON s.[schema_id] = v.[schema_id];
EXEC sp_executesql @sql;
(You may want to run it twice in case there are circular or out-of-order dependencies.)
But this is a temporary fix. You will have to repeat this every time you change any table that might have views that reference it, then go find out which views are involved. This is not the right way to approach schema design. AT ALL. I wrote about a DDL trigger that would help automate this for you, but this is really the wrong approach - just stop using SELECT * in your views!
You may also want to consider adding WITH SCHEMABINDING to the definition of all of your views. This does two things:
- Prevents you from using
SELECT * in the view. This is A GOOD THING™.
- Prevents you from changing underlying tables without knowing exactly which views would be affected by the change. This is also A GOOD THING™.
Best Answer
You can't do what you want with a view. You have to use a function. Note that I didn't retain all your double quoting of identifiers, so you will have to put them back if you insist.
You can use it kind of like a view, but you must supply your parameters in parentheses, not a WHERE clause: