First look at PostgreSQL stored procedures

  • 2020-05-06 11:55:00
  • OfStack

What is a stored procedure, baidu encyclopedia is such a definition: the stored procedure (Stored Procedure) is in a large database system, a set of SQL sets of statements in order to accomplish a specific function, stored in a database called again after the first compilation does not need to compile again, by the user specify the name of the stored procedure and parameters is given (if the stored procedure with parameters) to execute it. A stored procedure is an important object in a database and should be used by any well-designed database application.

The stored procedure syntax structure of Postgresql is as follows :

CREATE OR REPLACE FUNCTION The function name ( parameter 1 . [ The integer int4, The integer array _int4, ... ]) RETURNS Return value type AS $BODY$ DECLARE Variable declarations BEGIN The body of the function END; $BODY$ LANGUAGE ' plpgsql' VOLATILE;

the following example is to invoke a stored procedure to automatically create a corresponding set of tables :

CREATE OR REPLACE FUNCTION create_table_for_client(id int) RETURNS integer AS $BODY$ DECLARE num int4 := 0; sql "varchar"; BEGIN sql := 'create table _' || id || '_company(id int, name text)'; EXECUTE sql; sql := 'create table _' || id || '_employee(id int, name text)';EXECUTE sql; sql := 'create table _' || id || '_sale_bill(id int, name text)';EXECUTE sql; ....... return num; END; $BODY$ LANGUAGE plpgsql VOLATILE

Related articles: