Subtransaction error when creating a materialized view with a plpgsql function

user_14572977 0
Viewed 314

I am scratching my head on this since a while. I am trying to create a materialized view and I want the query to be parallel. However when I use the following code :

CREATE MATERIALIZED VIEW xxx AS SELECT foo(myfield) FROM table

I got the following error :

cannot start subtransactions during a parallel operation
CONTEXT:  PL/pgSQL function foo(text) line 1 during statement block entry

foo is a plpgsql function and as such considered as a subtransaction. However you can see there is nothing magic, I use it only to handle exceptions :

CREATE FUNCTION foo(TEXT) RETURNS NUMERIC LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS $$
    BEGIN RETURN CAST(REPLACE($1,',','.') AS NUMERIC);
    EXCEPTION
        WHEN invalid_text_representation THEN RETURN NULL;
    END; $$;

How can I create this view in a parallel way and still handle casting exceptions thanks to this plpgsql function ? Is there an other way to handle exceptions ? (try_catch from SQL server would be the fix here but sadly it's postgres).

Thanks !

postgresql
1 Answers

Check the number for correctness first:

CREATE FUNCTION foo(text) RETURNS numeric
   LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$$SELECT CASE WHEN $1 ~ '^-?[0-9]+(?:,[0-9]+)?$'
            THEN CAST (replace($1,',','.') AS numeric);
            ELSE CAST (NULL AS numeric)
       END$$;
user_64643080
Related