| 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: | Whole Thread | Raw Message | 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 |