Thread: schema level variables and deferrable unique constraints
<br /><font face="sans-serif" size="2">sorry my last mail went incomplete</font><br /><font color="#800080" face="sans-serif"size="1">please read through this and ignore my last mail</font><br /><font color="#800080" face="sans-serif"size="1">paraM</font><br /><font color="#800080" face="sans-serif" size="1"> </font><br /><font color="#800080"face="sans-serif" size="1">----- Forwarded by Paramveer Singh/Trilogy on 20/09/2004 05:21 PM -----</font><br/><table width="100%"><tr valign="top"><td></td><td><font face="sans-serif" size="1"><b>Paramveer Singh</b></font><p><fontface="sans-serif" size="1">20/09/2004 05:56 PM</font><br /></td><td><font face="Arial" size="1"> </font><br /><font face="sans-serif" size="1"> To: pgsql-hackers@postgresql.org</font><br /><fontface="sans-serif" size="1"> cc: </font><br /><font face="sans-serif" size="1"> Subject: schema level variables and deferrable unique constraints</font></td></tr></table><br /><br /><font face="sans-serif" size="2">hi!</font><br/><font face="sans-serif" size="2">I apologize for mailing on the hackers list but I posted on thegeneral list and did not get a good answer.</font><br /><font face="sans-serif" size="2">I have two independent issues,but am addressing them together.</font><br /><br /><font face="sans-serif" size="2">1.</font><br /><font face="sans-serif"size="2">Oracle has package level variables which are not mappable to any postgres equivalent.</font><br/><font face="sans-serif" size="2">could we have something like schema scope variables which could bedirectly referred from let's say plpgsql functions?</font><br /><font face="sans-serif" size="2">Can someone give me somepointers on how to go about implementing this?</font><br /><br /><font face="sans-serif" size="2">2.</font><br /><fontface="sans-serif" size="2">Postgres does not support deferrable unique constraints.</font><br /><font face="sans-serif"size="2">we would however like to support this especially as some updates give non-standard behavior becauseof this.</font><br /><font face="sans-serif" size="2">where do I start reading up on what all needs to be done tosupport this?</font><br /><font face="sans-serif" size="2">I have no idea about the postgres codebase. I only understandthe implementation of the plpgsql library.</font><br /><font face="sans-serif" size="2">Any pointers on where tostart would be helpful</font><br /><br /><font face="sans-serif" size="2">thanks</font><br /><font face="sans-serif" size="2">paraM</font><br/>
Paramveer.Singh@trilogy.com writes:
> Oracle has package level variables which are not mappable to any postgres
> equivalent.
> could we have something like schema scope variables which could be
> directly referred from let's say plpgsql functions?
> Can someone give me some pointers on how to go about implementing this?
It seems more appropriate to me to store your values in a table
(variable name and value). I don't see any strong need for a
system-level facility for this.
regards, tom lane
Tom Lane wrote: > Paramveer.Singh@trilogy.com writes: > >>Oracle has package level variables which are not mappable to any postgres >>equivalent. >>could we have something like schema scope variables which could be >>directly referred from let's say plpgsql functions? >>Can someone give me some pointers on how to go about implementing this? > > > It seems more appropriate to me to store your values in a table > (variable name and value). I don't see any strong need for a > system-level facility for this. > Package variables are not transactional, additionaly they are session based - each session has it's own variables(values). You can assign initial(defult) values in package spec. Using table for them would also imply several columns for each datatype or several tables for them. Andre
Andre wrote: > Package variables are not transactional, additionaly they are > session based - each session has it's own variables(values). > You can assign initial(defult) values in package spec. > Using table for them would also imply several columns > for each datatype or several tables for them. I did a crude implementation of session variables for my OSCON tutorial which can be found here: http://www.joeconway.com/tut_oscon_2004.pdf See the slides with myfunc_setvar(), myfunc_getvar(), and myfunc_rmvar(). I have a slightly improved version that I'm using on a production system with good results. You can get the tarball here: http://www.joeconway.com/pgsession.tar.gz There was another implementation of session variables based on shared memory floating around the lists somewhere. You might try searching the archives for that also. HTH, Joe
Joe Conway wrote: > Andre wrote: > >> Package variables are not transactional, additionaly they are >> session based - each session has it's own variables(values). >> You can assign initial(defult) values in package spec. >> Using table for them would also imply several columns >> for each datatype or several tables for them. > > > I did a crude implementation of session variables for my OSCON > tutorial which can be found here: > http://www.joeconway.com/tut_oscon_2004.pdf > See the slides with myfunc_setvar(), myfunc_getvar(), and myfunc_rmvar(). > > I have a slightly improved version that I'm using on a production > system with good results. You can get the tarball here: > > http://www.joeconway.com/pgsession.tar.gz > > There was another implementation of session variables based on shared > memory floating around the lists somewhere. You might try searching > the archives for that also. > > In 8.0 plperl has %_SHARED, which is essentially a stash for session-persistent data of arbitrary complexity. It's one of the new features. Writing get/set functions for them would be entirely trivial. Seeding them with inital defaults would involve an explicit function call at session startup - we don't have that automated for plperl yet. cheers andrew