postgresql / plpgsql - function with a loop and multiple actions

Question

I'm fairly new to Postgres but I've created a function via pgAdmin 4.15, Postgres is at version 9.6.
Problem is I can't make it work, I think there is something wrong in the syntax?
INSERT and UPDATE statements are working (outside the function), so it must be the function syntax...

The intention is to call this function via a jdbc plugin (Logstash) and update some tables using the given "input" string as key.
The "key" is used to SELECT the alarm IDs to operate on.
So, for every id I get, I need to perform some UPDATE/INSERT on those tables.

# \sf close_event
CREATE OR REPLACE FUNCTION public.close(input text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
  _event_idnumber INTEGER;
BEGIN
  FOR _event_idnumber IN
    SELECT id FROM public.alerts WHERE data ->> 'field_name' = input
  LOOP
    INSERT INTO public.comments ( created_date, content, alert_id, user_id) VALUES (current_timestamp, 'text1 text', _event_idnumber, 1);
    INSERT INTO public.comments ( created_date, content, alert_id, user_id) VALUES (current_timestamp, 'text2 text.', _event_idnumber, 1);
    INSERT INTO public.analysis ( alert_id, created_date, last_updated, notes) VALUES (_event_idnumber, current_timestamp, current_timestamp, 'text3 text');
    UPDATE public.alerts SET status='DONE', outcome='completed', last_updated=current_timestamp, assigned_user_id=1 WHERE id = _event_idnumber;
    UPDATE public.alerts SET data = jsonb_set(data, '{other_field}'::text[], input::jsonb) WHERE id = _event_idnumber;
  END LOOP;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
END;
$function$;

I've read that with some versions of postgres I must use a DO statement, but can't figure out how.
I'm following instructions at this page: https://www.postgresql.org/docs/9.6/plpgsql-control-structures.html

You'll also need to be more specific about what you're trying to accomplish. The code will only tell us what you've implemented, not what you intended. — Miles Elam
– Miles Elam, Commented Dec 18, 2019 at 17:31

Parfait · Accepted Answer · 2019-12-18 19:17:42Z

1

Because SQL is a set-based language for straightforward, logical actions you should handle your append and updates in sets and not iterative loop calls as commented. Therefore, convert the procedural language, plpgsql to pure sql. Specifically, consider select inserts with UNION and combining the update statements.

CREATE OR REPLACE FUNCTION public.close(input text)
 RETURNS void
 LANGUAGE sql
AS $function$

   -- COMMENTS INSERT
   INSERT INTO public.comments (created_date, content, alert_id, user_id) 
   SELECT current_timestamp, 'text1 text', a.id, 1
   FROM public.alerts a
   WHERE a.data ->> 'field_name' = input

   UNION ALL

   SELECT current_timestamp, 'text2 text.', a.id, 1
   FROM public.alerts a
   WHERE a.data ->> 'field_name' = input;

   -- ANALYSIS INSERT
   INSERT INTO public.analysis (alert_id, created_date, last_updated, notes) 
   SELECT a.id, current_timestamp, current_timestamp, 'text3 text'
   FROM public.alerts a
   WHERE a.data ->> 'field_name' = input;

   -- ALERTS UPDATE
   UPDATE public.alerts a
   SET status='DONE', 
       outcome='completed', 
       last_updated=current_timestamp, 
       assigned_user_id=1,
       data = jsonb_set(data, '{other_field}'::text[], input::jsonb) 
   WHERE a.data ->> 'field_name' = input;

$function$;

Online demo (using the 40 'postgres' rows in random mydata table)

answered Dec 18, 2019 at 19:17

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

2 Comments

eml Over a year ago

Yes! Thanks! Your solution is perfect, I've changed only the last update for data column in data = jsonb_set(data, '{other_field}'::text[], to_json(input::text)::jsonb) because it needs a json string.

Parfait Over a year ago

Excellent to hear and glad to help! Happy coding!

Collectives™ on Stack Overflow

postgresql / plpgsql - function with a loop and multiple actions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related