Using Wildcards In Your UDx
Vertica supports wildcard * characters in the place of column names in user-defined functions.
You can use wildcards when:
- Your query contains a table in the FROM clause
- You are using a Vertica-supported development language
- Your UDx is running in fenced or unfenced mode
Supported SQL Statements
The following SQL statements can accept wildcards:
- DELETE
- INSERT
- SELECT
- UPDATE
Unsupported Configurations
The following situations do not support wildcards:
- You cannot pass a wildcard in the OVER clause of a query
- You cannot us a wildcard with a DROP statement
- You cannot use wildcards with any other arguments
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;