postgresql: select non-outliers from view

Question

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.

Answer 1

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.

-> SQLfiddle for Postgres 8.3.