Обсуждение: 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