pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction valid only for one table
5+ messages / 3 participants
[nested] [flat]
* Function valid only for one table
@ 2004-05-19 14:42 Martin Knipper <knipper@mk-os.de>
2004-05-19 16:33 ` Re: Function valid only for one table Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Martin Knipper @ 2004-05-19 14:42 UTC (permalink / raw)
To: pgsql-sql
Hi there,
is it possible to define a function that is only valid in a context
of one table ?
For Example:
select someFunction() from someTable;
-> OK
select someFunction() from anotherTable;
-> Error: someFunction unkown
Of course both tables are in the same database.
Greetings,
Martin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function valid only for one table
2004-05-19 14:42 Function valid only for one table Martin Knipper <knipper@mk-os.de>
@ 2004-05-19 16:33 ` Richard Huxton <dev@archonet.com>
2004-05-19 17:09 ` Re: Function valid only for one table Martin Knipper <knipper@mk-os.de>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2004-05-19 16:33 UTC (permalink / raw)
To: Martin Knipper <knipper@mk-os.de>; +Cc: pgsql-sql
Martin Knipper wrote:
> Hi there,
>
> is it possible to define a function that is only valid in a context
> of one table ?
>
> For Example:
>
> select someFunction() from someTable;
> -> OK
>
> select someFunction() from anotherTable;
> -> Error: someFunction unkown
Um, no. What do you want someFunction() to do?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function valid only for one table
2004-05-19 14:42 Function valid only for one table Martin Knipper <knipper@mk-os.de>
2004-05-19 16:33 ` Re: Function valid only for one table Richard Huxton <dev@archonet.com>
@ 2004-05-19 17:09 ` Martin Knipper <knipper@mk-os.de>
2004-05-19 17:33 ` Re: Function valid only for one table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Martin Knipper @ 2004-05-19 17:09 UTC (permalink / raw)
To: pgsql-sql
Am 19.05.2004 18:33 schrieb Richard Huxton:
> Martin Knipper wrote:
>
>>Hi there,
>>
>>is it possible to define a function that is only valid in a context
>>of one table ?
>>
>>For Example:
>>
>>select someFunction() from someTable;
>>-> OK
>>
>>select someFunction() from anotherTable;
>>-> Error: someFunction unkown
>
>
> Um, no. What do you want someFunction() to do?
>
I thought about a mixture of object orientation and a normal table
for a relational database.
someFunction() is a method for the object/table someTable.
Since AFAIK postgres doesn't support calling methods of types or
objects I thought about a workaround. That's why is ask the above
question.
By the way, does anyone know if there is something like a standard
for object relational databases ? An URL would be nice.
Thanks and Greetings,
Martin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function valid only for one table
2004-05-19 14:42 Function valid only for one table Martin Knipper <knipper@mk-os.de>
2004-05-19 16:33 ` Re: Function valid only for one table Richard Huxton <dev@archonet.com>
2004-05-19 17:09 ` Re: Function valid only for one table Martin Knipper <knipper@mk-os.de>
@ 2004-05-19 17:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-05-19 17:41 ` Re: Function valid only for one table Martin Knipper <knipper@mk-os.de>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2004-05-19 17:33 UTC (permalink / raw)
To: Martin Knipper <knipper@mk-os.de>; +Cc: pgsql-sql
Martin Knipper <knipper@mk-os.de> writes:
> I thought about a mixture of object orientation and a normal table
> for a relational database.
> someFunction() is a method for the object/table someTable.
Perhaps you could do what you want with functions accepting table
rowtypes.
create function someFunction(someTable) returns ...
select someFunction(t.*) from someTable t where ...
-- works
select someFunction(t.*) from anotherTable t where ...
-- fails
create function someFunction(anotherTable) returns ...
select someFunction(t.*) from someTable t where ...
-- still works
select someFunction(t.*) from anotherTable t where ...
-- now calls the second someFunction()
As of existing releases this doesn't scale well to large tables, because
passing a whole table row to a function leaks memory intraquery. That
is fixed for 7.5 though.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function valid only for one table
2004-05-19 14:42 Function valid only for one table Martin Knipper <knipper@mk-os.de>
2004-05-19 16:33 ` Re: Function valid only for one table Richard Huxton <dev@archonet.com>
2004-05-19 17:09 ` Re: Function valid only for one table Martin Knipper <knipper@mk-os.de>
2004-05-19 17:33 ` Re: Function valid only for one table Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-05-19 17:41 ` Martin Knipper <knipper@mk-os.de>
0 siblings, 0 replies; 5+ messages in thread
From: Martin Knipper @ 2004-05-19 17:41 UTC (permalink / raw)
To: pgsql-sql
Hi Tom,
Am 19.05.2004 19:33 schrieb Tom Lane:
> Martin Knipper <knipper@mk-os.de> writes:
>
>>I thought about a mixture of object orientation and a normal table
>>for a relational database.
>>someFunction() is a method for the object/table someTable.
>
> Perhaps you could do what you want with functions accepting table
> rowtypes.
>
> create function someFunction(someTable) returns ...
>
> select someFunction(t.*) from someTable t where ...
> -- works
>
> select someFunction(t.*) from anotherTable t where ...
> -- fails
>
> create function someFunction(anotherTable) returns ...
>
> select someFunction(t.*) from someTable t where ...
> -- still works
>
> select someFunction(t.*) from anotherTable t where ...
> -- now calls the second someFunction()
>
> As of existing releases this doesn't scale well to large tables, because
> passing a whole table row to a function leaks memory intraquery. That
> is fixed for 7.5 though.
>
Thanks for those examples. Can you tell me, if this is something
that is defined in the SQL 92/99/03 Standards ?
Greetings,
Martin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-05-19 17:41 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-19 14:42 Function valid only for one table Martin Knipper <knipper@mk-os.de>
2004-05-19 16:33 ` Richard Huxton <dev@archonet.com>
2004-05-19 17:09 ` Martin Knipper <knipper@mk-os.de>
2004-05-19 17:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-05-19 17:41 ` Martin Knipper <knipper@mk-os.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox