CODEWITHSUNDEEP
postgresqlsha256byteagenerated-columns
Attila Kun
Attila Kun

Reputation: 2415

Generated sha256 column in Postgres from varchar/text column?

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.

Upvotes: 2

Views: 377

Answers (1)

mwalter
mwalter

Reputation: 132

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 
);

Upvotes: 3

Related Questions