PgTidy/testdata/corpus/test_a.pgsql

--select * from dropall('resolvespec_login');
CREATE OR REPLACE FUNCTION resolvespec_login(
   INOUT p_data    jsonb
  ,OUT p_success boolean
  ,OUT p_error   text
)
LANGUAGE plpgsql
VOLATILE
SECURITY DEFINER
AS
$$
DECLARE
  --Error Handling--
  m_funcname text = 'resolvespec_login';
  m_errmsg text;
  m_errcontext text;
  m_errdetail text;
  m_errhint text;
  m_errstate text;
  m_retval integer;
  --Error Handling--
  m_rid_user integer;
  m_rid_hub integer;
  m_pass_hashed citext[];
  m_session jsonb;
  m_allow_hash_auth boolean;
BEGIN
  m_allow_hash_auth = _try_integer( p_data->'claims'->>'rid_user',0) > 0;
  create extension if not exists pgcrypto;

  perform log_event(m_funcname,format('API Login username: %s   hh=%s claims: %s',p_data->>'username',m_allow_hash_auth,p_data->'claims'), bt_enum('eventlog','local notice'));

  select h.rid_hub
  from public.user h
  where h.usercode = p_data ->>'username'
  into m_rid_hub;

  if m_rid_hub is null
      and exists (select 1 from information_schema.tables t where t.table_schema = 'public' and t.table_name = 'users')
  then
    select u.rid_hub, u.rid_user
    from public.users u
    where u.rid_user = _try_integer(p_data - > 'claims' ->>'rid_user', 0) into m_rid_hub,m_rid_user;

  end if;

  if m_rid_hub is null
  then
    raise exception 'Invalid username / password';
  end if;

  m_pass_hashed = array[encode(digest(format('%s:%s',p_data->>'username',p_data->>'password'), 'sha512'), 'hex')
    ,encode(digest(format('%s:%s',p_data->>'username',p_data->>'password'), 'md5'), 'hex')
    ,encode(digest(format('%s',p_data->>'password'), 'md5'), 'hex')
    ]::citext[];

  if m_allow_hash_auth
  then
    m_pass_hashed := m_pass_hashed || array[
      p_data->>'password'
    ]::citext[];
  end if;

  --select $A${"meta": null, "claims": {"rid_user": 30000024}, "password": "c4ca4238a0b923820dcc509a6f75849b", "username": "SUPPORT"}$A$::jsonb->>'password'
  --c4ca4238a0b923820dcc509a6f75849b
  --select * from v_eventlog

  if exists (
    select 1
    from information_schema.tables t
    where t.table_schema = 'public'
      and t.table_name = 'users'
  )
  then
    if not exists (
      select 1
      from public.user h
      left outer join public.users usr on usr.rid_hub = h.rid_hub
      where h.rid_hub = m_rid_hub
      and (
        h.password = any (m_pass_hashed)
        and nv(h.password) <> ''
        or usr.password = any(m_pass_hashed)
        and nv(usr.password) <> ''
      )
    )
    then
      raise exception 'Password incorrect';
    end if;

  elsif not exists (
    select 1
    from public.user h
    where h.rid_hub = m_rid_hub
    and h.password = any(m_pass_hashed)
    and nv(h.password) <> ''
  )
  then
    raise exception 'Password incorrect';
  end if;

  if _try_bool(p_data->'jsonvalue'->>'issecurity',false)
    and not exists (
      select h.rid_hub
      from public.user h
      inner join public.user_all_parents(m_rid_hub) p on p.parent_rid_hub = h.rid_hub
      where h.hubname ilike '%Access Control%'
      )
  then
    raise exception 'Cannot login with security mode. User must be in Access Control group';
  end if;

  with newsession as (
  insert
  into core._loginsession (createtm, modifytm, rid_user, usertable, sessionid, token, useragent, location,
                         ipaddress, expiretm, jsonvalue)
  select now(),
       now(),
       m_rid_hub,
       'hub',
       newid(),
       newid(),
       p_data ->>'user-agent', p_data->>'fromurl'
        , p_data->>'host', (now() + '31 days':: interval), p_data->'jsonvalue'
    returning *
    )
  select to_jsonb(newsession)
  from newsession into m_session;

  if _try_integer(m_session->>'rid_user',0) > 0
  then
    update public.user u
    set jsonvalue = _jsonb_object_cat(u.jsonvalue, jsonb_build_object('lastlogin', to_char(now(), 'YYYY-MM-DD HH24:mi:SS')))
    where u.rid_hub = m_rid_hub;
  end if;

  select jsonb_build_object('token', m_session ->>'token'
           , 'session', m_session ->>'session'
           , 'user', _jsonb_object_cat(jsonb_build_object(
                                               'user_id', h.rid_hub
                                           , 'username', h.usercode
                                           , 'email', null
                                           , 'user_level', 0
                                           , 'roles', jsonb_build_array()
                                           , 'session_id', m_session ->>'sessionid'
                                           , 'token', m_session ->>'token'
                                           , 'session_rid', _try_integer(m_session ->>'id')
                                       ),
                                       (select jsonb_build_object('program_user_table', r.tablename, 'program_user_id',
                                                                  _try_integer(r.key, 0))
                                        from public.user_tableinfo(m_rid_hub) r)
                     )
           , 'expires_in', 86400
       )
  from public.user h
  where h.rid_hub = m_rid_hub into p_data;

  p_success = true;
EXCEPTION
WHEN others THEN
  GET STACKED DIAGNOSTICS
    m_errmsg = MESSAGE_TEXT
   ,m_errcontext = PG_EXCEPTION_CONTEXT
   ,m_errdetail = PG_EXCEPTION_DETAIL
   ,m_errhint = PG_EXCEPTION_HINT
   ,m_errstate = RETURNED_SQLSTATE;

  p_error := get_err_msg(m_funcname, m_errmsg, m_errcontext, m_errdetail, m_errhint, m_errstate);
  p_success := false;
END;
$$;