I have a postgresql query that takes data that is in an indexed format and converts it to regular text. For example:

column abstract_inverted_index -> {"IndexLength":97,"InvertedIndex":{"Environmental":[0],"exposure":[1],"is":[2]}}

column abstract -> Environmental exposure is

The query looks like this:

update my_table
set abstract=subquery.abstract
FROM (select id, string_agg(key, ' ' order by ord::int) as abstract
      from my_table
               cross join jsonb_each(abstract_index::jsonb -> 'InvertedIndex')
               cross join jsonb_array_elements(value) as e(ord)
      group by id) AS subquery
where my_table.id = subquery.id and abstract_index is not null;

What I want to do is turn this code into a trigger function that automatically happens when data is inserted or updated.

What I have so far is:

CREATE OR REPLACE FUNCTION fn_convert_abstract_index()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    IF NEW.abstract_inverted_index IS NOT NULL:
         SET NEW.abstract = string_agg(key, ' ' order by ord::int) as abstract
         cross join jsonb_each(abstract_inverted_index::jsonb -> 'InvertedIndex')
               cross join jsonb_array_elements(value) as e(ord)
    END IF;

    RETURN NEW;
END;
$$

CREATE TRIGGER tr_convert_abstract_index

  AFTER INSERT OR UPDATE

  ON "my_table"

  FOR EACH ROW

  EXECUTE PROCEDURE fn_convert_abstract_index();

But this is not working properly due to syntax errors. Any advice on how to format this properly?

CodePudding user response：

simplified query:

WITH cte AS (
    SELECT
        ((js::jsonb) -> 'InvertedIndex') AS unnested_js
    FROM (
        VALUES ('{"IndexLength":97,"InvertedIndex":{"Environmental":[0],"exposure":[1],"is":[2]}}')) ss (js)
),
cte1 AS (
    SELECT
        (jsonb_each(unnested_js)).*
    FROM
        cte
    ORDER BY
        2
)
SELECT
    string_agg(key, ' ' ORDER BY value)
FROM
    cte1;

demo

• You need before insert/update trigger, so that you can modified the new record and return new record.
• If key 'InvertedIndex' not exists then make code run to another case condition branch immediately.
• raise notice is for debug purpose.
• I did not consider the case that new.abstract_inverted_index cannot cast to jsonb.

CREATE OR REPLACE FUNCTION fn_convert_abstract_index ()
    RETURNS TRIGGER
    AS $$
DECLARE
    _sql text;
BEGIN
    RAISE NOTICE 'new abstract_inverted_index is %', NEW.abstract_inverted_index;
    RAISE NOTICE 'key exists: %', ((NEW.abstract_inverted_index)::jsonb)['InvertedIndex'] IS NOT NULL;
    IF NEW.abstract_inverted_index IS NOT NULL AND ((NEW.abstract_inverted_index)::jsonb)['InvertedIndex'] IS NOT NULL THEN
        _sql := ( WITH cte AS (
                SELECT
                    ((js::jsonb) -> 'InvertedIndex') AS unnested_js
                FROM (
                    VALUES (NEW.abstract_inverted_index)) ss (js)),
                cte1 AS (
                    SELECT
                        (jsonb_each(unnested_js)).*
                    FROM
                        cte
                    ORDER BY
                        2
)
                    SELECT
                        string_agg(key, ' ' ORDER BY value)
                    FROM
                        cte1);
        NEW.abstract := _sql;
        RAISE NOTICE 'new.abstract: %', NEW.abstract;
        RAISE NOTICE 'new: %', ROW (NEW.*);
        RETURN new;
    ELSE
        NEW.abstract := 'test';
        RETURN new;
    END IF;
END;
$$
LANGUAGE PLPGSQL;

CodePudding user response：

You need a BEFORE INSERT OR UPDATE trigger to be able to modify the new record. Also use a much simpler subquery for creating the value:

CREATE OR REPLACE FUNCTION fn_convert_abstract_index()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    IF NEW.abstract_inverted_index IS NOT NULL:
         NEW.abstract = (
             SELECT string_agg(key, ' ' ORDER BY (value->>0)::int)
             FROM jsonb_each(abstract_inverted_index::jsonb -> 'InvertedIndex')
         );
    END IF;

    RETURN NEW;
END;
$$