Solved

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

Posted on 2008-10-06
5
3,857 Views
Last Modified: 2012-05-05
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
Comment
Question by:newvoicetelecom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 22660897
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
 

Author Comment

by:newvoicetelecom
ID: 22662652
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
 

Author Comment

by:newvoicetelecom
ID: 22663261
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
 
LVL 22

Accepted Solution

by:
earth man2 earned 500 total points
ID: 22668145
$$ 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
 

Author Closing Comment

by:newvoicetelecom
ID: 31503714
earthman2,  thank you vrey much for your help on this issue.  This fixed the issue completely.  
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question