Hello,

Is there any way to set up overloaded functions whose parameters are
composite types (i.e., row types) within a table inheritance hierarchy, so
that postgresql dispatches to the correct function depending on the actual
type of its argument, in an "object-oriented" fashion? What I mean is this.
Suppose I create a table for CITIES and another table for CAPITALS which
inherits from CITIES. Can I create same-named overloaded functions on
CITIES and CAPITALS in such a way that the "correct" function is called
depending on the actual type of the row?

Cheers,
David A. Ventimiglia
DSSG
Wells Fargo Bank

This message may contain confidential and/or privileged information. If you
are not the addressee or authorized to receive this for the addressee, you
must not use, copy, disclose, or take any action based on this message or
any information herein. If you have received this message in error, please
advise the sender immediately by reply e-mail and delete this message. Thank
you for your cooperation.

<<Ventimiglia, David.vcf>>


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

David.Ventimiglia (AT) wellsfargo (DOT) com writes:
AFAIK this has always worked: functions on parent tables can be called
on rows of child tables. What problem are you hitting exactly (and
which PG version are you trying it in)?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Creating a "toy" example to test it out, I have these tables:
create table cities (
name text,
population float,
altitude int
)

create table capitals (
state char(2)
) inherits (cities)

with this data:
insert into cities(name, population, altitude) values('Detroit', 10, 10);
insert into cities(name, population, altitude) values('Boston', 20, 20);
insert into cities(name, population, altitude) values('San Francisco', 30,
30);
insert into capitals(name, population, altitude, state) values('Denver',
40, 40, 'CO');
insert into capitals(name, population, altitude, state)
values('Sacramento', 50, 50, 'CA');

and these functions:
create function change_population(cities)
returns float
as '
select $1.population * 2 as population;
'
language sql;

create function change_population(capitals)
returns float
as '
select $1.population * -2 as population;
'
language sql;

I want to call the function in some way so that the correct implementation
is invoked depending on the type of the row, either CITIES or CAPITALS.
Something like this:
select name, change_population(cities.*)
from cities;

I want to get results like this:
Detroit 20
Boston 40
San Francisco 60
Denver -80
Sacramento -100

Instead I get results like this:
Detroit 20
Boston 40
San Francisco 60
Denver 80
Sacramento 100

So the problem I'm having is that PostgreSQL seems to be dispatching to the
implementation of the function based on the declared type of the argument
passed in during invocation (e.g., "cities"), rather than on the actual
types of the various rows (i.e., some rows are "cities" while others are of
a derived type, "capitals"). It's likely I'm using it incorrectly. Any
advice on how to use it correctly? Thanks!

Regards,
David

-----Original Message-----
From: Tom Lane [mailto:tgl (AT) sss (DOT) pgh.pa.us]
Sent: Friday, July 09, 2004 8:16 PM
To: David.Ventimiglia (AT) wellsfargo (DOT) com
Cc: pgsql-novice (AT) postgresql (DOT) org
Subject: Re: [NOVICE] table inheritance and polymorphic functions


David.Ventimiglia (AT) wellsfargo (DOT) com writes:
AFAIK this has always worked: functions on parent tables can be called
on rows of child tables. What problem are you hitting exactly (and
which PG version are you trying it in)?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org