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;