0

I'm trying to create the following table in PostgreSQL 13:

CREATE TABLE cached (
  text VARCHAR NOT NULL,
  text_hash BYTEA GENERATED ALWAYS AS (sha256(convert_to(text, 'LATIN1'))) STORED PRIMARY KEY
);

However, I'm getting the following error:

generation expression is not immutable

I'm guessing that this is because convert_to is not immutable. How do I work around this? Is there a way of converting the text column to bytea in an immutable way?

Things I'd rather avoid:

  1. Casting to text::bytea. It won't work correctly as explained here.
  2. Using triggers.
Attila Kun
  • 2,215
  • 2
  • 23
  • 33
  • `convert_to(text, 'LATIN1')` is as problematic as `::bytea`. What if `text` uses a different collation? What if it's UTF8? That cast will mangle the text *unless* it's LATIN1 or all characters are in the US-ASCII range. Even English names can't be represented in the US-ASCII range, eg `Charlotte Brontë` – Panagiotis Kanavos Feb 10 '23 at 13:58
  • You could lie. Create a function(say conv_txt()) that does `convert_to(text, 'LATIN1')` and mark it `IMMUTABLE` and then do `(sha256(conv_txt(text)))`. You will have to accept the consequences if the `text` strays from the expected. – Adrian Klaver Feb 10 '23 at 16:25
  • Or not lie. Use `digest` from [pgcrypto](https://www.postgresql.org/docs/current/pgcrypto.html) extension. As in `text_hash BYTEA GENERATED ALWAYS AS (digest(text, 'sha256')) STORED PRIMARY KEY` – Adrian Klaver Feb 10 '23 at 17:26
  • As an aside, naming a column/table with a reserved word is a very poor decision. – Bohemian Feb 12 '23 at 00:33

1 Answers1

1
CREATE OR REPLACE FUNCTION cripto(TEXT )
RETURNS TEXT AS 
$function$
DECLARE tText TEXT ; 

BEGIN
SELECT sha256(convert_to($1, 'LATIN1')) INTO tText;
RETURN tText ;

END ;
$function$ 
IMMUTABLE
LANGUAGE plpgsql ; 


CREATE TABLE cripto(
text TEXT , 
text_hash TEXT GENERATED ALWAYS AS ( cripto(text) ) STORED PRIMARY KEY 
);
mwalter
  • 102
  • 5