This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Stored procedure functions

This section contains functions for managing stored procedures.

1: ACTIVE_SCHEDULER_NODE
2: ENABLE_SCHEDULE
3: ENABLE_TRIGGER
4: EXECUTE_TRIGGER

This section contains functions for managing stored procedures.

1 - ACTIVE_SCHEDULER_NODE

Returns the active scheduler node.

Returns the active scheduler node. A schedule must be associated with a trigger to be enabled.

To view existing schedules, see USER_SCHEDULES.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

ACTIVE_SCHEDULER_NODE()

Privileges

Superuser

Examples

To return the active scheduler node:

=> SELECT active_scheduler_node();

 active_scheduler_node
-----------------------
 initiator
(1 row)

2 - ENABLE_SCHEDULE

Enables or disables a schedule.

Enables or disables a schedule. A schedule can only be enabled if a trigger is attached to it.

To view existing schedules, see USER_SCHEDULES.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

ENABLE_SCHEDULE ( '[[database.]schema.]schedule', enabled )

Arguments

[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
schedule: The schedule to enable or disable.
enabled: Boolean, whether to enable the trigger.

Privileges

Superuser

Examples

To enable a schedule:

=> SELECT enable_schedule('vmart.management.daily_1am', true);

To disable a schedule:

=> SELECT enable_schedule('vmart.management.daily_1am', false);

If you leave the database and schema empty, the default is current_database.public:

=> SELECT enable_schedule('biannual_22_noon_gmt', true);

3 - ENABLE_TRIGGER

Enables or disables a trigger.

To view existing triggers, see STORED_PROC_TRIGGERS.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

ENABLE_TRIGGER ( '[[database.]schema.]trigger', enabled )

Arguments

[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
trigger: The trigger to enable or disable.
enabled: Boolean, whether to enable the trigger.

Privileges

Superuser

Examples

To enable a trigger:

=> SELECT enable_trigger('vmart.management.log_user_actions', true);

To disable a trigger:

=> SELECT enable_trigger('vmart.management.log_user_actions', false);

If you leave the database and schema empty, the default is current_database.public:

=> SELECT enable_trigger('revoke_log_privileges', true);

4 - EXECUTE_TRIGGER

Manually executes the stored procedure attached to a trigger.

Manually executes the stored procedure attached to a trigger. This is generally used for testing the trigger.

To view existing triggers, see STORED_PROC_TRIGGERS.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

EXECUTE_TRIGGER ( '[[database.]schema.]trigger' )

Arguments

[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
trigger: The trigger to execute.

Privileges

Superuser

Examples

To execute a trigger:

=> SELECT execute_trigger('vmart.management.log_user_actions');

If you leave the database and schema empty, the default is current_database.public:

=> SELECT execute_trigger('revoke_log_privileges');