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
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.
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.
This is not what we want. We want "test1.sql" to return the following result
the manager variant of getTax() to the manager record):
25000 <-- Manager variant of getTax() should be used here
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
This is correct, but we shouldn't have to call this variant explicitly.
cause it to be called implicitly.
------ Start of scripts --------
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,
Managers are employees who manage a department.
create table t_manager
An ordinary employee.
insert into t_employee
insert into t_manager
A "method" defined for the "employee" class,
which should be inherited by the "manager" class.
create function getTax( t_employee ) returns float8 as '
emp alias for $1;
return emp.salary * 0.2;
' language 'plpgsql';
A "method" defined for the "manager" class,
which should override that defined for the
create function getTax( t_manager ) returns float8 as '
mgr alias for $1;
return mgr.salary * 0.25;
' language 'plpgsql';
Calculate tax for all employees.
select getTax( t_employee ) from t_employee;
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 --------
Chief Web Architect
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
Reply2(tm) Ltd Phone: +612 9339 2900
GPO Box 794 Facsimile: +612 9339 2933
KINGS CROSS 1340 Email: reply2(at)reply2(dot)com
NSW AUSTRALIA Website: http://www.reply2.com/
pgsql-bugs by date
|Next:||From: Paul A. Lender||Date: 2001-07-10 17:41:19|
|Subject: OpenSSH, Windows Clients, and Incoming Tunnels|
|Previous:||From: Hiroshi Inoue||Date: 2001-07-10 03:39:42|
|Subject: Re: small ODBC problem|