Scripting
This section explains the scripting programming that you can use to execute multiple SQL commands sequentially and to handle errors during the executions. You can run control jobs within the database (for example, complex loading processes) and simplify the repetitive jobs by parameterized scripts, such as, creating a user with its password and privileges.
Additionally, you can process result tables of SQL queries, however, a scripting program is a sequential program and only runs on a single cluster node (except the contained SQL statement). Therefore, it is not reasonable to do iterative operations on big data sets. For this purpose, you can use user-defined functions, see UDF Functions for more details.
For control jobs including several SQL commands, you must use Lua scripts. UDFs are not able to run SQL commands natively.
For scripting programs, only the programming language Lua
A script program is created, executed, and dropped by the commands CREATE SCRIPT, EXECUTE SCRIPT, and DROP SCRIPT. The return value of the EXECUTE SCRIPT command is either a number (as rowcount) or a result table.
Example
In the following example a script is created that can copy all tables from one schema into a new schema.
--/
CREATE SCRIPT copy_schema (src_schema, dst_schema) AS
-- define function if anything goes wrong
function cleanup()
query([[DROP SCHEMA ::s CASCADE]], {s=dst_schema})
exit()
end
-- first create new schema
query([[CREATE SCHEMA ::s]], {s=dst_schema})
-- then get all tables of source schema
local success, res = pquery([[SELECT table_name FROM EXA_ALL_TABLES
WHERE table_schema=:s]], {s=src_schema})
if not success then
cleanup()
end
-- now copy all tables of source schema into destination
for i=1, #res do
local table_name = res[i][1]
-- create table identifiers
local dst_table = join(".", quote(dst_schema), quote(table_name))
local src_table = join(".", quote(src_schema), quote(table_name))
-- use protected call of SQL execution including parameters
local success = pquery([[CREATE TABLE ::d AS SELECT * FROM ::s]],
{d=dst_table, s=src_table})
if not success then
cleanup()
end
end
/
EXECUTE SCRIPT copy_schema ('MY_SCHEMA', 'NEW_SCHEMA');