• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4182
  • Last Modified:

Create function in Postgres to update table with a value from another table

I am trying to create a trigger in postgres to update a field of a newly inserted record with a value returned from a query of another table in the same database.  The function below is my attempt of doing this, but it seems to get stuck in a loop whenever i try to run this trigger.  I am also concerned if this will add a lot of overhead to my database.  

I appreciate any help given.
CREATE OR REPLACE FUNCTION billingblock() RETURNS trigger AS '
BEGIN
 UPDATE voipcalls2
    SET billingblock = (select code from routing_table
where dialed_number like routing_table.code || '%' order by routing_table.code desc limit 1); 
    RETURN NEW;
END;
' LANGUAGE 'plpgsql'

Open in new window

0
newvoicetelecom
Asked:
newvoicetelecom
  • 3
  • 2
1 Solution
 
earth man2Commented:
CREATE OR REPLACE FUNCTION billingblock() RETURNS trigger AS $$
BEGIN
 UPDATE voipcalls2
    SET billingblock = (select code from routing_table
where NEW.dialed_number like routing_table.code || '%' order by routing_table.code desc limit 1);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
0
 
newvoicetelecomAuthor Commented:
thank you for your response.  I  have tried entering your code and i get the following error.  It seems to get stuck in a loop.  Also can you explain what the '$$' means? I suppose its a varibale.

>[Error] Script lines: 1-2 --------------------------
 ERROR: stack depth limit exceeded
 Line: 1 

Also i am including the tables and triggers i am using.




CREATE TABLE public.voipcalls2 ( 
    dialed_number	varchar(25) NULL,
    billingblock 	varchar(25) NULL 
    )
GO
CREATE TRIGGER trig_billingblock
	 BEFORE INSERT OR UPDATE ON voipcalls2 FOR EACH ROW
	 EXECUTE PROCEDURE billingblock()
GO
insert into voipcalls2 (dialed_number)
values ('522281231234')
GO
CREATE TABLE public.routing_table ( 
	code    	varchar(150) NULL,
	rate    	varchar(150) NULL,
	date    	varchar(150) NULL,
	provider	varchar(150) NULL,
	city    	varchar(150) NULL 
	)
GO
INSERT INTO public.routing_table(code, rate, date, provider, city)
  VALUES('52228', '0.0137', '8/26/08', 'L1', 'JALAPA')
GO
INSERT INTO public.routing_table(code, rate, date, provider, city)
  VALUES('52229', '0.0135', '8/26/08', 'L1', 'VERACRUZ')

Open in new window

0
 
newvoicetelecomAuthor Commented:
I would like to explain in greater detail what it is that I am trying to accomplish.  I am trying to update the field billingblock in table voipcalls2 whenever a new record is inserted into this same table with the value that is returned by looking up the matching code from the table routing_table.  In the example that I provided the dialed_number of '522281231234' when inserted into the db should trigger the function BILLINGBLOCK() which in turn should match the record in routing_table and update the field billingblock in the table voipcalls2.

I hope this makes it a bit clearer, what it is i am trying to accomplish.

Regards
0
 
earth man2Commented:
$$ acts as the function text delimiter.
as you have a quote in your function around the '%' then that terminates the function string.  $$ gets around "quoting hell".  If you cannot use $$ then you must put '''%''' ( or is it ''%'' ).  (See what I mean).

Also I assumed that you need NEW.dialling_code as you need to distinguish between OLD and NEW for an update trigger.

As it stands you have a recursive trigger as your function updates the the table which triggered the trigger in first place, which triggers the trigger again and again....

Try.

CREATE OR REPLACE FUNCTION billingblock() RETURNS trigger AS $$
DECLARE
  nu_code routing_table.code%TYPE;
BEGIN
    select code into nu_code from routing_table rt
      where NEW.dialed_number like rt.code || '%' order by rt.code desc limit 1;
    NEW.billingblock := nu_code;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
0
 
newvoicetelecomAuthor Commented:
earthman2,  thank you vrey much for your help on this issue.  This fixed the issue completely.  
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now