From: | Dmitry Savenko <ds(at)dsavenko(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to get the size of JSONB in bytes? |
Date: | 2015-12-15 13:20:03 |
Message-ID: | 1450185603.3748585.467962769.17EA0B11@webmail.messagingengine.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Petr Korobeinikov | 2015-12-15 14:24:23 | Re: How to get the size of JSONB in bytes? |
Previous Message | Paul | 2015-12-15 13:18:45 | Re: Trigger function, C, lookup of attribute type |