[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