Thread: How can I tell, in a trigger, if a value in a string is an integer.

I have a trigger function that, for every Update or Insert of
a table inserts a row in a "audit" table that contains some trace
data, the key of the first table and the changed columns (unchanged
cols are NULL) I have this working with a plpgsql function execpt
for one of the trace fields (called the audit_session) which is an
integer. The value of this should be -1 unless a variable is set
for the session using

 set_config('session.sessionid','23',false);

which can be read with

current_setting('session.sessionid')

Which will allow the web server to link a action to a "session"
(data stored in another table but starts from 1 and goes up as
a integer)

I was not able to make the trigger function work in plperl (where
the forcing of the invalid, unset, or unknown to -1 would be easy).

any ideas on the best way to handle this.

--
This email, and any files transmitted with it, is confidential
and intended solely for the use of the individual or entity to
whom they are addressed.  If you have received this email in error,
please advise postmaster@mdtsoft.com <mailto:postmaster@mdtsoft.com>.

New MDT Software Headquarters (As of July 1, 2008):
3480 Preston Ridge Road
Suite 450
Alpharetta, GA 30005


Philip W. Dalrymple III <pwd@mdtsoft.com>
MDT Software - The Change Management Company
+1 678 297 1001
Fax +1 678 297 1003

Philip W. Dalrymple napisal 29.10.2008 15:26:

>  set_config('session.sessionid','23',false);

I validate strings with regexp. This expression returns true:

select '23' ~'^-{0,1}[0-9]+$';

If it's true - you can cast it safely:
select cast('23' as integer)

--
Regards,
Tomasz Myrta