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.

Postgres

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';

The 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))[1]::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 serial or 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, '')
         ))[1]::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;

Oracle

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.