Let's try to use polymorphism in PostgreSQL. This could be quite a useful
feature in many situations.
We create two tables, one inheriting from the other, then define functions
with the same name
on both of them, but with different internal logic. When we call the
function on all records of
the parent class, we want the subclass's variant to be called for the
records corresponding to
the subclass.
However, the syntax of function definitions for a table expects the table
name to be passed to the
function as an argument. Therefore, it does not seem possible to
transparently call different
functions depending on the class of a record.
Is there a way to do this? Is the syntax described in this example wrong?
Create a class "employee" with key "id" and attributes "name" and "salary".
Define a function
"getTax()" that calculates tax as 20% of salary. Insert a record into it.
Now create a subclass of "employee" called "manager" with one extra
attribute "dept", and insert
a record into it. Define a function "getTax()" for "manager" that calculates
tax as 25% of salary.
(Let managers pay more tax ;-)
If we call "getTax()" on all records of "employee", we want regular
employees to be shown taxed at 20%,
but managers taxed at 25%. That is polymorphic behaviour.
However, we are only able to invoke the employee version of getTax() on the
employee table. Application
of the manager version is only possible (explicitly) on the manager table.
STEPS:
1. Run the script "setup.sql". This creates the two tables, populates them
with a record each, and
creates the "getTax()" functions.
2. Run the script "test1.sql". This invokes the "getTax()" method on the
"employee" table for
all records. Only the employee version is called.
gettax
--------
20000
20000
(2 rows)
This is not what we want. We want "test1.sql" to return the following result
(i.e. applying
the manager variant of getTax() to the manager record):
gettax
--------
20000
25000 <-- Manager variant of getTax() should be used here
(2 rows)
3. Run the script "test2.sql". This explicitly invokes the "getTax()" method
on the "manager" table
for all records corresponding to the child class (manager). This calls the
manager variant.
gettax
--------
25000
(1 row)
This is correct, but we shouldn't have to call this variant explicitly.
Polymorphism should
cause it to be called implicitly.
------ Start of scripts --------
setup.sql:
----------
drop function getTax( t_employee );
drop function getTax( t_manager );
drop table t_manager;
drop table t_employee;
/*
Employees have an id (key), a name and a salary.
*/
create table t_employee
(
id int4 primary key,
name varchar(50) not null,
salary float8
);
/*
Managers are employees who manage a department.
*/
create table t_manager
(
dept char(2)
)
inherits (t_employee);
/*
An ordinary employee.
*/
insert into t_employee
values
(
1,
'Joe Bloggs',
100000.0
);
/*
A manager.
*/
insert into t_manager
values
(
2,
'John Doe',
100000.0,
'HR'
);
/*
A "method" defined for the "employee" class,
which should be inherited by the "manager" class.
*/
create function getTax( t_employee ) returns float8 as '
declare
emp alias for $1;
begin
return emp.salary * 0.2;
end;
' language 'plpgsql';
/*
A "method" defined for the "manager" class,
which should override that defined for the
"employee" class.
*/
create function getTax( t_manager ) returns float8 as '
declare
mgr alias for $1;
begin
return mgr.salary * 0.25;
end;
' language 'plpgsql';
test1.sql:
----------
/*
Calculate tax for all employees.
*/
select getTax( t_employee ) from t_employee;
test2.sql:
----------
/*
The "method" for managers has to be explicitly called.
The "method" defined for managers should be implicitly called
even if the record is in the "employee" table.
*/
select getTax( t_manager ) from t_manager;
------ End of scripts --------
Ganesh Prasad
Chief Web Architect
Reply2(tm) Ltd
Tel (+ 61 2) 9339 2912
==========================================================
PRIVILEGED - PRIVATE AND CONFIDENTIAL
This electronic mail is solely for the use of the addressee and may contain
information which is confidential or privileged.
If you are not the intended recipient any use, distribution, disclosure or
copying of this information is prohibited. If you receive this electronic
mail in error, please delete it and any attachments from your system
immediately and notify the sender by electronic mail or using any of the
following contact details
Except as required at law, Reply2(tm) Pty Ltd does not represent, warrant
and/or guarantee that the integrity of this communication has been
maintained nor that the communication is free of errors, virus, interception
or interference.
Reply2(tm) Ltd Phone: +612 9339 2900
GPO Box 794 Facsimile: +612 9339 2933
KINGS CROSS 1340 Email: reply2@reply2.com
NSW AUSTRALIA Website: http://www.reply2.com/
==========================================================
