0

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
lostinthebits
  • 661
  • 2
  • 11
  • 24
  • You havent' really explained what you expected from this query. Is it 95% of the rows? If so, according to what ordering expression? – Daniel Vérité Jul 11 '13 at 17:10

1 Answers1

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Tried to create this function and got the following error:ERROR: language "plpgsql" does not exist HINT: Use CREATE LANGUAGE to load the language into the database. – lostinthebits Jul 11 '13 at 18:21
  • Sorry I created this language. I thought I might not have the rights to do so. But now I'm getting "type "v_t" does not exist". – lostinthebits Jul 11 '13 at 18:28
  • @lostinthebits: Replace all occurrences of `v_t` with the actual name of your view or table. This could be made [generic with a polymorphic type](http://stackoverflow.com/questions/11740256/refactor-a-pl-pgsql-function-to-return-the-output-of-various-select-queries/11751557#11751557), but Postgres 8.3 is just too outdated and does not have [`pg_typeof()`](http://www.postgresql.org/docs/current/interactive/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE). So the function works only for one object. – Erwin Brandstetter Jul 11 '13 at 18:31