Обсуждение: Function valid only for one table

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

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

I'm planning on dipping my toes into the world of schemata. I have tables,
created in the Public schema, that I'd like to move to the new schema:

SELECT * INTO new.tablename FROM public.tablename;
CREATE SEQUENCE ...;
CREATE INDEX ...;
ALTER TABLE ...;
BLAH ...;
BLAH ...;
BLAH ...;
DROP public.tablename;
REPEAT ...;
REPEAT ...;
REPEAT ...;
VOMIT;

Is there an easier, faster, less user-error-prone way around this? 

    
__________________________________
Do you Yahoo!?
SBC Yahoo! - Internet access at a great low price.
http://promo.yahoo.com/sbc/

Chris Gamache <cgg007@yahoo.com> writes:
> I'm planning on dipping my toes into the world of schemata. I have tables,
> created in the Public schema, that I'd like to move to the new schema:
> Is there an easier, faster, less user-error-prone way around this? 

pg_dump, edit the "set search_path" commands in the dump script, reload.
        regards, tom lane

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

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

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