Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 56800632ADB for ; Wed, 18 Nov 2009 15:35:35 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 38805-08 for ; Wed, 18 Nov 2009 19:35:21 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yx0-f181.google.com (mail-yx0-f181.google.com [209.85.210.181]) by mail.postgresql.org (Postfix) with ESMTP id 7E2D16329B9 for ; Wed, 18 Nov 2009 15:35:24 -0400 (AST) Received: by yxe11 with SMTP id 11so1289067yxe.15 for ; Wed, 18 Nov 2009 11:35:23 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=WysHUvCSkkpWODHqAVYTm0AGM4/cuhCLqza/UkI20/Y=; b=xbwpJfAMBIRzhkL8ogCv85/WZXmOLss2feCnueblOfz8DUTqiVLXcR18xEp5ysn9iN ebyNV6P3tP5pAvqhPSiLNc5InW12mIm3W5oOAGXSrQJU/UcqUqpe0fmd30I6uvBDqvEG hMcDPvSciILce3AgLY3++FqJYQ/kN6T6eVw6U= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=Vtkf/pPdEuWCZEiBk4OZ6gAcvZ4gcxs6Nl+yDpRsEo6+7NSCnqgoFEim93Y6dRkR5m sqz2yVtxa0puCk8vwT1eHQs30w8kxm4uEch9UgvuuMC8gwW0QoHoiZgTgoXjByjoxR+5 5AXEKdeR8e0g1DCDzIhzbo91L6Tsnj4PXsRMg= MIME-Version: 1.0 Received: by 10.150.61.10 with SMTP id j10mr2938340yba.282.1258572922517; Wed, 18 Nov 2009 11:35:22 -0800 (PST) In-Reply-To: References: <5ab13580911181121g6e89759doa6c6ea6557424f71@mail.gmail.com> <200911182027.08857.guillaume@lelarge.info> Date: Wed, 18 Nov 2009 20:35:22 +0100 Message-ID: <162867790911181135u48cbeee0s18f7dee362557e51@mail.gmail.com> Subject: Re: LIMIT BASED ON PERCENT From: Pavel Stehule To: Another Trad Cc: Guillaume Lelarge , pgsql-sql@postgresql.org, Lee Hachadoorian Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.575 tagged_above=-10 required=5 tests=AWL=0.024, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200911/56 X-Sequence-Number: 33407 2009/11/18 Another Trad : > But there is any way to do it? CREATE OR REPLACE twenty() RETURNS SETOF foo AS $$ DECLARE rows int; r record; BEGIN rows :=3D (SELECT count(*) FROM foo); FOR r IN EXECUTE 'SELECT * FROM r ORDER BY some col LIMIT ' || (rows * 0.2)::int LOOP RETURN NEXT r; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; this code should to work in 8.3 regards Pavel Stehule > > 2009/11/18 Guillaume Lelarge >> >> Le mercredi 18 novembre 2009 =C3=A0 20:24:09, Another Trad a =C3=A9crit = : >> > No, It doesn't. >> > In my machine: >> > >> > First select >> > ERROR: =C2=A0syntax error at end of input >> > LINE 1: select * from rapadura.cliente limit 20% >> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 ^ >> > Second one: >> > ERROR: =C2=A0argument of LIMIT must not contain subqueries >> > >> > Postgres 8.3 >> > >> >> It works for Lee because obviously he's working on a 8.4 server. You can >> use >> subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For >> earlier >> releases, there's no way to do this in a single query. >> >> >> -- >> Guillaume. >> =C2=A0http://www.postgresqlfr.org >> =C2=A0http://dalibo.com > >