Let's say we have departments table in Oracle:
alp@XE>descr departments
Name Null? Type
----------------------------------------------------- -------- -----------------------
DEPARTMENT_ID NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
And we create a function to access this table's data:
create type department_row AS OBJECT (department_id int, department_name varchar2(30), manager_id number, location_id number);
create type department_table AS TABLE OF department_row;
create or replace function showData return department_table PIPELINED IS
r department_row:= department_row(null,null,null,null);
CURSOR c1 IS SELECT * FROM DEPARTMENTS;
BEGIN
FOR i in c1 LOOP
r.department_id:=i.department_id;
r.department_name:=i.department_name;
r.manager_id:=i.manager_id;
r.location_id:=i.location_id;
PIPE ROW(r);
END LOOP;
END;
/
Now we can just grant execute on this function to some user:
alp@XE>grant execute on showData to tu;
And this user can view data in departments table:
tu@XE>select * from TABLE(alp.showData());
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
27 rows selected.
In PostgreSQL you should explicitly set definer's rights when creating function:
CREATE TYPE data_rowtype as (ip inet, traffic integer);
create function showData () returns SETOF DATA_ROWTYPE AS '
select * from data
'
LANGUAGE SQL
SECURITY DEFINER;
And after
grant execute on function showData() to tu;
tu can view data in table "data":
alp=> select * from showData();
ip | traffic
----------------+----------
10.1.1.6/24 | 24117248
192.168.1.2/24 | 3145728
10.1.1.7/24 | 4194304
10.1.1.8/24 | 8388608
10.1.1.2/24 | 90177536
10.1.1.4/24 | 24117248
(6 rows)
In general, PostgreSQL's ability to use SQL in function definition is something great (1 line of SQL instead of 10 PL/SQL - Oracle is bitten :) ). However, there are differences from Oracle's behaviour to remember...
