Creating Trigger function in pgAdmin 4

Question

I'm trying to create a trigger function in the PostgreSQL database with pgAdmin 4. The function should perform pg_notify and return newly inserted data in JSON. But I'm getting the error and can't figure out where is a mistake.

Code:

CREATE FUNCTION ba_weather.weather_notify_func()
  RETURNS trigger
  LANGUAGE 'plpgsql'
  NOT LEAKPROOF
AS $BODY$ CREATE OR REPLACE FUNCTION weather_notify_func()
 RETURNS TRIGGER
 LANGUAGE plpgsql
 AS $$
 BEGIN
    PERFORM pg_notify('weather_insert', row_to_json(NEW));
    RETURN NEW;
 END;
 $$;$BODY$;

ALTER FUNCTION ba_weather.weather_notify_func()
   OWNER TO me;

Error:

enter code here
ERROR: syntax error at or near "CREATE"
LINE 5: AS $BODY$ CREATE OR REPLACE FUNCTION weather_notify_func()
^

The solution is:

CREATE FUNCTION ba_weather.weather_notify_func()
  RETURNS trigger
  LANGUAGE 'plpgsql'
  NOT LEAKPROOF
AS $BODY$ 
 BEGIN
    PERFORM pg_notify('weather_insert', row_to_json(NEW));
    RETURN NEW;
 END;
 $BODY$;

ALTER FUNCTION ba_weather.weather_notify_func()
   OWNER TO me;

user330315 · Accepted Answer · 2020-05-01 21:44:19Z

2

You nested the function definition again into the create function statement:

CREATE FUNCTION ba_weather.weather_notify_func()
  RETURNS trigger
  LANGUAGE plpgsql
  NOT LEAKPROOF
AS $BODY$ 
BEGIN
  PERFORM pg_notify('weather_insert', row_to_json(NEW));
  RETURN NEW;
END;
$BODY$;

ALTER FUNCTION ba_weather.weather_notify_func()
   OWNER TO me;

answered May 1, 2020 at 21:44

user330315

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

Collectives™ on Stack Overflow

Creating Trigger function in pgAdmin 4

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related