I have a trigger that sets an expires column to
last_access+expiry::interval if expires IS NULL or if the expires value
isn't being set or changed.

IF NEW.expires IS NULL OR NEW.expires = OLD.expires THEN
NEW.expires = NEW.last_access+NEW.expiry:interval;
END IF;

The problem here is OLD doesn't exist on the first INSERT which throws
an error. It seems PL/pgSQL doesn't have C's short-circuit booleans.

a) Is there a way around this?
b) is there a 'right' way to determine if a column is being changed?

Paul (total PL/pgSQL newbie)

--
Paul Makepeace ................................ http://paulm.com/ecademy

"If I had new shoes, then he wouldn't sing Halleighluha."
-- http://paulm.com/toys/surrealism/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

We check the value of TG_OP as in:
IF TG_OP = ''UPDATE'' THEN
Code that address OLD and NEW here;
ELSE
Code that addresses only NEW here.
END IF;

On Thursday 22 January 2004 08:29 am, Paul Makepeace wrote:
--
Quote: 8
"Even now politicians use the phrase 'federal dollars' as a synonym for
'free money.' It's a dangerous tendency, for it leaves the states
accountable to Washington rather than to their own voters. This mix of
state and federal governments is not just economically suspect but
politically corrosive; it undermines the essence of real federalism:
the integrity of both state and federal governments. If we're serious
about states' rights, then we need to get serious about states'
responsibilities. The two go together. As with love and marriage, you
can't have one without the other."

--Paul Greenberg

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry (AT) esc1 (DOT) com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

After looking at this again, I see that my answer doesn't help you. We use the
same trigger code on INSERT OR UPDATE. According to the online docs I have:
OLD
Data type RECORD; variable holding the old database row for UPDATE/DELETE
operations in ROW level triggers.

OLD is only available for UPDATE/DELETE. As for workarounds, I don't know of
any.

On Thursday 22 January 2004 09:26 am, Terry Lee Tucker wrote:
--
Quote: 36
"War is an ugly thing, but not the ugliest of things. The person who
has nothing for which he is willing to fight, nothing which is more
important than his own personal safety, is a miserable creature and
has no chance of being free unless made and kept so by the exertions
of better men than himself."

--John Stuart Mill

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry (AT) esc1 (DOT) com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

I can't quite understand the problem. There are minor differences
between insert triggers and update triggers in postgresql...
AFAIK the values OLD and NEW are related to the data being
inserted/updated/modified right now and can't imagine why someone
would use 'old' in a simple insert statement, but here goes my
humble opinions:

1 - make two triggers. One for insertin and another for updating
2 - short-circuit OR can be "simulated" using if-then-else clauses:
if new.expires is null then
new.expires = //whatever//
else
if new.expires = old.expires then
new.expires = //whatever//
end if;
end if;

If you want to be sure that a column is being modified, write a
update trigger.

Hope this helps a bit.




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)