Postgres if statement inside function inserting values

Question

Is it possible to make this function working? I have If statement inside Insert Into query in Postgres function.

What methods do you have?

    CREATE FUNCTION proc_api_consumer_audit_insert()
    RETURNS TRIGGER AS $api_consumer$
    BEGIN
        INSERT INTO api_consumer_audit(api_consumer_id, change_type, changed_by, changed_date, business_id_old, business_id_new, name_old, name_new, api_callback_url_old, api_callback_url_new, application_base_url_old, application_base_url_new, authorization_callback_url_old, authorization_callback_url_new, status_old, status_new) 
        VALUES(NEW.id, 'INSERT', (IF NEW.created_by=null THEN CURRENT_USER ELSE NEW.created_by END IF;), CURRENT_TIMESTAMP, null, NEW.business_id, null, NEW.name, null, NEW.api_callback_url, null, NEW.application_base_url, null, NEW.authorization_callback_url, null, NEW.status);
        RETURN NEW;
    END;
    $api_consumer$ LANGUAGE plpgsql;

user330315 · Accepted Answer · 2017-10-16 14:27:00Z

3

There is no IF in SQL. In general one would use a CASE expression but in your case coalesce() is much shorter:

INSERT INTO api_consumer_audit(...) 
VALUES (NEW.id, 
        'INSERT', 
        coalesce(new.created_by, current_user),
        ...);

The same with a CASE expression would look like this:

INSERT INTO api_consumer_audit(...) 
VALUES (NEW.id, 
        'INSERT', 
        case 
           when new.created_by IS NOT NULL then new.created_by
           else current_user
        end,
        ...);

answered Oct 16, 2017 at 14:27

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

xross Over a year ago

When I am invoking sql insert query, coalesce doesnt work:

INSERT INTO api_consumer (business_id, type, status, description, created_by, created_date, modified_by, modified_date, name, origin_country, license_authority_name, registration_date, api_callback_url, application_base_url, authorization_callback_url, address, local_address, license) VALUES ('101', 'PSD2', 'NEW', 'TEST OF TRIGGER ON INSERT', 'kk', CURRENT_TIMESTAMP, 'gg', null, 'SPRAWDZAM_COS', 'PL', null, null, null, null, null, 1, 1, 1);

- it is still showing mi incorrect user

user330315 Over a year ago

@xross There is no coalesce() in the code you added in the first comment

xross Over a year ago

OK, anyway it worked. I wrote that insert in the first comment exaclty to be done in DB - this was not a query in a function

Collectives™ on Stack Overflow

Postgres if statement inside function inserting values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related