By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36283 in the last week).
CREATE TABLE fortune500(
id serial PRIMARY KEY
, ticker int
, profits_change text
, industry text
);
INSERT INTO fortune500 (ticker, profits_change, industry)
VALUES
(5, 'a' , NULL)
, (6, 'a' , NULL)
, (6, NULL, NULL);
✓
3 rows affected
hidden batch(es)
CREATE OR REPLACE FUNCTION f_count_nulls(_tbl regclass)
RETURNS TABLE (column_name text, missing_values bigint)
LANGUAGE plpgsql STABLE PARALLEL SAFE AS
$func$
BEGIN
RETURN QUERY EXECUTE (
SELECT format(
$$
SELECT x.*
FROM (SELECT count(*) AS ct, %s FROM %s) t
CROSS JOIN LATERAL (VALUES %s) x(col, nulls)
ORDER BY nulls DESC, col DESC
$$, string_agg(format('count(%1$I) AS %1$I', attname), ', ')
, $1
, string_agg(format('(%1$L, ct - %1$I)', attname), ', ')
)
FROM pg_catalog.pg_attribute
WHERE attrelid = $1
AND attnum > 0
AND NOT attisdropped
-- more filters?
);
END
$func$;
