Re: subquery in limit

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: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)org] On Behalf Of Grzegorz Jaskiewicz
> Sent: Friday, February 15, 2008 5:35 AM
> To: pgsql-hackers(at)postgresql(dot)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
> gj(at)pointblue(dot)com(dot)pl
>
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq