CREATE OR REPLACE FUNCTION table_has_column(tablename TEXT, OUT res boolean) RETURNS boolean AS $func$ -- DECLARE res boolean DEFAULT FALSE; BEGIN SELECT (count(constraint_name)>0)::boolean AS res INTO res FROM information_schema.table_constraints WHERE table_name = tablename AND constraint_type = 'PRIMARY KEY'; END; $func$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION table_has_pk(tablename TEXT, OUT res boolean) RETURNS boolean AS $func$ res boolean DEFAULT FALSE; BEGIN SELECT (count(constraint_name)>0)::boolean AS res INTO res FROM information_schema.table_constraints WHERE table_name = tablename AND constraint_type = 'PRIMARY KEY'; END; $func$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION table_has_column(tablename TEXT, columnname TEXT, OUT res boolean) RETURNS boolean AS $func$ BEGIN SELECT (count(column_name) > 0)::boolean AS res INTO res FROM information_schema.columns WHERE table_name = tablename AND column_name = columnname; END; $func$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION table_has_columns(tablename TEXT, VARIADIC columnname TEXT) RETURNS boolean AS $func$ DECLARE res boolean DEFAULT FALSE; DECLARE x TEXT; res boolean DEFAULT TRUE; BEGIN FOREACH x IN ARRAY columnname LOOP EXIT WHEN res = FALSE; SELECT table_has_column(tablename, x) AS res INTO res; END; END; $func$ LANGUAGE plpgsql;
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter