Polymorphism in the PostgreSQL object model - Mailing list pgsql-general

Hi,

Let me re-post an issue that I had posted to pgsql-bugs@postgresl.org almost 2
years ago. I got no response at all to that query, and meanwhile, the issue
has perhaps become more important because of all the furore in the Enterprise
Java world on the best way to persist objects.

Postgres has the ability to make that entire argument go away by providing
true object capability in the data store itself, so there's no need to
"persist" an object in memory. Objects are already persistent. If you write
your entire application logic as stored procedures, then the database itself
becomes your app server! Writing a thin Java layer that maps class attributes
and methods to database columns and procedures is then almost trivial.

Regards,
Ganesh Prasad

Here's the problem (basically that polymorphism isn't implemented):

----------------------------------------------------------------------------------------------

(10 July 2001)

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 --------