Обсуждение: 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