help on delete trigger.

Sundararajan wrote:

I am developing a db application in postgresql and i need to write a delete
trigger on one of the tables.

the environment is

table1

field1 varchar(64)
other fields.

table 2.

field1 varchar(64)
other fields

I need a delete trigger on the table 1, so that if I delete a row from table
1 , the corresponding rows from table 2 should also be deleted.

This is the code I have tried.

DROP FUNCTION ApplicationsDeleteFn();
CREATE FUNCTION ApplicationsDeleteFn()
RETURNS OPAQUE
AS '
BEGIN
delete from ports where appName=OLD.appName;
RETURN OLD;

END;
'
LANGUAGE 'plpgsql';

Please help me with this, as my work is time bound.

I don't see anything wrong with the trigger you have (except
that you use appName instead of field1).

But shouldn't that be a foreign key reference with ON DELETE
CASCADE and ON UPDATE CASCADE in the first place? This would
automatically delete the referencing rows, so no need for a
custom trigger. In addition, it'll ensure that you can't get
entries into ports for non-existent applications at all.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

This code looks fine to me, other than missing the actual trigger statement
is missing. Assuming table 1 is named apps:

DROP TRIGGER OnApplicationsDelete ON apps;
DROP FUNCTION ApplicationsDeleteFn();
CREATE FUNCTION ApplicationsDeleteFn()
RETURNS OPAQUE
AS '
BEGIN
delete from ports where appName=OLD.appName;
RETURN OLD;

END;
'
LANGUAGE 'plpgsql';
CREATE TRIGGER OnApplicationsDelete BEFORE DELETE ON apps FOR EACH ROW
EXECUTE PROCEDURE ApplicationsDeleteFn();

But the question is, would it be better to use a foreign key? If you put a
foreign key on ports.appName that REFERENCES apps.appName, you could define
it as an ON DELETE CASCADE relationship. Meaning that deleting the value
from the apps table would cascade that delete to the ports table.

Hope this helps,

Greg

Hi,

I'm pretty new to PostgreSQL so please bear with me if this is a newbie
question that has been answered before on this list.

I have created several tables containing a SERIAL column-type as primary
key.
What I would like to do is get the last value of these columns after Ive
inserted a new row.

I know I can do this either using nextval('sequence_name') or
curval('sequence_name').

My question:
is there anyway of retreiving the sequence_name corresponding to the
respective column,
knowing just the tablename and columnname?

The reason I need to do this, is because the application I write
dynamicly creates new tables, and I have no way of knowing the sequence
name before hand.

thanks in advance
Wieger
--
Wieger Uffink
tel: +31 20 428 6868
fax: +31 20 470 6905
web: http://www.usmedia.nl

My question:
is there anyway of retreiving the sequence_name corresponding to the
respective column,
knowing just the tablename and columnname?

The reason I need to do this, is because the application I write
dynamicly creates new tables, and I have no way of knowing the sequence
name before hand.

If it is very difficult finding that sequence name (I'm not sure)
You may want to create the sequence yourself instead of using SERIAL.
Then you would know the name.

Hi,

My question:
is there anyway of retreiving the sequence_name corresponding to the
respective column,
knowing just the tablename and columnname?

The reason I need to do this, is because the application I write
dynamicly creates new tables, and I have no way of knowing the sequence
name before hand.

If it is very difficult finding that sequence name (I'm not sure)
You may want to create the sequence yourself instead of using SERIAL.
Then you would know the name.

Not hard at all.

csmith=# create table test (testid serial, name text);
NOTICE: CREATE TABLE will create implicit sequence 'test_testid_seq' for
SERIAL column 'test.testid'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_testid_key'
for table 'test'
CREATE

Sequence name becomes <tablename>_<columnname>_seq

so if we use the serial datatype to create a column called "xyz" in the
table "abc" the sequence name is :

abc_xyz_seq

Make sense?

-----------------
Chris Smith
http://www.squiz.net/

On Wed, Aug 15, 2001 at 12:14:02PM +1000, Chris wrote:

If it is very difficult finding that sequence name (I'm not sure)
You may want to create the sequence yourself instead of using SERIAL.
Then you would know the name.

Not hard at all.

csmith=# create table test (testid serial, name text);
NOTICE: CREATE TABLE will create implicit sequence 'test_testid_seq' for
SERIAL column 'test.testid'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_testid_key'
for table 'test'
CREATE

Sequence name becomes <tablename>_<columnname>_seq

Actually, it's a bit more complicated than that. Since the maximum
identifier length is 32 or so, if the above identifier comes out too long,
various rules are applied to make it shorter.

If you want a guarenteed name, I suggest not using serial and building the
sequence yourself. Then you can use any naming convention you like.

--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/

On Tue, 14 Aug 2001, Wieger Uffink wrote:

My question:
is there anyway of retreiving the sequence_name corresponding to the
respective column,
knowing just the tablename and columnname?

The reason I need to do this, is because the application I write
dynamicly creates new tables, and I have no way of knowing the sequence
name before hand.

Try "tablename_fieldname_seq". That is the default sequence name for a
"serial" field.

Cheers.

Gordan

Hi,

First of all thanks for the reactions, but I think I have probably not
made my problem clear enough.

I know the sequence name will be some combination, usually as
'tablename_columnname_seq',
when implicitly created during table creation.
But when I create my table 'maincompetencies' with serial column
'maincompetencid'
the sequence is named 'maincompetenc_maincompetenc_seq'. Clearly not the
same as the above mentioned naming formula.

I have a hunch that table and column names are cut off after the first
13 characters, but im not sure if this is true, or will maybe change in
future versions of postgre.

What I would really like to do is just query Postgre, give the tablename
and columnname in question, and retreive the exact sequencename. I know
Postgre 'knows' this, it has that info stored somewhere, it just wont
tell me :)

thanks,

Wieger

Chris wrote:

Hi,

My question:
is there anyway of retreiving the sequence_name corresponding to the
respective column,
knowing just the tablename and columnname?

The reason I need to do this, is because the application I write
dynamicly creates new tables, and I have no way of knowing the sequence
name before hand.

If it is very difficult finding that sequence name (I'm not sure)
You may want to create the sequence yourself instead of using SERIAL.
Then you would know the name.

Not hard at all.

csmith=# create table test (testid serial, name text);
NOTICE: CREATE TABLE will create implicit sequence 'test_testid_seq' for
SERIAL column 'test.testid'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_testid_key'
for table 'test'
CREATE

Sequence name becomes <tablename>_<columnname>_seq

so if we use the serial datatype to create a column called "xyz" in the
table "abc" the sequence name is :

abc_xyz_seq

Make sense?

-----------------
Chris Smith
http://www.squiz.net/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
Wieger Uffink
tel: +31 20 428 6868
fax: +31 20 470 6905
web: http://www.usmedia.nl

On Wed, Aug 15, 2001 at 10:49:38AM +0200, Wieger Uffink wrote:

Hi,

First of all thanks for the reactions, but I think I have probably not
made my problem clear enough.

I know the sequence name will be some combination, usually as
'tablename_columnname_seq',
when implicitly created during table creation.
But when I create my table 'maincompetencies' with serial column
'maincompetencid'
the sequence is named 'maincompetenc_maincompetenc_seq'. Clearly not the
same as the above mentioned naming formula.

Yes, the max identifier length is 32.

I have a hunch that table and column names are cut off after the first
13 characters, but im not sure if this is true, or will maybe change in
future versions of postgre.

It's a tricky algorithm.

What I would really like to do is just query Postgre, give the tablename
and columnname in question, and retreive the exact sequencename. I know
Postgre 'knows' this, it has that info stored somewhere, it just wont
tell me :)

Well, it would be stored in the default value field of the column. You could
try to parse it out of there. I suggest however not using the serial
keyword, but instead building it yourself. Then you could call you sequence
maincompetencies_pseq or whatever you like.

Parsing it out of the system tables might not work in future versions
either. The only guarenteed way is to name them yourself.

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/

I think someone pointed out not long ago that that naming convention isn't
always 100% reliable. How about creating the sequence dynamically (using that
naming convention even), then create the table dynamically and have it
explicitly use the sequence you just created. That way you *know* the name of
the sequence; should probably check to be sure the name isn't already in use, or
trap for errors when creating the sequence and adjust accordingly.

Gordan Bobic <gordan%bobich.net@interlock.lexmark.com> on 08/15/2001 04:14:02 AM

To: Wieger Uffink <wieger%usmedia.nl@interlock.lexmark.com>
cc: PostgreSQL-General <pgsql-general%postgresql.org@interlock.lexmark.com>
(bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: Re: [GENERAL] nextval, sequences and sequencenames

On Tue, 14 Aug 2001, Wieger Uffink wrote:

My question:
is there anyway of retreiving the sequence_name corresponding to the
respective column,
knowing just the tablename and columnname?

The reason I need to do this, is because the application I write
dynamicly creates new tables, and I have no way of knowing the sequence
name before hand.

Try "tablename_fieldname_seq". That is the default sequence name for a
"serial" field.

Cheers.

Gordan

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Hi,

Im accessesing a postgreSQL db through JDBC, and I would like take make
use of the method, getErrorCode in the SQLException class as in
java.sql.

So far I have been able to retreive exception messages but not the error
codes in case of an SQL exception.

I would like to know if postgreSQL propegates any errorcodes at all,
since I found some post earlier on this list saying it was not
implemented, but that post didnt refer to any specific version. If it is
could someone also point out to me where these codes are defined?

I could ofcourse base my excepion handling on the messages but I'd
rather use codes.

thanks in advance,
Wieger
--
Wieger Uffink
tel: +31 20 428 6868
fax: +31 20 470 6905
web: http://www.usmedia.nl

Wieger Uffink writes:

I would like to know if postgreSQL propegates any errorcodes at all,

No.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

hehe thank you for your elaborate though very clear reply :)

thanks really

Wieger

Peter Eisentraut wrote:

Wieger Uffink writes:

I would like to know if postgreSQL propegates any errorcodes at all,

No.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

--
Wieger Uffink
tel: +31 20 428 6868
fax: +31 20 470 6905
web: http://www.usmedia.nl

Wieger,

The server does not have a concept of error codes currently (it is on
the TODO list). Therefore the JDBC driver has no error code to report
since it doesn't get one from the backend. When the server supports
error codes the JDBC driver will as well.

thanks,
--Barry

Wieger Uffink wrote: