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@postgresql.org [mailto:pgsql-hackers-
owner@postgresql.org] 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
gj@pointblue.com.pl

---------------------------(end of

broadcast)---------------------------

"Roberts, Jon" <Jon.Roberts@asurion.com> writes:

I have no idea why you can't do a subquery in the limit

It hasn't seemed worth putting any effort into --- AFAIR this is the
first time anyone's even inquired about it. As you say, you can always
use a function.

And I'm sure someone will point out a more efficient way to write my
function without using pl/pgsql. :)

Only that it doesn't seem a particularly bright idea to use SECURITY
DEFINER for a function that will execute any arbitrary caller-provided
SQL ...

regards, tom lane

"Roberts, Jon" <Jon.Roberts@asurion.com> writes:

I have no idea why you can't do a subquery in the limit

It hasn't seemed worth putting any effort into --- AFAIR this is the
first time anyone's even inquired about it. As you say, you can

always

use a function.

And I'm sure someone will point out a more efficient way to write my
function without using pl/pgsql. :)

Only that it doesn't seem a particularly bright idea to use SECURITY
DEFINER for a function that will execute any arbitrary caller-provided
SQL ...

LOL! I knew something in my code would trigger a response. :)

Jon