Using version 8.3 (no choice in the matter).
I cannot use ";WITH x AS" style queries because that is not supported (from what I can tell in 8.3. I have attempted to use them and they are not recognized). I can also not use TOP of course since I'm not in SQL Server. I tried to use the following limit query but was stymied by the following error:
SELECT * FROM myView LIMIT(SELECT (COUNT(int_field) * 0.95)::integer FROM myView);
ERROR: argument of LIMIT must not contain subqueries
It's not ideal since it doesn't remove the lower 5% but I can live just the highest 5% being removed.
Before Postgres 8.4 there is no built-in way to get a percentage of rows with a single query. Consider this closely related thread on the pgsql-sql list
You could write a function doing the work in a single call. this should work in Postgres 8.3:
CREATE OR REPLACE FUNCTION foo(_pct int)
RETURNS SETOF v_t AS
$func$
DECLARE
_ct int := (SELECT count(*) FROM v_t);
_offset int := (_ct * $1) / 100;
_limit int := (_ct * (100 - 2 * $1)) / 100;
BEGIN
RETURN QUERY
SELECT *
FROM v_t
OFFSET _offset
LIMIT _limit;
END
$func$ LANGUAGE plpgsql;
Call:
SELECT * FROM foo(5)
This actually crops 5% from top and bottom.
The return type RETURNS SETOF v_t is derived from a view named v_t directly.