Re: [GENERAL] unnest-like pg_stats.most_common_values andpg_stats.most_common_freqs

On Fri, Nov 17, 2017 at 09:32:23PM +0100, Luca Ferrari wrote:
> Hi all,
> maybe this is trivial, but I need an hint on a way to see a table form
> of the MCVs and MCFs out of pg_stats with a query. Is it possible to
> get a set of rows each with a most common value on one column and the
> corresponding column on the the other? (assuming I can cast the array
> of MCVs to the right type array)

I think you want something like this ?

postgres=# SELECT schemaname, tablename, attname, unnest(histogram_bounds::text::text[]), histogram_bounds FROM
pg_statsLIMIT 9;pg_catalog | pg_pltemplate | tmplname    | plperl              |
{plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog| pg_pltemplate | tmplname    | plperlu
          | {plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog | pg_pltemplate | tmplname
 | plpgsql             | {plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog |
pg_pltemplate| tmplname    | plpython2u          |
{plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog| pg_pltemplate | tmplname    |
plpython3u         | {plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog | pg_pltemplate |
tmplname   | plpythonu           | {plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog |
pg_pltemplate| tmplname    | pltcl               |
{plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog| pg_pltemplate | tmplname    | pltclu
          | {plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog | pg_pltemplate |
tmplhandler| plperl_call_handler |
{plperl_call_handler,plperlu_call_handler,plpgsql_call_handler,plpython2_call_handler,plpython3_call_handler,plpython_call_handler,pltcl_cal
l_handler,pltclu_call_handler}

Justin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general