How to write "trim" function in PostgreSQL-CodePudding

I have written "trim" function like this

 IF trim(both P_BEN_AADHAR_NUM) IS NULL THEN
        P_SUC_FLAG := 'F';
        P_ERROR    := 'BENREG004'; --'AADHAR Number cannot be Blank';
      END IF;

is this the correct way to write trim function in PostgreSQL.

CodePudding user response：

I think your check is based in the assumption that TRIM() returns NULL when the string only contains white space. That isn't true, it handles NULL the standard way so you can only get NULL if the column itself is NULL:

SELECT TRIM('    '), TRIM(NULL);

btrim	btrim
	null

(Demo)

You need to handle both conditions separately:

IF P_BEN_AADHAR_NUM IS NULL OR TRIM(P_BEN_AADHAR_NUM) = '' THEN

If you really want to make a compact check in a single condiction, you need a function that can handle NULL out of the box (COALESCE() comes to my mind) but it's up to you to evaluate what's more legible:

IF COALESCE(TRIM(P_BEN_AADHAR_NUM), '') = '' THEN

Note: I'm dropping BOTH because it's the default.