Title:
Tag1 for Path:	a single word in lower characters
Tag2 for Path:
List of alternative single tags:	one or many words in lower characters, separated by comma or space
Text:	<p>We wish to count all rows in all tables of a schema in PostgreSQL 9.3.</p> <p>For this tasks we need to use a xml function such as query_to_xml.</p> <pre> select tablename as sttable, xpath('/table/row/count/text()', x) as stcount from (select tablename,query_to_xml('select count(*) from '||tablename,false,false,'') as x from pg_tables where schemaname = 'public' order by 1) as z </pre> <p>Here is the result:</p> <pre> sttable | stcount -------------+--------- sh_category | {4} sh_image | {5} sh_item | {5} sh_user | {4} (4 rows) </pre> <p>Here's an extented version showing the result without curly brackets:</p> <pre> select tablename as sttable, btrim(xpath('/table/row/count/text()', x)::text,'{}')::integer as stcount from ( select tablename,query_to_xml('select count(*) from '||tablename,false,false,'') as x from pg_tables where schemaname = 'public' order by 1 ) as z </pre> <samp> sttable | stcount ----------------+--------- cdr | 65 clubtyp | 107 control | 0 country | 242 </samp> <p>Return all Tables and row counts as json:</p> <pre> select array_to_json(array_agg(t)) from ( select tablename as table, btrim(xpath('/table/row/count/text()', x)::text,'{}')::integer as rowcnt from ( select tablename,query_to_xml('select count(*) from '||tablename,false,false,'') as x from pg_tables where schemaname = 'public' order by 1 ) as z ) as t; </pre> <samp> array_to_json ---------------------------------------------------------------- [{"table":"customer","rowcnt":265},{"table":"user","rowcnt":12}] </samp> (1 row) </pre>
URL:
Operation:	Delete Update Insert
Template Prompt