Qury plan for sql function with security definer

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

Browse pgsql-general by date

  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