Need Assistance with a Postgres Trigger and Function

Question

I have a lookup table that holds a column of sources (from various hardcoded campaigns captured through a webservice API I created) and the respective brands that should be associated with them. This is so I can give a brand to records where brand is null - so that they can be welcomed with a certain template through a marketing automation tool.

I'm eventually deprecating this API and replacing it with one where brand is required, but in the meantime I have to craft a temporary solution until I give all my brand teams time to change their API calls.

I wrote this function:

CREATE OR REPLACE FUNCTION public.brand_lookup(IN i_brand TEXT )
  RETURNS SETOF RECORD VOLATILE AS
$$
BEGIN
   RETURN QUERY
     UPDATE subscriber
     SET brand = (SELECT brand FROM brand_translation
                  WHERE source = subscriber.source);
END;
$$
LANGUAGE plpgsql;

And a trigger to fire the function when a record is inserted:

CREATE TRIGGER brand_translation 
  AFTER INSERT ON subscriber
  FOR EACH ROW EXECUTE PROCEDURE public.brand_lookup();

But my trigger comes back with an error that "ERROR: function public.brand_lookup() does not exist" (but it created successfully)". Besides the fact that my trigger doesn't see my function, will that function do what I'm intending? I'm fairly noob at functions (as you can probably tell).

Erwin Brandstetter · Accepted Answer · 2013-03-25 23:38:53Z

2

It might work like this:

CREATE OR REPLACE FUNCTION public.f_brand_lookup()
   RETURNS trigger AS
$func$
BEGIN
   SELECT INTO NEW.brand
          bt.brand
   FROM   brand_translation bt
   WHERE  bt.source = NEW.source;

   RETURN NEW;
END
$func$
LANGUAGE plpgsql;

CREATE TRIGGER brand_insert_before_lookup
BEFORE INSERT ON subscriber
FOR EACH ROW EXECUTE PROCEDURE public.f_brand_lookup();

There is just too much completely wrong with your example.
You need to start by studying the basics. As always, I suggest the very fine manual.
Start here and here.

answered Mar 25, 2013 at 23:38

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Karmel · Accepted Answer · 2013-03-25 21:56:26Z

0

Postres allows for overloading of functions depending on the input parameters-- so when you call public.brand_lookup() with no parameters, the function you created-- public.brand_lookup(text)-- is not found. I think you need to pass the necessary parameter in the call to the function, or remove the parameter in the function definition.

answered Mar 25, 2013 at 21:56

Karmel

3,4822 gold badges20 silver badges11 bronze badges

Collectives™ on Stack Overflow

Need Assistance with a Postgres Trigger and Function

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related