[SQL] defaults on updates
Hi, I'm trying to make some sort of function, rule, trigger, or what ever that would be capable of modifing my table on an update. Basically, I have 5 fields, one being a PK (SERIAL), 3 with information, and the last one a timestamp field that will show the last time the register was modified (tmodif which has a DEFAULT CURRENT_TIMESTAMP). Now, eveytime someone modifys any, or all of the 3 information fields, I want the tmodif field of that register to be set to now(). I tried with rules, but just didn't work (obviously, and not so), even with INSTEAD. The only way I see of doing it is through a view/rule, but I would leave that as the last resource. Any ideas? -- 10:43:01 up 10 days, 18:05, 4 users, load average: 0.95, 0.44, 0.35 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] defaults on updates
Martin Marques <[EMAIL PROTECTED]> writes: > Basically, I have 5 fields, one being a PK (SERIAL), 3 with information, and > the last one a timestamp field that will show the last time the register was > modified (tmodif which has a DEFAULT CURRENT_TIMESTAMP). > Now, eveytime someone modifys any, or all of the 3 information fields, > I want the tmodif field of that register to be set to now(). This is trivial to do with a trigger --- you just need something like NEW.lastmod := now(); RETURN NEW; There's a complete example at the bottom of this page: http://developer.postgresql.org/docs/postgres/plpgsql-trigger.html regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] conditional query?
As you say, it is easiest if there is only one level of 'parenting'..! SELECT p.id, CASE WHEN p.UseParentAddress = 'Y' THEN (SELECT address FROM profile WHERE id = p.parentid) ELSE address AS address FROM Profile p If you do have multiple levels, you'll need to recurse, either with a cursor or a while loop - it might make sense to build a temporary table of all profiles for which you need addresses, and then loop around it until they are all filled in (or similar). Cheers, Matt Mlunnon @ RWA <[EMAIL PROTECTED]> wrote : > Try something like > > SELECT p2.* > FROM profile p1, profile p2 > WHERE ( p1.id =1 AND useParenAddres = 'N' AND p2.id = p1.id ) > OR ( p1.id =1 AND useParenAddres = 'Y' AND p2.id = p1.parentId) > > Obviously this won't work if you have more than one level of parent > hood, i.e. it would pick up a grand parent. If this is the case then > there is some kind of tree walking functionality in Postgres but I > don't know how portable this is or whether it will solve your problem. > > Another way to solve the grand parent thing would be to define a > recursive function. > > Happy coding. > > Cheers > Matthew > > > Achilleus Mantzios wrote: > > Why dont you try a combination of > CASE WHEN ... THEN ... ELSE ... END construct > along with a LEFT OUTER join (in case parentId is null). > > Not sure how "portable" the above will be. > > O kyrios Frank Morton egrapse stis Oct 31, 2003 : > > > > I have a table called a "profile" that has company addresses as well > as individual contact information. Simpifying: > > id (the primary key) > parentId (any profile can have a parent ie, contact parent is a company) > address (for this example, will just have one piece of the address) > useParentAddress > > If "useParentAddress"=="Y", that means that the parent address of this > person should really be used for mailings. If == "N" then the address > with that profile is the right one to use. > > Is there any way to do a single select to get a single "address" back > that is the right one depending on the value of "useParentAddress" > field? > > Also want to make this sql as portable as possible. > > Will appreciate any ideas. > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings --Matt ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] connectby
> I use postgresql 7.2.3 > How can I use connectby ?? > > Must I install files ? or packages ? or it is recommanded to upgrade dataserver ? For recent versions of PostgreSQL, go into the contrib/tablefunc directory and see the readme file for how to install. I downloaded PostgreSQL 7.2.3 and there was no contrib/tablefunc directory. You'll have to upgrade. George Essig ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
