CREATE SOURCE
Adds a user-defined load source function. You must have already loaded this library using the CREATE LIBRARY statement. When you call the SQL function, Vertica passes the parameters to the function in the library to process it.
Syntax
CREATE [ OR REPLACE ] SOURCE [schema.]function-name
... AS LANGUAGE 'language' NAME 'factory' LIBRARY library-name
... [ FENCED | NOT FENCED ];
Parameters
OR REPLACE
|
If you do not supply this parameter, the CREATE SOURCE statement fails if an existing function matches the name and parameters of the source function you are trying to define. If you do supply this parameter, the new source function definition overwrites the old. |
schema |
Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example: myschema.thisDbObject |
function‑name
|
The name of the source function to create. If the source function name is schema-qualified (as above), the function is created in the specified schema. This name does not need to match the name of the factory, but it is less confusing if they are the same or similar. |
LANGUAGE 'language'
|
The language used to develop this function, one of the following:
Default value: C++ |
NAME 'factory'
|
The name of the factory class in the shared library that generates the object to handle the source function's processing. This is the same name used by the RegisterFactory class. |
LIBRARY library‑name
|
The name of the shared library that contains the C++ object to perform the processing for this source function. This library must have been previously loaded using the CREATE LIBRARY statement. |
FENCED | NOT FENCED
|
Enables or disables Fenced Mode for this function. Default Value: |
Notes
- The parameters and return value for the source function are automatically determined by the CREATE SOURCE statement, based on data supplied by the factory class.
- You can return a list of all SQL functions and User Defined Functions by querying the system table
V_CATALOG.USER_FUNCTIONS
or executing the vsql meta-command\df
. Users see only the functions on which they haveEXECUTE
privileges.
Privileges
- Only a superuser can create or drop a function that uses a UDx library.
- To use a User Defined Source, the user must have USAGE privileges on the schema and EXECUTE privileges on the defined source function. See GRANT (User Defined Extension) and REVOKE (User Defined Extension).
Important: Installing an untrusted UDL function can compromise the security of the server. UDx's can contain arbitrary code. In particular, UD Source functions can read data from any arbitrary location. It is up to the developer of the function to enforce proper security limitations. Superusers must not grant access to UDx's to untrusted users.
Example
The following example demonstrates loading a library named curllib, then defining a function named curl that is mapped to the CurlSourceFactory factory class in the library:
=> CREATE LIBRARY curllib as '/opt/vertica/sdk/examples/build/cURLLib.so'; CREATE LIBRARY => CREATE SOURCE curl AS LANGUAGE 'C++' NAME 'CurlSourceFactory' LIBRARY curllib; CREATE SOURCE => \x Expanded display is on. => SELECT * FROM user_functions; -[ RECORD 1 ]----------+-------------------- schema_name | public function_name | curl procedure_type | User Defined Source function_return_type | function_argument_type | function_definition | volatility | is_strict | f is_fenced | f comment |