Team Exasol
Team Exasol

Background

Sometimes during the development of SQL processes, you get to the point where you wonder what kind of SQL type gets generated by some operation or function.

Typically, you can use CREATE VIEW or CREATE TABLE and then DESCRIBE the resulting object to gain that information.

This solution describes a method on how to retrieve type information without the need for a syntactically correct and complete CREATE statement.

Prerequisites

The method described does need a CREATE statement, albeit a fixed one where the created script can be reused.
One of the UDF Languages is required, this example is using the java language.

How to Deploy

Step 1

We will create a SET/EMITS UDF script with variadic input to gather and emit type information:

create or replace java set script
	-- variadic input list. Pass any data....
	ETYPE(...)
emits
	(parameter_number integer, SQL_type varchar(64), java_type varchar(64))
as

	class ETYPE {

		/**
			This run method will ignore any actual data passed.
			All it does is examine the column types and return that information
		*/
		static void run(final ExaMetadata meta, final ExaIterator ctx) throws Exception {
			for( int i=0; i<meta.getInputColumnCount(); i++ ) {
				ctx.emit(
					i,
					meta.getInputColumnSqlType(i),
					meta.getInputColumnType(i).getName()
				);
			}
		}
	}
/

 

Additional Notes for Step 1

The function will take any input and output the corresponding type information, one row per parameter.

 

 

 Please note that

  • as a SET script, it will be called once per grouping, and will also generate output per grouping. Thus, it should best be called without any GROUP BY clause.
  • as a SET script, all input data will be materialized prior to the function call, even though the function itself will never touch the data. Keep that in mind when using the function on top of complex views with lots of data.
  • as UDFs are primarily designed to handle data, the run method will only be called when there is actual data to process. Thus, trying to avoid the above materialization by adding a WHERE FALSE filter will give you no information.

Step 2

Easy check of selected table/view columns

SELECT ETYPE(session_id, sql_text) from exa_user_sessions;
PARAMETER_NUMBER SQL_TYPE JAVA_TYPE
0 DECIMAL(20,0) java.math.BigDecimal
1 VARCHAR(2000000) UTF8 java.lang.String

Step 3

Checking expression results for operations on numeric literals

SELECT ETYPE(100, 1000, 100/1000, 100.001);
PARAMETER_NUMBER SQL_TYPE JAVA_TYPE
0 DECIMAL(3,0) java.lang.Integer
1 DECIMAL(4,0) java.lang.Integer
2 DECIMAL(4,3) java.math.BigDecimal
3 DECIMAL(6,3) java.math.BigDecimal

Additional Notes

TRUNC does not change the data type!

SELECT ETYPE(sysdate, systimestamp, trunc(systimestamp));
PARAMETER_NUMBER SQL_TYPE JAVA_TYPE
0 DATE java.sql.Date
1 TIMESTAMP java.sql.Timestamp
2 TIMESTAMP java.sql.Timestamp