Re: Sending Email

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(dot)Elphick(at)lfix(dot)co(dot)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

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(at)coralindia(dot)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(dot)Elphick(at)lfix(dot)co(dot)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

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(at)coralindia(dot)com>
To: <pgsql-novice(at)postgresql(dot)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(at)postgresql(dot)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(at)lfix(dot)co(dot)uk>
To: <denis(at)coralindia(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)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(at)coralindia(dot)com>
To: <pgsql-novice(at)postgresql(dot)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(at)roselink(dot)co(dot)uk>
To: <denis(at)coralindia(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)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(at)coralindia(dot)com>
> To: <pgsql-novice(at)postgresql(dot)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(at)coralindia(dot)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(at)sirinet(dot)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(at)coralindia(dot)com>
To: "Steve Boyle (Roselink)" <boylesa(at)roselink(dot)co(dot)uk>
Cc: <pgsql-novice(at)postgresql(dot)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(at)roselink(dot)co(dot)uk>
> To: <denis(at)coralindia(dot)com>
> Cc: <pgsql-novice(at)postgresql(dot)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(at)coralindia(dot)com>
> > To: <pgsql-novice(at)postgresql(dot)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