Dynamic SQL without stored procedures
Sometimes it’s necessary to run dynamic SQL, but it’s not always possible (or sensible) to write a stored procedure just for that.
Postgres and Oracle both support functions that can run queries using dynamic SQL and return the result of those queries as XML.
This can be helpful in various situation, e.g. when it’s needed to iterate over a list of tables, and then retrieve data from those tables. Examples where this could be useful is to count the rows or extract specific values across several tables.
The heart of such a query is a function that takes a SQL query as input and returns the result of that query as a XML value. For Postgres this function is named
query_to_xml(), for Oracle
dbms_xmlgen.getxml(). In both cases it’s necessary to extract the actual data using an XPath expression (or XMLTABLE, depending on the desired output).
Of course you could also use SQL Workbench/J’s WbRowCount command instead of parsing XML.
Retrieving row counts
To run a
SELECT statement on each table in Postgres, something like the following is needed:
select schemaname, tablename, query_to_xml(format('select count(*) as cnt from %I.%I', schemaname, tablename), false, true, '') as xml_count from pg_tables where schemaname = 'public';
format() function is used to properly deal with identifiers that might need quoting. The result of the
query_to_xml() function is the following:
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <cnt>8</cnt> </row>
This can then easily be parsed using the
xpath() function to extract the actual row count, to make the query a bit more readable, I will use a common table expression for the above query, and then use the xpath() function in the final query:
with counts as ( select schemaname, tablename, query_to_xml(format('select count(*) as cnt from %I.%I', schemaname, tablename), false, true, '') as xml_count from pg_tables where schemaname = 'public' ) select schemaname, tablename, (xpath('/row/cnt/text()', xml_count))::text::int as row_count from counts;
Synchronizing sequence values
In Postgres this approach could also be used to synchronize all sequences with the values in the corresponding columns. This might be needed when bulk loading data into tables that use
identity columns. The next value for a sequence can be changed without using dynamic SQL. The
setval() function can be called from any SQL statement.
To do this, we need to first identify all columns that are associated with a sequence. A simple - but not very efficient - way of doing that is to use the function
pg_get_serial_sequence() to test all columns.
with seq_info as ( select table_schema, table_name, column_name, pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name) as col_seq from information_schema.columns where table_schema = 'public' ) select * from seq_info where col_seq is not null;
The result of the above query can then be used to run a dynamic query using
query_to_xml() to get the maximum value for each column. And with the result of that, we can call the
setval() function for every column:
with seq_info as ( select table_schema, table_name, column_name, pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name) as col_seq from information_schema.columns where table_schema = 'public' ), max_values as ( select table_schema, table_name, column_name, col_seq, (xpath( '/row/max_val/text()', query_to_xml( format('select coalesce(max(%I),1) as max_val from %I.%I',column_name, table_schema, table_name), false, true, '') ))::text::int as max_val from seq_info where col_seq is not null ) select table_schema, table_name, column_name, col_seq, setval(col_seq::regclass, max_val) as new_sequence_value from max_values;
For Oracle the basic approach is pretty much the same, only the functions needed are a bit different.
select table_name, dbms_xmlgen.getxml('select count(*) c from '||table_name) as xml_count from user_tables;
The result is:
<?xml version="1.0"?> <ROWSET> <ROW> <C>4</C> </ROW> </ROWSET>
So we need to use
/ROWSET/ROW/C as the xpath expression:
select table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) as count from user_tables;
Oracle offers no way to change the next value of a sequence using plain SQL, and it’s not possible to run DDL, through dbms_xmlgen, so the above approach to adjust sequence values doesn’t work there.