This form with
DISTINCT ON is typically faster than either
CREATE VIEW v3 AS
SELECT DISTINCT ON (id) 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
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
IF _id IS NULL THEN
SELECT ... ;
WHERE id = _id;
$func$ LANGUAGE plpgsql;
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