Need help with trigger and Postgres function

Question

I have a lookup table that contains a column of sources (from various hard-coded campaigns captured through the web services API I created) and the corresponding brands that should be associated with them. This means that I can give the brand records where the brand is zero, so that they can be greeted with a specific template using the marketing automation tool.

I, in the end, condemn this API and replace it with where the brand is needed, but at the same time I have to create a temporary solution until I give all my brands 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 the trigger to start the function when inserting a record:

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

But my trigger returns with the error that "ERROR: function public.brand_lookup () does not exist" (but it was created successfully). Besides the fact that my trigger does not see my function, what do I mean? I honestly noob in functions ( as you probably can tell).

Answer 1

It can work as follows:

 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 are too many errors in your example.
You need to start by learning the basics. As always, I offer a very accurate guide.
Start here and here .

Answer 2

Postres allows you to overload functions depending on the input parameters - therefore, when you call public.brand_lookup() without parameters, the function you created - public.brand_lookup(text) - is not found. I think you need to pass the necessary parameter in the function call or delete the parameter in the function definition.