From: | "Andrew G(dot) Saushkin" <ags(at)bsse(dot)ru> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Qury plan for sql function with security definer |
Date: | 2013-09-13 11:52:38 |
Message-ID: | 5232FC86.7080902@bsse.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello all,
I have two simple SQL function. The first is defined with the modifier
"security definer", while the second is not.
1 create or replace function func_with_sec_definer(param text)
returns setof text as $$
2
3 select unnest(string_to_array(param, ',')) ;
4
5 $$ language sql immutable security definer;
6
7 create or replace function func_without_sec_definer(param text)
returns setof text as $$
8
9 select unnest(string_to_array(param, ',')) ;
10
11 $$ language sql immutable ;
When I look at the query execution plan, I see that the first function
uses the function scan, while the second is not. How can I do to make
the first function does not use function scan as well?
=# explain select * from func_with_sec_definer('1,2,3');
┌───────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN
│
├───────────────────────────────────────────────────────────────────────────────┤
│ Function Scan on func_with_sec_definer (cost=0.25..10.25 rows=1000
width=32) │
└───────────────────────────────────────────────────────────────────────────────┘
=# explain select * from func_without_sec_definer('1,2,3');
┌────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────┤
│ Result (cost=0.00..0.51 rows=100 width=0) │
└────────────────────────────────────────────┘
=# select version();
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ version
│
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
--
Regards, Andrew G. Saushkin
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Voras | 2013-09-13 12:56:57 | Re: Major upgrade of PostgreSQL and MySQL |
Previous Message | Rémi Cura | 2013-09-13 08:42:05 | Computing (disjoint) union of range |