Thread: inheritance and audit columns
hi everyone, i have several questions. first, i want to have audit columns in every table of my database. these should hold information about who has last edited a column on which time/date. to avoid a lot of repetition i would like to use inheritance. something like this: /** start of code **/ create table audit_cols ( mut_user varchar(100) not null default current_user, mut_timestamp timestamp not null default CURRENT_TIMESTAMP ); create table foo ( foo_id serial primary_key, foo_name varchar(100) ) inherits (audit_cols); create table bar ( bar_id serial primary_key, bar_name varchar(100) ) inherits (audit_cols); /** end of code **/ i have to think of some functions that will automatically fill the audit_cols columns, but that should not be a big problem, the idea is that i can have every table inheriting from audit_cols. what may cause a problem though is that i want to do multiple inheritance, something like: /** start of code **/ create table dummy ( dummy_id serial primary_key, dummy_name varchar(100) ) inherits (foo, bar); /** end of code **/ i have two questions about this statement: 1. the manual says that multiple inheritance is possible, but doesn't give an example. is the syntax "inherits (foo, bar)" correct? 2. both foo and bar have (inherited) columns called mut_user and mut_timestamp. can i expect a conflict when creating dummy? i couldn't find the answers neither in the online help nor in Bruces book, also online (maybe i didn't search good enough), so TIA for any pointers! -- Jules Alberts.
On Mon, 2002-01-14 at 14:20, Jules Alberts wrote: ... > what may cause a problem though is that i want to do multiple > inheritance, something like: > > /** start of code **/ > create table dummy ( > dummy_id serial primary_key, > dummy_name varchar(100) > ) inherits (foo, bar); > /** end of code **/ > > i have two questions about this statement: > > 1. the manual says that multiple inheritance is possible, but doesn't > give an example. is the syntax "inherits (foo, bar)" correct? > > 2. both foo and bar have (inherited) columns called mut_user and > mut_timestamp. can i expect a conflict when creating dummy? > > i couldn't find the answers neither in the online help nor in Bruces > book, also online (maybe i didn't search good enough), so TIA for any > pointers! Well, the simple method is to try it! (Which would show you that "primary_key" is wrong; it should be "primary key".): junk=# create table dummy ( junk(# dummy_id serial primary key, junk(# dummy_name varchar(100) junk(# ) inherits (foo, bar); NOTICE: CREATE TABLE will create implicit sequence 'dummy_dummy_id_seq' for SERIAL column 'dummy.dummy_id' NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'dummy_pkey' for table 'dummy' NOTICE: CREATE TABLE: merging multiple inherited definitions of attribute "mut_user" NOTICE: CREATE TABLE: merging multiple inherited definitions of attribute "mut_timestamp" CREATE Duplicate multiply inherited columns will be merged if they are of the same type. It is an error to have them of the same name but different types: junk=# create table try (mut_user char(10)); CREATE junk=# create table foobar (junk serial primary key) inherits (audit_cols, try); NOTICE: CREATE TABLE will create implicit sequence 'foobar_junk_seq' for SERIAL column 'foobar.junk' NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foobar_pkey' for table 'foobar' NOTICE: CREATE TABLE: merging multiple inherited definitions of attribute "mut_user" ERROR: CREATE TABLE: inherited attribute "mut_user" type conflict (varchar and bpchar) There are problems with inheritance with regard to inheritance of primry keys and use of parent tables in foreign key references; see in the todo details directory. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "For I know that my redeemer liveth, and that he shall stand at the latter day upon the earth" Job 19:25
Attachment
On 15 Jan 2002 at 10:18, Oliver Elphick wrote: > On Mon, 2002-01-14 at 14:20, Jules Alberts wrote: > ... > > what may cause a problem though is that i want to do multiple > > inheritance, something like: > > > > /** start of code **/ > > create table dummy ( > > dummy_id serial primary_key, > > dummy_name varchar(100) > > ) inherits (foo, bar); > > /** end of code **/ <snip> > Well, the simple method is to try it! (Which would show you that > "primary_key" is wrong; it should be "primary key".): <snip> hello Oliver, thanks for reacting. i was (and am) going to try it, but i also posted the question here hoping to hear from people who use this in live situations, and know more about postgresql in general. i will have a look at the problems in the todo's, thanks again. -- Jules Alberts
Hi all, After spending a day, i have been able to start postmaster on Windows 2000 Professional (using Cygwin) I am facing one problem. I have created database and users also. I can also able to create tables, insert rows in tables but when i issue SELECT, it gives error "less : not found". I am issuing a very basic select statement. Can anyone give any help. db=> CREATE TABLE test (aa numeric, bb text); CREATE db=> INSERT INTO TEST VALUES (100,'Denis'); INSERT 54555 1 db=> SELECT * FROM test; less : not found db=> Thanx Denis
On Wed, 2002-01-16 at 05:29, denis@coralindia.com wrote: > Hi all, > > After spending a day, i have been able to start postmaster on Windows 2000 > Professional (using Cygwin) > > I am facing one problem. I have created database and users also. I can also > able to create tables, insert rows in tables but when i issue SELECT, it > gives error "less : not found". I am issuing a very basic select statement. > Can anyone give any help. > > db=> CREATE TABLE test (aa numeric, bb text); > CREATE > db=> INSERT INTO TEST VALUES (100,'Denis'); > INSERT 54555 1 > db=> SELECT * FROM test; > less : not found > db=> I have no experience of Cygwin, but less is a pager filter that is very commonly used in Unix. The pager is normally set by the environment variable PAGER; so if you have "more" but not "less", set PAGER=more before you start psql. (Or unset PAGER; "more" is used by default if PAGER is not set.) You can use "\pset pager" in psql to toggle the use of the pager on and off. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Jesus said unto her, I am the resurrection, and the life; he that believeth in me, though he were dead, yet shall he live" John 11:25
Attachment
Denis, I think the easiest way around this is to install less from the cygwin site, IIRC it is one of the standard packages that can be selected as part of the Cygwin install. hih steve boyle ----- Original Message ----- From: <denis@coralindia.com> To: <pgsql-novice@postgresql.org> Sent: Wednesday, January 16, 2002 5:29 AM Subject: [NOVICE] select !working > Hi all, > > After spending a day, i have been able to start postmaster on Windows 2000 > Professional (using Cygwin) > > I am facing one problem. I have created database and users also. I can also > able to create tables, insert rows in tables but when i issue SELECT, it > gives error "less : not found". I am issuing a very basic select statement. > Can anyone give any help. > > db=> CREATE TABLE test (aa numeric, bb text); > CREATE > db=> INSERT INTO TEST VALUES (100,'Denis'); > INSERT 54555 1 > db=> SELECT * FROM test; > less : not found > db=> > > Thanx > > Denis > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Thanx Oliver, It worked for me. Actually, PAGER was on and when i executed \PSET PAGER it turned off and now i can see the output of SELECT. Thanx once again. Denis ----- Original Message ----- From: "Oliver Elphick" <olly@lfix.co.uk> To: <denis@coralindia.com> Cc: <pgsql-novice@postgresql.org> Sent: Wednesday, January 16, 2002 4:14 PM Subject: Re: [NOVICE] select !working
Hi all, Is there any package / procedure in PostGres to send email (like UTL_SMTP in Oracle) Thanx Denis
You can send mail using: PgMail (http://sourceforge.net/projects/pgmail/) or PL/sh (http://www.ca.postgresql.org/~petere/plsh.html) I think using PL/sh you should be able to send mail by catting text through the 'mail' command. hih steve boyle ----- Original Message ----- From: <denis@coralindia.com> To: <pgsql-novice@postgresql.org> Sent: Thursday, January 17, 2002 8:39 AM Subject: [NOVICE] Sending Email > > Hi all, > > Is there any package / procedure in PostGres to send email (like UTL_SMTP in > Oracle) > > Thanx > > Denis > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Thanx Steve I am working on it... i think i'll get thru.. Denis ----- Original Message ----- From: "Steve Boyle (Roselink)" <boylesa@roselink.co.uk> To: <denis@coralindia.com> Cc: <pgsql-novice@postgresql.org> Sent: Thursday, January 17, 2002 3:54 PM Subject: Re: [NOVICE] Sending Email > You can send mail using: > > PgMail (http://sourceforge.net/projects/pgmail/) > or > PL/sh (http://www.ca.postgresql.org/~petere/plsh.html) > > I think using PL/sh you should be able to send mail by catting text through > the 'mail' command. > > hih > > steve boyle > > ----- Original Message ----- > From: <denis@coralindia.com> > To: <pgsql-novice@postgresql.org> > Sent: Thursday, January 17, 2002 8:39 AM > Subject: [NOVICE] Sending Email > > > > > > Hi all, > > > > Is there any package / procedure in PostGres to send email (like UTL_SMTP > in > > Oracle) > > > > Thanx > > > > Denis > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > >
denis@coralindia.com writes: > Hi all, > > Is there any package / procedure in PostGres to send email (like > UTL_SMTP in Oracle) > > Thanx > > Denis The way to do this in a transaction safe way is to have some sort of process that runs outside of PostgreSQL. You put the information necessary to generate your email into a table, and then you call the process (probably from cron). This makes it much easier to do error checking. Instead of firing triggers from PostgreSQL and hoping that the mail actually gets sent. You simply select from your outgoing mail table, create the necessary emails, and remove the entries in the table. This allows you to rollback changes to your database on failure, and guarantees that a problem with your email setup won't send valuable mail to the bit bucket. Besides, generating and sending email is probably easier to do from Python or Perl than any method available internally to PostgreSQL. Jason
On Thursday 17 January 2002 12:19 pm, Jason Earl wrote: > The way to do this in a transaction safe way is to have some sort of > process that runs outside of PostgreSQL. You put the information > necessary to generate your email into a table, and then you call the > process (probably from cron). > Besides, generating and sending email is probably easier to do from > Python or Perl than any method available internally to PostgreSQL. It would be trivial to do in perl with DBI and MailTools. -- Bud Rogers <budr@sirinet.net> All things in moderation. And not too much moderation either.
Wow... it worked for me in a single shot.. Thanx Denis ----- Original Message ----- From: <denis@coralindia.com> To: "Steve Boyle (Roselink)" <boylesa@roselink.co.uk> Cc: <pgsql-novice@postgresql.org> Sent: Thursday, January 17, 2002 5:13 PM Subject: Re: [NOVICE] Sending Email > Thanx Steve > > I am working on it... i think i'll get thru.. > > Denis > > ----- Original Message ----- > From: "Steve Boyle (Roselink)" <boylesa@roselink.co.uk> > To: <denis@coralindia.com> > Cc: <pgsql-novice@postgresql.org> > Sent: Thursday, January 17, 2002 3:54 PM > Subject: Re: [NOVICE] Sending Email > > > > You can send mail using: > > > > PgMail (http://sourceforge.net/projects/pgmail/) > > or > > PL/sh (http://www.ca.postgresql.org/~petere/plsh.html) > > > > I think using PL/sh you should be able to send mail by catting text > through > > the 'mail' command. > > > > hih > > > > steve boyle > > > > ----- Original Message ----- > > From: <denis@coralindia.com> > > To: <pgsql-novice@postgresql.org> > > Sent: Thursday, January 17, 2002 8:39 AM > > Subject: [NOVICE] Sending Email > > > > > > > > > > Hi all, > > > > > > Is there any package / procedure in PostGres to send email (like > UTL_SMTP > > in > > > Oracle) > > > > > > Thanx > > > > > > Denis > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org