General purpose view for last value in a group

Question

I am trying to create a view to show the last value in the group. Looking for a more general purpose query.

CREATE TABLE a (
   id INTEGER,
   seq INTEGER,
   val VARCHAR2(16),
   PRIMARY KEY(id,seq)
);

-- View 1
CREATE VIEW v1 AS SELECT a.*
 FROM 
  a, 
  (SELECT id, MAX(seq) from a GROUP BY id) b
 WHERE a.id=b.id AND a.seq=b.seq

-- View 2
CREATE VIEW v2 AS SELECT a.*
 FROM 
  a 
 WHERE a.seq=(SELECT seq from a b WHERE a.id=b.id ORDER BY seq DESC LIMIT 1) 

View 1 perform better when I want to list all records.

View 2 perform better when I do SELECT * FROM v2 WHERE id=?

Because this view is to be given to outsiders I want this to be as general purpose as possible. Is there any way to tell PostgreSQL these two query is equivalent and let it pick them automagically?

Answer 1

This form with DISTINCT ON is typically faster than either v1 or v2:

CREATE VIEW v3 AS
SELECT DISTINCT ON (id) a.*
FROM   a
ORDER  BY id, seq DESC NULLS LAST;

You get sorted output (by id) on top of it, which is typically a welcome side effect.
Minor difference: this form includes a row for ids with only NULL values in seq. This, too, would be a welcome effect, typically. And irrelevant if seq is defined NOT NULL.

If performance is essential I strongly suggest to create a matching (!) multicolumn index of the form:

CREATE INDEX a_id_seq_idx ON a (id, seq DESC NULLS LAST);

A lot more details and more alternatives under this related question on SO:
Select first row in each GROUP BY group?

Switch query depending on input

If you are interested in a switching technique per se, rather, you could create a table function instead of the view which takes an id as parameter.

CREATE OR REPLACE FUNCTION f_a(_id int DEFAULT NULL)
  RETURNS SETOF a AS
$func$
BEGIN

IF _id IS NULL THEN
    RETURN QUERY
    SELECT ... ;
ELSE
    RETURN QUERY
    SELECT ...
   WHERE  id = _id;
END IF;

END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM f_a();     -- for the whole table
SELECT * FROM f_a(123);  -- for a particular id

Could be improved with more parameters.

This approach is perfect for simple calls. Even saves some planning overhead and is typically faster than raw SQL this way. Be advised, though, that a plpgsql function poses as optimization barrier. The body of the function cannot be inlined or optimized in the context of a bigger query (like a view or a simple SQL function could be). More in this related answer:
PostgreSQL Stored Procedure Performance