Creating Stored Procedures ​

You can create PL/pgSQL (plpgsql) language stored procedures and execute them in a Yellowbrick database. Yellowbrick stored procedures may perform actions without returning results, such as running DML operations or storing complex SQL calculations. You can also write stored procedures that return either a value or a set of rows. For details about the plpgsql language, see the PostgreSQL 9.5 documentation.

Stored procedures can be invoked at any time via ybsql or another SQL interface, and different values can be passed as parameters on each invocation.

Use the CREATE PROCEDURE command to create stored procedures. If your procedures do not return result sets (using SETOF syntax), execute them with the CALL command. If a procedure returns a set, you must execute it by using a simple SELECT statement that does not contain table references. For example:

select proc_set();

A procedure that returns a single value may be executed with either a CALL statement or a SELECT statement. The CALL statement does not display the returned value.

If a procedure that does not return a value is executed with a SELECT statement, the query returns a NULL value.

Stored procedures may have arguments that use any of the supported Yellowbrick data types. Stored procedure code may contain the following SQL commands:

• SELECT
• CREATE TABLE AS
• INSERT
• UPDATE
• DELETE
• TRUNCATE

Note: Although superusers are allowed to create stored procedures in the sys schema, this practice is not recommended. All users should create stored procedures in public or a user-defined schema.

Parent topic:Database Administration