authen.public

Parameters

Name	Type	Mode

Definition

declare 
	image_data bytea;
	content_type text;
	prop_code text;
    s3_file_path text;
	file_ext text;
    error     TEXT;
    v_msgtext TEXT;
    v_state   TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
BEGIN
    /* Encrypt password */
	IF (NEW.PASSWORD is not null) and ((TG_OP = 'INSERT') or (OLD.PASSWORD is distinct from NEW.PASSWORD)) THEN
    	NEW.PASSWORD := crypt(NEW.PASSWORD , gen_salt('bf', 8));  
    END IF;

/* Store user image on S3 bucket, clear image data and keep image url instead */
    IF ((TG_OP = 'INSERT') 
		or (TG_OP = 'UPDATE' and
			 (OLD.image is distinct from NEW.image
			  or OLD.image_url is distinct from NEW.image_url and NEW.image_url is null) -- regenerate image url
			)
		) 
	THEN
		IF (NEW.image is not null) THEN
--			prop_code := (select lower(p.code)
--						  from user_property up inner join property p on p.id = up.prop_id 
--						  where up.user_id = NEW.id
--						  order by p.id limit 1);
			BEGIN                
                image_data := decode(convert_from(NEW.image, 'UTF8'), 'base64');
		        --image_data := decode('data:image/png;base64,'||NEW.image::text, 'base64');
		    EXCEPTION WHEN OTHERS THEN
		        image_data := NEW.image;		
    			GET STACKED DIAGNOSTICS
			        v_state   = RETURNED_SQLSTATE,
			        v_msgtext = MESSAGE_TEXT,
			        v_detail  = PG_EXCEPTION_DETAIL,
			        v_hint    = PG_EXCEPTION_HINT,
			        v_context = PG_EXCEPTION_CONTEXT;
                error := coalesce('message   : '||NULLIF(v_msgtext, '')    ||E'\n\n' , '') ||
                         coalesce('detail    : '||NULLIF(v_detail, '') ||E'\n\n' , '') ||
                         coalesce('hint      : '||NULLIF(v_hint, '')   ||E'\n\n' , '') ||
                         coalesce('context   : '||NULLIF(v_context, '')||E'\n\n', '')||
                         coalesce('sql_state : '||NULLIF(v_state, '')    ||E'\n\n' , '') ||
                         coalesce('image_type: '||NULLIF(NEW.image_type, '')||E'\n\n' , ''); 			    
				insert into sys_log(log_level, log_tag, log_msg)
				values ('E', 'sp_tr_users_init', error);
                perform sp_system_notify('sp_tr_users_init', error);
		    END;

if image_data is not null then
				file_ext := case when NEW.image_type like '%image/png%' then '.png' else '.jpg' end;
				content_type := case when NEW.image_type like '%image/png%' then 'image/png' else 'image/jpeg' end;								
				s3_file_path  := 'users/images/'||md5('user_image_'||NEW.id::text)||file_ext;
                --raise notice '%, %, %: %', prop_code, NEW.id, file_ext, s3_file_path; 
				NEW.image_url := doc.s3_upload_file(s3_file_path, image_data, content_type, is_public => false);

insert into sys_log(log_level, log_tag, log_msg)
                values ('D', 'sp_tr_users_init', 'Upload user image to S3 - '||s3_file_path);
				NEW.image     := null;
            else
                insert into sys_log(log_level, log_tag, log_msg)
                values ('D', 'sp_tr_users_init', 'image_data is null');
			end if;
		ELSE
			NEW.image_url := null;
		END IF;
    END IF;
 
   RETURN NEW;
END;