Hi,
I want to impose size restrictions on JSONB documents stored in my table. Say, no document over 10Kb must be inserted in the table. For that, I try to write a trigger like this (jdoc here is of JSONB type):
create function check_document() returns trigger as $$
begin
if 10 * 1024 < octet_length(new.jdoc::bytea) then
raise exception 'Document is too big';
end if;
return new;
end
$$ language plpgsql;
create trigger check_document_trg
before insert or update
on documents
for each row
execute procedure check_document();
This doesn't work because it can't cast JSONB to 'bytea'. I tried casting to 'text', still no luck. Could anyone please help me?
Also, is there a better approach to impose a size limit, then writing triggers? I need it to be fairly flexible, e.g. 10Kb is not a constant, it may even be different for different documents (rows) in the same table.
Best regards,
Dmitry.