Skip site navigation (1) Skip section navigation (2)

Schema/Trigger help

From: "Cody Konior" <cody(dot)konior(at)reynolds(dot)com(dot)au>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Schema/Trigger help
Date: 2008-05-05 05:44:59
Message-ID: 004e01c8ae73$286d6870$79483950$@konior@reynolds.com.au (view raw or flat)
Thread:
Lists: pgsql-novice
I'm relatively new to postgres and SQL in general and need some assistance.


 

We have a database split into multiple schemas, and a program that runs an
SQL command (abbreviated for brevity) like:

                INSERT INTO xxx.parts_purchasing (DEALER_ID,DATE_CHANGED)
VALUES ('xxx', '28-Apr-2008')

 

Where xxx is one of a number of possible schemas, and parts_purchasing is a
table.  We appear to have a trigger on parts_purchasing like so:

 

CREATE OR REPLACE function fn_update_so_tran() RETURNS TRIGGER AS
$trg_update_so_tran$

DECLARE

BEGIN

   IF (NEW.tran_status = 'U') OR (NEW.tran_status = 'D') THEN

      UPDATE parts_purchasing SET

         qty_received=qty_received + NEW.qty_received,

         qty_invoiced = qty_invoiced + NEW.qty_invoiced,

         amt_invoiced = amt_invoiced + NEW.amt_invoiced,

         amt_received = amt_received + NEW.amt_received

      WHERE 

         dealer_id = NEW.dealer_id AND 

         so_tran_address = NEW.so_tran_address AND 

         this_tran_address = so_tran_address;

   END IF;

   RETURN NULL;

END;

$trg_update_so_tran$ LANGUAGE plpgsql;

ALTER FUNCTION fn_update_so_tran() OWNER TO "AutoDRS";

CREATE TRIGGER trg_update_so_tran AFTER INSERT OR UPDATE on parts_purchasing
FOR EACH ROW EXECUTE PROCEDURE fn_update_so_tran();

 

I've pasted the whole thing because I don't know how much is important.  The
problem is that it seems whenever we do the INSERT, the trigger causes an
error because it says parts_purchasing table doesn't exist.  Naturally... it
does exist!

 

I wonder if triggers aren't schema specific, and so it's getting the schema
wrong?  But if that's the case, I'm not sure how to fix it.  I've tried
changing the first part of it to:

 

      UPDATE dealer_id.parts_purchasing SET

      UPDATE NEW.dealer_id..parts_purchasing SET

      UPDATE OPERATOR(dealer_id.+)parts_purchasing SET

      UPDATE OPERATOR(NEW.dealer_id.+)parts_purchasing SET

    

But none of those seem valid.  

 

I also thought of doing a workaround where set SET SEARCH_PATH = xxx before
each INSERT.  But the problem then is ... there appears to be no way to do
an UNSET SEARCH_PATH, so if a SET SEARCH_PATH failed for some reason then
we'd start clobbering the wrong schema's data.

 

Some advice would be greatly appreciated!

 

Thanks

 

 

Responses

pgsql-novice by date

Next:From: Ken AllenDate: 2008-05-05 12:05:17
Subject: SQl Server Linked Server-Cant write to table with OID
Previous:From: Mag GamDate: 2008-05-05 00:12:31
Subject: Re: Need help with INNER Join

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group