[ic] Interchange and SQL-Ledger / dblink / and psql triggers (LONG)
David Ratte
dratte at recreationalmobility.com
Tue Jan 25 14:07:25 EST 2005
Hello all,
Last week I posed a question regarding getting data into SQL-Ledger from a
foreign application (i.e. interchange) and if anyone had any successes with
this configuration.
The use of dblink and triggers. This was very interesting because it means total
independance of both applications as to keeping the data in sync -- and either
application changing would not effect this stuff from working unless the db
structure changed.
Pardon my length, but some might find what I'm doing useful, and others to
tell me why it won't work!
Now this code here does work, but only if the sql-ledger customer table is in the same
database as interchanges userdb.
I guess the ultimate question is, Can dblink be used inside an plpgsql
procedure, or is this just plain illegal??
If I throw dblink into the mix, everything breaks:
(working query)
select * from dblink_exec('testdb','insert into customer (id,name)values
(DEFAULT,''i24NULL'')'); dblink_exec
-----------------
INSERT 201975 1
(1 row)
this however, breaks down at hostname:
===BAD CODE SNIP===
CREATE OR REPLACE FUNCTION user_update()
RETURNS trigger AS
'BEGIN
select * from dblink('hostaddr=192.168.1.1 dbname=test
user=test','select name from customer') as t1(name varchar) ;
if exists( select 1 from dblink ('hostaddr=192.168.1.1 dbname=test
user=test', 'select * from customer WHERE customer.name =
(new.lname||'', ''||NEW.fname)') ) then
===END BAD CODE===
Here's what I came up with (and it works, but so far without dblink):
===BEGIN CODE update_user.sql===
CREATE OR REPLACE FUNCTION user_update()
RETURNS trigger AS '
BEGIN
--Let's see if the customer already exists in sql-ledger's customer database
--If the customer already exists, just update the existing record.
if exists( select 1 from customer WHERE customer.name = (new.lname||'',
''||NEW.fname) ) then UPDATE customer SET
--sql-ledger has only one name field, interchange has lastname and
firstname -- Ucase(lcase(name)) normalizes 'jOe sMiTh' into 'Smith, Joe' to
prevent dupes in sql-ledger
name = (initcap(NEW.lname) ||'', '' || initcap(NEW.fname)),
address1 = NEW.address1,
address2=NEW.address2,
city=NEW.city,
state=NEW.state,
zipcode=NEW.zip,
email=NEW.email,
phone=NEW.phone_day,
contact=initcap(NEW.fname)||'' ''||initcap(NEW.lname)
WHERE customer.name = (new.lname||'', ''||NEW.fname);
else
--Since we decided the customer did not exist, lets add a new customer
record
INSERT INTO customer ( name, address1, address2, city, state,
zipcode, email, phone, contact ) VALUES (initcap(NEW.lname)||'', ''
||initcap(NEW.fname),
NEW.address1,
NEW.address2,
NEW.city,
NEW.state,
NEW.zip,
NEW.email,
NEW.phone_day,
initcap(NEW.fname)||'' ''||initcap(NEW.lname));
end if;
RETURN NEW;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
--If the trigger already exists, get rid of it so we can re-create it
DROP TRIGGER trans_update on transactions;
CREATE TRIGGER trans_update
BEFORE UPDATE
ON transactions
FOR EACH ROW
====END CODE====
Thank you for your response, and please trim responses accordingly to keep
mail size down.
-Dave Ratte
-------------------------------------------------------
More information about the interchange-users
mailing list