Search code examples
postgresqlpgpg-notify

Alias column name in Postgres notify

I am using trigger in Postgres database to call function and send newly inserted row to NodeJs application

CREATE OR REPLACE FUNCTION triggerFunction() RETURNS trigger AS $$ 
DECLARE
BEGIN
PERFORM pg_notify('tableName', row_to_json(NEW)::text );
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This returns the whole row in json format. However I need to change one of the column name while this row is returned.

Unfortunately AS keywork doesnt work in the row to json with NEW.COLUMN_NAME AS NEW_COLUMN. How can we achieve the solution for this?

Solution 
  •  CREATE OR REPLACE FUNCTION triggerFunction() RETURNS trigger AS $$ 
 DECLARE
   ret json;
    BEGIN
      select row_to_json(x) into ret from
        (select NEW.abc as def, NEW.jkl, NEW.col3) x;
      PERFORM pg_notify('tableName', ret::text );
      RETURN NEW;
    END;
 $$ LANGUAGE plpgsql;