Solved

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

Posted on 2008-10-06
5
3,706 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
  • 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now