[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
Re: [HACKERS] 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: [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
Re: [HACKERS] subquery in limit
Roberts, Jon [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] subquery in limit
Roberts, Jon [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
