Thread: Polymorphic function calls
Is there any chance to implement polymorphic calls in PostgreSQL?
Consider the following definitions:
create table base_table (x integer, y integer);
create table derived_table (z integer) inherits (base_table);
create function volume(r base_table) returns integer as $$ begin return
r.x*r.y; end; $$ language plpgsql strict stable;
create function volume(r derived_table) returns integer as $$ begin
return r.x*r.y*r.z; end; $$ language plpgsql strict stable;
insert into base_table values (1,2);
insert into derived_table values (3,4,5);
postgres=# select * from base_table; x | y
---+--- 1 | 2 3 | 4
(2 rows)
postgres=# select volume(r) from base_table r; volume
-------- 2 12
(2 rows)
postgres=# select volume(r) from only base_table r union all select
volume(r_1) from only derived_table r_1; volume
-------- 2 60
(2 rows)
Execution plans of first and second queries are very similar.
The difference is that type of r_1 in first query is "base_table".
It is obvious that query should return fixed set of columns, so
select * from base_table;
can not return "z" column.
But passing direved_table type instead of base_table type to volume()
function for record belonging to derived_table seems to be possible and
not contradicting something, isn't it?
On Sun, Dec 29, 2013 at 8:44 AM, knizhnik <knizhnik@garret.ru> wrote: > create function volume(r base_table) returns integer as $$ begin return > r.x*r.y; end; $$ language plpgsql strict stable; > create function volume(r derived_table) returns integer as $$ begin return > r.x*r.y*r.z; end; $$ language plpgsql strict stable; [...] > Execution plans of first and second queries are very similar. > The difference is that type of r_1 in first query is "base_table". > It is obvious that query should return fixed set of columns, so > > select * from base_table; > > can not return "z" column. > But passing direved_table type instead of base_table type to volume() > function for record belonging to derived_table seems to be possible and not > contradicting something, isn't it? Correct. Postgres chooses a function based on the passed signature. When you specify base_table it will choose volume(base_table) and for base_table it will be volume(derived_table) as well. FYI, there is a common practice to follow the DRY principle with inheritance and polymorphic functions in Postgres. On your example it might be shown like this: create function volume(r base_table) returns integer as $$ begin return r.x*r.y; end; $$ language plpgsql strict stable; create function volume(r derived_table) returns integer as $$ begin return volume(r::base_table) *r.z; end; $$ language plpgsql strict stable; -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
On 12/30/2013 01:22 AM, Sergey Konoplev wrote: > On Sun, Dec 29, 2013 at 8:44 AM, knizhnik <knizhnik@garret.ru> wrote: >> create function volume(r base_table) returns integer as $$ begin return >> r.x*r.y; end; $$ language plpgsql strict stable; >> create function volume(r derived_table) returns integer as $$ begin return >> r.x*r.y*r.z; end; $$ language plpgsql strict stable; > [...] > >> Execution plans of first and second queries are very similar. >> The difference is that type of r_1 in first query is "base_table". >> It is obvious that query should return fixed set of columns, so >> >> select * from base_table; >> >> can not return "z" column. >> But passing direved_table type instead of base_table type to volume() >> function for record belonging to derived_table seems to be possible and not >> contradicting something, isn't it? > Correct. > > Postgres chooses a function based on the passed signature. When you > specify base_table it will choose volume(base_table) and for > base_table it will be volume(derived_table) as well. I think you mean "and for derived_table it will be base_table as well". Certainly I understand the reasons of such behavior and that it will be difficult to introduce some other non-contradictory model... But polymorphism is one of the basic features of OO approach. Definitely PostgreSQL is not OODBMS. But it supports inheritance. And I wonder if it is possible/desirable to make an exception for function invocation rules for derived tables? Actually a query on table with inherited tables is implemented as join of several independent table traversals. But for all derived tables we restrict context to the scope of base table. If it is possible to leave real record type when it is passed to some function, we can support polymorphic calls... Will it violate some principles/rules? I am not sure... > > FYI, there is a common practice to follow the DRY principle with > inheritance and polymorphic functions in Postgres. On your example it > might be shown like this: > > create function volume(r base_table) returns integer as $$ begin > return r.x*r.y; > end; $$ language plpgsql strict stable; > > create function volume(r derived_table) returns integer as $$ begin > return volume(r::base_table) *r.z; > end; $$ language plpgsql strict stable; > I do not completely understand you here. Function of derived class can refere to the overridden function when it's result depends on result of the overridden function. But it is not always true, for example you can derived class Circle from Ellipse, but formula for circle volume do not need to use implementation of volume for ellipse. In any case, my question was not how to implement polymorphic volume function. I asked whether it is possible to change PostgreSQL function lookup rules to support something like virtual calls.
On Mon, Dec 30, 2013 at 2:03 AM, knizhnik <knizhnik@garret.ru> wrote:
> On 12/30/2013 01:22 AM, Sergey Konoplev wrote:
>> On Sun, Dec 29, 2013 at 8:44 AM, knizhnik <knizhnik@garret.ru> wrote:
>>> But passing direved_table type instead of base_table type to volume()
>>> function for record belonging to derived_table seems to be possible and
>>> not
>>> contradicting something, isn't it?
>>
>> Correct.
>>
>> Postgres chooses a function based on the passed signature. When you
>> specify base_table it will choose volume(base_table) and for
>> base_table it will be volume(derived_table) as well.
>
> I think you mean "and for derived_table it will be base_table as well".
Sure. Sorry for the typo.
> Certainly I understand the reasons of such behavior and that it will be
> difficult to introduce some other non-contradictory model...
>
> But polymorphism is one of the basic features of OO approach. Definitely
> PostgreSQL is not OODBMS. But it supports inheritance.
> And I wonder if it is possible/desirable to make an exception for function
> invocation rules for derived tables?
>
> Actually a query on table with inherited tables is implemented as join of
> several independent table traversals.
I would say it is more like union,
> But for all derived tables we restrict context to the scope of base table.
> If it is possible to leave real record type when it is passed to some
> function, we can support polymorphic calls...
>
> Will it violate some principles/rules? I am not sure...
Well, it is not implemented in Postgres.
>> FYI, there is a common practice to follow the DRY principle with
>> inheritance and polymorphic functions in Postgres. On your example it
>> might be shown like this:
>>
>> create function volume(r base_table) returns integer as $$ begin
>> return r.x*r.y;
>> end; $$ language plpgsql strict stable;
>>
>> create function volume(r derived_table) returns integer as $$ begin
>> return volume(r::base_table) *r.z;
>> end; $$ language plpgsql strict stable;
>
> I do not completely understand you here.
> Function of derived class can refere to the overridden function when it's
> result depends on result of the overridden function.
> But it is not always true, for example you can derived class Circle from
> Ellipse, but formula for circle volume do not need to use implementation of
> volume for ellipse.
Sure, it is not universal thing.
> In any case, my question was not how to implement polymorphic volume
> function.
> I asked whether it is possible to change PostgreSQL function lookup rules to
> support something like virtual calls.
You can use this hack to make it.
create or replace function volume(r base_table, t text) returns integer as $$
declare v integer;
begin execute format('select volume(r) from %I r where r.x = %L', t,
r.x) into v;
return v;
end; $$ language plpgsql;
select volume(r, tableoid::regclass::text) from base_table r;volume
-------- 2 60
(2 rows)
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com