Hi all,

I have a table something like this....
table Alarm(
AlarmId integer,
AlarmName varchar,
Sentflag smallint,
AckFlag smallint,
RTNFlag smallint,
AutoRTNFlag smallint,
cookie long);

I am trying to write a trigger on this table for insert and update
operations.
In the above table cookie field is not unique....there can be a max of 2
tuples with a given cookie number.
Now in the trigger function i check if there are more than one tuple with
the cookie number of the tuple being modified or inserted into the table.
If there are 2 tuples with the same cookie, i need to check if
SentFlag,AckFlag,RTNFlag of both the tables are equal to 1...if so delete
both the tuples from the table.
I am not able to refer to the tuples in the function....how can i refer to
the fields of both the tuples.

The trigger function is something like this

CREATE FUNCTION PP_DeleteAlarm() RETURNS TRIGGER AS '
DECLARE
number INTEGER = 0;
BEGIN
--check if the previous operation on the table is UPDATE
IF TG_OP = ''UPDATE'' OR TG_OP = ''INSERT'' THEN

SELECT INTO number COUNT(*) FROM Alarm WHERE Cookie =
NEW.Cookie;

IF number > 1 THEN

--check for the 3 flags of both the tuples -- how ???

--check if all the three flags in the Alarm table are 0
IF NEW.Sent = 1 AND NEW.Ack = 1 AND NEW.RTN = 1 THEN

--Delete the tuple from the table
DELETE FROM Alarm
WHERE PointNum = NEW.PointNum;

END IF;

END IF;

RETURN OLD;

END ;
' LANGUAGE 'plpgsql';



With Best Regards,
Pradeep Kumar P.J


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Wed, Oct 06, 2004 at 10:26:00PM -0700, Pradeepkumar, Pyatalo (IE10) wrote:
PostgreSQL doesn't have a LONG type -- perhaps you mean BIGINT.

Does the application guarantee the 2-tuple limit or does the database
need to enforce it? If the latter, then what should happen if more
than 2 tuples are inserted?

The trigger function below, fired after inserts and updates, might
be close to what you need. However, it doesn't enforce the 2-tuple
limit -- it only contains the logic to delete records based on the
criteria you specified. It worked in the minimal tests I performed,
but I'd recommend doing more thorough testing before using it in
production.

If this isn't what you're looking for, then please clarify your
requirements.

CREATE OR REPLACE FUNCTION PP_DeleteAlarm() RETURNS TRIGGER AS '
DECLARE
row RECORD;
BEGIN
-- Does this record meet the criteria for deletion?
IF NEW.SentFlag = 1 AND NEW.AckFlag = 1 AND NEW.RTNFlag = 1 THEN

-- Look for another record for this cookie that also meets
-- the criteria for deletion.
SELECT INTO row AlarmId
FROM Alarm
WHERE cookie = NEW.cookie
AND AlarmId <> NEW.AlarmId
AND SentFlag = 1
AND AckFlag = 1
AND RTNFlag = 1;

-- If we found another record then delete them both.
IF FOUND THEN
DELETE FROM Alarm WHERE AlarmId = NEW.AlarmId OR AlarmId = row.AlarmId;
-- or perhaps WHERE cookie = NEW.cookie
END IF;
END IF;

RETURN NULL;
END;
' LANGUAGE plpgsql;

DROP TRIGGER alarm_after ON Alarm;

CREATE TRIGGER alarm_after AFTER INSERT OR UPDATE ON Alarm
FOR EACH ROW EXECUTE PROCEDURE PP_DeleteAlarm();

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

On Fri, Oct 08, 2004 at 12:52:52AM -0600, Michael Fuhr wrote:

I should also point out that the trigger function I posted doesn't
deal with concurrency. For example, if an update happens in one
transaction, and an insert happens in another transaction before
the update commits, then you could end up with two records that
should have been deleted but weren't. The function I posted was
merely to show how one might perform the tests you need to make.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Dear Group,
I have a table that stores the description of an
experiment, date and person details who performed the
experiment.
In the last column I want to give the directory where
the files from these experiments are stored.

My questions:

1. Once a user queries this table from a command-line
(using sql commands). How can he get the data to
download?

I want my user to be able to download that data.

Is something possible from
SELECT statements or do I have to do something. Can
any one help me with should I do.

Thank you.

Kumar.




__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend