SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-11-2008, 07:57 AM
| ||||
| ||||
 Expression index with function based on current_user? I have private tables such as this (Very simplified): CREATE TABLE private.t_foo ( ... ); REVOKE ALL ON private.t_foo FROM public; Different users have access to different data in the table, so I do this view: CREATE VIEW foo AS SELECT * FROM private.t_foo WHERE private.haveaccess(); GRANT ALL ON foo TO public; Using rules I then make the view updatable (In reality views fetch and write data to multiple tables usually). This works fine, but I have some thought on performance in the future. The function private.haveaccess()'s result depends on the currently logged in user, is it still possible to create an expression index over that function? // Fredrik Olsson ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly      |
|
04-11-2008, 07:57 AM
| |||
| |||
| Re: Expression index with function based on current_user? > >The function private.haveaccess()'s result depends on the currently logged >in user, is it still possible to create an expression index over that >function? > >// Fredrik Olsson > Hello, All functions and operators used in an index definition must be "immutable", that is, their results must depend only on their arguments and never on any outside influence. ... And your function is vollatile => you can't to do expression index. Regards Pavel Stehule __________________________________________________ _______________ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly |
|
04-11-2008, 07:57 AM
| |||
| |||
| Re: Expression index with function based on current_user? Ühel kenal päeval, K, 2006-02-08 kell 12:26, kirjutas Fredrik Olsson: > I have private tables such as this (Very simplified): > CREATE TABLE private.t_foo ( > ... > ); > REVOKE ALL ON private.t_foo FROM public; > > Different users have access to different data in the table, so I do > this view: > CREATE VIEW foo AS > SELECT * FROM private.t_foo WHERE private.haveaccess(); > GRANT ALL ON foo TO public; > > Using rules I then make the view updatable (In reality views fetch and > write data to multiple tables usually). This works fine, but I have some > thought on performance in the future. > > The function private.haveaccess()'s result depends on the currently > logged in user, is it still possible to create an expression index over > that function? No, but you probably can crete index on static function user_has_access(username) and then use that func in private.haveaccess() ------------ Hannu ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly |
|
04-11-2008, 07:57 AM
| ||||
| ||||
| Re: Expression index with function based on current_user? Fredrik Olsson <fredrik.olsson@treyst.se> writes: > CREATE VIEW foo AS > SELECT * FROM private.t_foo WHERE private.haveaccess(); > GRANT ALL ON foo TO public; If the function is parameterless as you show, what is it accomplishing that could not be done with grant/revoke on the view? If it is not parameterless, you had better be more specific about what it depends on. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org |
Linear Mode
