2

I would like to store lon, lat as varchar in an PostgreSQL generated column that uses the_geom as data source.

alter table poi add column 
lonlat varchar generated always as (
st_x(the_geom)|| ', '|| st_y(the_geom)
)
stored;

The problem is, that I get the following error:

ERROR: generation expression is not immutable

Is there another way to archieve this?

1

The volatility of the concatenation operator || (as well as the functions CONCAT|CONCAT_WS) is only STABLE, not IMMUTABLE, as it may invoke datatype formatting functions.

And as per the docs

  • The generation expression can only use immutable functions [...]

I can think of two ways:

  • use a composite type function expression as column; instead of the actual column, create a function that (specifically!) accepts the composite type of your relation as parameter, and that does the concatenation; for your convenience, name it after your intended column name:

    CREATE OR REPLACE FUNCTION lonlat(rec poi)
      RETURNS TEXT AS
        $$
          SELECT ST_X($1.the_geom) || ', ' || ST_Y($1.the_geom);
        $$
      LANGUAGE SQL
    ; 
    

    Use it like a relation qualified column:

    SELECT *, poi.lonlat
    FROM   poi
    ;
    

    This is very fast, easy, versatile, and the functional expression is perfectly indexable.

  • create an IMMUTABLE function wrapper using the CONCAT C function:

    CREATE OR REPLACE FUNCTION immutable_concat(VARIADIC TEXT[])
      RETURNS TEXT AS
        'text_concat'
      LANGUAGE INTERNAL
      IMMUTABLE PARALLEL SAFE
    ;
    

    and create your table with it:

    ALTER TABLE poi
      ADD COLUMN lonlat TEXT GENERATED ALWAYS AS ( 
        IMMUTABLE_CONCAT(ST_X(the_geom), ', ', ST_Y(the_geom))
      ) STORED
    ;
    

    Those core functions are STABLE for a reason. For anything but TEXT (or implicitly cast VARCHAR) this wrapper may silently fail, or break things like indexes; I added the explicit type to the parameter to avoid this as much as possible.

    Use only for this specific purpose.


Related:

1

You can only have immutable functions in a generated field. The problem here is not the ST_X or ST_Y function, but the concatenation. For wathever reason, concatenation in postgresql is stable and not immutable. You can see the definition of a function doing this:

select pg_get_functiondef('concat'::regproc)

The simplest way to do what you want should be by creating a concatenation function which is immutable, by doing this (you need top priviledges to do this so you probably need to login as postgres to your db):

CREATE OR REPLACE FUNCTION immutable_concat(VARIADIC "any")
RETURNS text AS 'text_concat'
LANGUAGE internal IMMUTABLE;

Then you should be able to do this (untested):

ALTER TBALE poi ADD COLUMN
lonlat varchar GENERATED ALWAYS AS (
    immutable_concat(ST_X(the_geom), ', ', ST_Y(the_geom))
)
stored;

You should use this immutable version carefully tho, because it can apparently change the output depending of the type of argument in input, especially with date (see for exemple https://stackoverflow.com/questions/54372666/create-an-immutable-clone-of-concat-ws). This function is originally not immutable for a reason. But in your case there shouldn't be any problem.

1
  • 2
    :-D so not only did I write up an answer that looks like a direct copy of those we both linked, no, you also came up with pretty much the same, at the same time. I had to link those answers after I saw that you linked them, and my answer kinda looking exactly like them... – geozelot Sep 2 '20 at 10:52
1

I first tried the function from geozelot. Since the function uses

immutable_concat(VARIADIC TEXT[])

I had just to do an typecast to ::text to make it work:

ALTER TABLE poi
  ADD COLUMN lonlat TEXT GENERATED ALWAYS AS ( 
    IMMUTABLE_CONCAT(ST_X(the_geom)::text, ', ', ST_Y(the_geom)::text)
  ) STORED
;

With the propose from robin loche using:

immutable_concat(VARIADIC "any") 

it works without typecast.

Thx for help.

1
  • Good catch, I missed that, although that was the whole purpose: limit to TEXT input, to limit possible sources of error... – geozelot Sep 4 '20 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.