0

I've created a function to be executed before inserting rows in a table, in order to avoid the error "malformed array literal". It still throws the error and I deduct that the process starts earlier than my trigger. Is there other trigger which I can use to intercept the event earlier?

CREATE TABLE "user" (
permissions text[]
);

CREATE FUNCTION format_array() RETURNS trigger AS $func$
    BEGIN
        NEW.permissions := translate(NEW.permissions::text, '[]', '{}')::text[];
        RETURN NEW;
    END;
$func$ LANGUAGE plpgsql;

CREATE TRIGGER format_array BEFORE INSERT ON "user"
    FOR EACH ROW EXECUTE PROCEDURE format_array();

INSERT INTO "user" (permissions)
    VALUES ('["test", "test2"]');

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=ae1561db1f61f9458fabf02007f388ed

  • 1
    "malformed array literal" is a syntax error that is thrown before your statement even executes. There is no way to "fix" that in a trigger – a_horse_with_no_name Feb 9 at 8:46
  • @a_horse_with_no_name Thanks! Then, is there any way to intercept the SELECT on "users", in order to return an altered "permissions" response to the webservice(from "{test, test2}" to '["test", "test2"]')? – surrender_to_the_Now Feb 9 at 10:22
  • You can create view that returns a JSON value not a native array, e.g. using to_jsonb() dbfiddle.uk/… – a_horse_with_no_name Feb 9 at 10:28
  • I hoped that I can intercept the selection on "permissions" automatically, like using a "trigger", but it looks that I don't have any other other choice than modifing the ORM framework, used in my backend web application. – surrender_to_the_Now Feb 9 at 10:55
  • 1
    Why not simply define the column as jsonb then you don't need to intercept anything. – a_horse_with_no_name Feb 9 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.