sql - postgresql: select non-outliers from view -

using version 8.3 (no choice in matter).

i cannot use ";with x as" style queries because not supported (from can tell in 8.3. have attempted use them , not recognized). can not use top of course since i'm not in sql server. tried use following limit query stymied following error:

select * myview limit(select (count(int_field) * 0.95)::integer myview);  error:  argument of limit must not contain subqueries 

it's not ideal since doesn't remove lower 5% can live highest 5% being removed.

before postgres 8.4 there no built-in way percentage of rows single query. consider this closely related thread on pgsql-sql list

you write function doing work in single call. should work in postgres 8.3:

create or replace function foo(_pct int)   returns setof v_t $func$ declare    _ct     int := (select count(*) v_t);    _offset int := (_ct * $1) / 100;    _limit  int := (_ct * (100 - 2 * $1)) / 100; begin  return query select *   v_t offset _offset limit  _limit;  end $func$ language plpgsql; 

call:

select * foo(5) 

this crops 5% top and bottom.

the return type returns setof v_t derived view named v_t directly.

-> sqlfiddle postgres 8.3.