Created
March 23, 2016 21:47
-
-
Save hahla/26a28621a4acba99ecc2 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- This function replicates the functionality of \d table_name | |
-- It's useful if you work in a sql tool such as intelliJ, where \d does not work. | |
--- usage: select * from check_schema('table_name'); | |
drop function check_schema(text); | |
CREATE FUNCTION check_schema(text) | |
RETURNS TABLE (f1 text, attname name, format_type text, substr text, attnotnull boolean, attnum smallint) AS $$ | |
BEGIN | |
RETURN QUERY | |
SELECT $1 as f1, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), | |
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) | |
FROM pg_catalog.pg_attrdef d | |
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), | |
a.attnotnull, a.attnum | |
FROM pg_catalog.pg_attribute a | |
WHERE a.attrelid = (SELECT c.oid FROM pg_catalog.pg_class c | |
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace | |
WHERE c.relname LIKE $1 AND pg_catalog.pg_table_is_visible(c.oid) | |
) | |
AND a.attnum > 0 AND NOT a.attisdropped | |
ORDER BY a.attnum | |
; | |
END; | |
$$ LANGUAGE PLPGSQL; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment