type geography does not exist -- suddenly

Hi All,
I don't know what happened recently but in a Function, called via a trigger, I suddenly receive an error "type "geography" does not exist", while it worked a couple of days ago.

Just in case... here is the code extract.

CREATE TRIGGER tr_t_user_inserted
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE PROCEDURE fn_t_create_user_after_registration();

CREATE OR REPLACE FUNCTION fn_t_create_user_after_registration() RETURNS trigger AS $fn_t_create_user_after_registration$
  DECLARE
    uid bigint;
  BEGIN  
    -- create new record
    INSERT INTO public.t_user (user_id, email)
    VALUES (NEW.id, NEW.email) RETURNING id INTO uid;

    -- create a fake address
    INSERT INTO public.t_address (object_id, object_type, latitude, longitude)
    VALUES (uid, 'user', 0, 0);

    RETURN NEW;
  END;
$fn_t_create_user_after_registration$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION fn_t_address_geo_update_event() RETURNS trigger AS $fn_t_address_geo_update_event$
  BEGIN  
    UPDATE public.t_address SET 
      geog = ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 4326)::geography,      ----- WHERE THE PROBLEM IS
      updated_at = date_part('epoch'::text, now()) WHERE address_id=NEW.address_id;

        
    RETURN NULL;
  END;
$fn_t_address_geo_update_event$ LANGUAGE plpgsql;

CREATE TRIGGER tr_t_address_inserted
  AFTER INSERT ON t_address
  FOR EACH ROW
  EXECUTE PROCEDURE fn_t_address_geo_update_event();

CREATE TRIGGER tr_t_user_inserted
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE PROCEDURE fn_t_create_user_after_registration();

CREATE OR REPLACE FUNCTION fn_t_create_user_after_registration() RETURNS trigger AS $fn_t_create_user_after_registration$
  DECLARE
    uid bigint;
  BEGIN  
    -- create new record
    INSERT INTO public.t_user (user_id, email)
    VALUES (NEW.id, NEW.email) RETURNING id INTO uid;

    -- create a fake address
    INSERT INTO public.t_address (object_id, object_type, latitude, longitude)
    VALUES (uid, 'user', 0, 0);

    RETURN NEW;
  END;
$fn_t_create_user_after_registration$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION fn_t_address_geo_update_event() RETURNS trigger AS $fn_t_address_geo_update_event$
  BEGIN  
    UPDATE public.t_address SET 
      geog = ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 4326)::geography,      ----- WHERE THE PROBLEM IS
      updated_at = date_part('epoch'::text, now()) WHERE address_id=NEW.address_id;

        
    RETURN NULL;
  END;
$fn_t_address_geo_update_event$ LANGUAGE plpgsql;

CREATE TRIGGER tr_t_address_inserted
  AFTER INSERT ON t_address
  FOR EACH ROW
  EXECUTE PROCEDURE fn_t_address_geo_update_event();

CREATE TRIGGER tr_t_user_inserted
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE PROCEDURE fn_t_create_user_after_registration();

CREATE OR REPLACE FUNCTION fn_t_create_user_after_registration() RETURNS trigger AS $fn_t_create_user_after_registration$
  DECLARE
    uid bigint;
  BEGIN  
    -- create new record
    INSERT INTO public.t_user (user_id, email)
    VALUES (NEW.id, NEW.email) RETURNING id INTO uid;

    -- create a fake address
    INSERT INTO public.t_address (object_id, object_type, latitude, longitude)
    VALUES (uid, 'user', 0, 0);

    RETURN NEW;
  END;
$fn_t_create_user_after_registration$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION fn_t_address_geo_update_event() RETURNS trigger AS $fn_t_address_geo_update_event$
  BEGIN  
    UPDATE public.t_address SET 
      geog = ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 4326)::geography,      ----- WHERE THE PROBLEM IS
      updated_at = date_part('epoch'::text, now()) WHERE address_id=NEW.address_id;

        
    RETURN NULL;
  END;
$fn_t_address_geo_update_event$ LANGUAGE plpgsql;

CREATE TRIGGER tr_t_address_inserted
  AFTER INSERT ON t_address
  FOR EACH ROW
  EXECUTE PROCEDURE fn_t_address_geo_update_event();

CREATE TRIGGER tr_t_user_inserted
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE PROCEDURE fn_t_create_user_after_registration();

CREATE OR REPLACE FUNCTION fn_t_create_user_after_registration() RETURNS trigger AS $fn_t_create_user_after_registration$
  DECLARE
    uid bigint;
  BEGIN  
    -- create new record
    INSERT INTO public.t_user (user_id, email)
    VALUES (NEW.id, NEW.email) RETURNING id INTO uid;

    -- create a fake address
    INSERT INTO public.t_address (object_id, object_type, latitude, longitude)
    VALUES (uid, 'user', 0, 0);

    RETURN NEW;
  END;
$fn_t_create_user_after_registration$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION fn_t_address_geo_update_event() RETURNS trigger AS $fn_t_address_geo_update_event$
  BEGIN  
    UPDATE public.t_address SET 
      geog = ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 4326)::geography,      ----- WHERE THE PROBLEM IS
      updated_at = date_part('epoch'::text, now()) WHERE address_id=NEW.address_id;

        
    RETURN NULL;
  END;
$fn_t_address_geo_update_event$ LANGUAGE plpgsql;

CREATE TRIGGER tr_t_address_inserted
  AFTER INSERT ON t_address
  FOR EACH ROW
  EXECUTE PROCEDURE fn_t_address_geo_update_event();

Table t_address works perfectly in "regular" query and FUNCTION 'fn_t_create_user_after_registration' has been created with "SECURITY DEFINER" as type of security.

Could anyone please help me?

Many thanks in advance,

type geography does not exist -- suddenly

Similar Threads

Similar Threads

Similar Threads