I have no idea why you can't do a subquery in the limit but you can reference a function:
create table test as select * from pg_tables; create or replace function fn_count(p_sql varchar) returns int as $$ declare v_count int; begin execute p_sql into v_count; return v_count; end; $$ language 'plpgsql' security definer; select * from test limit fn_count('select round(count(*)*0.9) from test'); And I'm sure someone will point out a more efficient way to write my function without using pl/pgsql. :) Jon > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED] On Behalf Of Grzegorz Jaskiewicz > Sent: Friday, February 15, 2008 5:35 AM > To: pgsql-hackers@postgresql.org > Subject: [HACKERS] subquery in limit > > (just as an example): > select * from test order by a limit (select count(*)*0.9 from test); > > is not doable in postgresql. Someone recently asked on IRC about, > "SELECT TOP 90 PERCENT" type of query in m$sql. > Any ideas how should this be approach in psql. I ask here, because you > guys probably can tell why the first query won't work (subquery is not > allowed as limit's argument, why?). > > cheers. > > -- > Grzegorz Jaskiewicz > [EMAIL PROTECTED] > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster