How to get the size of JSONB in bytes?

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.

Responses

Browse pgsql-general by date

  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