Using Wildcards In Your UDx

Vertica supports wildcard * characters in the place of column names in user-defined functions.

You can use wildcards when:

Supported SQL Statements

The following SQL statements can accept wildcards:

Unsupported Configurations

The following situations do not support wildcards:

Examples

These examples show wildcards and user-defined functions in a range of data manipulation operations.

DELETE statements:

=> DELETE FROM tablename WHERE udf(tablename.*) = 5;
			

INSERT statements:

=> INSERT INTO table1 SELECT udf(*) FROM table2;
			

SELECT statements:

=> SELECT udf(*) FROM tablename;
=> SELECT udf(tablename.*) FROM tablename;
=> SELECT udf(f.*) FROM table f;
=> SELECT udf(*) FROM table1,table2;
=> SELECT udf1( udf2(*) ) FROM table1,table2;
=> SELECT udf( db.schema.table.*) FROM tablename;
=> SELECT udf(sub.*) FROM (select col1, col2 FROM table) sub;
=> SELECT x FROM tablename WHERE udf(*) = y;
=> WITH sub as (SELECT * FROM tablename) select x, udf(*) FROM sub;
=> SELECT udf( * using parameters x=1) FROM tablename;
=> SELECT udf(table1.*, table2.col2) FROM table1,table2;

UPDATE statements:

=> UPDATE tablename set col1 = 4 FROM tablename WHERE udf(*) = 3;