Thread: check if type is valid pg type

Hi all,

I need to check, whether a type is a valid postgres type or not. My first take was to check in the pg_type table.
However,pg_type does not list types such as integer (but it does list int4) or float (but it does indeed list float8).
Whatis the best way to check whether a type is valid or not? 

Thanks,
Dominik

On Mon, Oct 1, 2012 at 3:47 PM, Dominik Moritz <domoritz@gmail.com> wrote:
> I need to check, whether a type is a valid postgres type or not. My first take was to check in the pg_type table.
However,pg_type does not list types such as integer (but it does list int4) or float (but it does indeed list float8).
Whatis the best way to check whether a type is valid or not? 

You could cast the type name to regtype, e.g.

  SELECT 'integer'::regtype, 'float'::regtype;

Josh

Hi Josh,

That is exactly what I needed. Thank you very much. However, is there a way to do this without getting an exception if
thetype does not exist? 

Cheers,
Dominik

On Oct 10, 2012, at 23:20 , Josh Kupershmidt <schmiddy@gmail.com> wrote:

> On Mon, Oct 1, 2012 at 3:47 PM, Dominik Moritz <domoritz@gmail.com> wrote:
>> I need to check, whether a type is a valid postgres type or not. My first take was to check in the pg_type table.
However,pg_type does not list types such as integer (but it does list int4) or float (but it does indeed list float8).
Whatis the best way to check whether a type is valid or not? 
>
> You could cast the type name to regtype, e.g.
>
>  SELECT 'integer'::regtype, 'float'::regtype;
>
> Josh

On Fri, Oct 12, 2012 at 6:45 AM, Dominik Moritz <domoritz@gmail.com> wrote:
> That is exactly what I needed. Thank you very much. However, is there a way to do this without getting an exception
ifthe type does not exist? 

You could wrap the lookup in a little PL/pgSQL function, something like this:

CREATE OR REPLACE FUNCTION is_valid_type(v_type text)
RETURNS boolean
AS $$
BEGIN
  PERFORM v_type::regtype;
  RETURN true;
EXCEPTION WHEN undefined_object THEN
  RETURN false;
END;
$$ LANGUAGE plpgsql stable;

Josh