Re: question on writing a function

On 01/22/2014 08:26 AM, Aaron Burnett wrote:
>
> Greetings all,
>
> I *think* there¹s a way to accomplish what I want, but it is eluding me at
> this time. Any help or pointers will be greatly appreciated.
>
> What I am trying to accomplish is that when a member deactivates their
> account through the UI, my trigger will call a function that deletes or
> archives their data. The issue being that we don¹t want the UI to have to
> wait for this process because of the huge amount of data and tables that
> the queries will have to go through, so we are avoiding doing it
> programatically through the UI and I want it to happen in the background.
>
> The problem is this (and I may just not be thinking clearly): Everything
> is tied back to the member¹s ID which is the constant throughout the
> tables that I will need to manipulate. My trigger will call the (to be
> written) function that will do all the heavy lifting when the
> active_status changes from Œa¹ to Œx¹, but I somehow need to get the ID of
> that member and pass it on to the function so it can then do all that it
> needs to.
>
> For simplicity, the member table is just a few columns (id, active_status,
> name). How would I grab the ID from that table the moment the trigger
> fires to be able to pass it to my function?
>
> I hope that¹s clear.
>
> Thanking you in advance for any help.

Something more concrete assuming using plpgsql Also just a skeleton
function to demonstrate fetching id.

CREATE OR REPLACE FUNCTION status_update()
RETURNS trigger AS
DECLARE
m_id int;
$Body$
BEGIN
IF NEW.active_status = 'f' THEN
m_id := NEW.id;
<more code>
END IF;
RETURN NEW;
END;
$Body$
LANGUAGE plpgsql;
>
> Aaron

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com