Greenplum PL/pgSQL Procedural Language
A newer version of this documentation is available. Click here to view the most up-to-date release of the Greenplum 4.x documentation.
Greenplum PL/pgSQL Procedural Language
This section contains an overview of the Greenplum Database PL/pgSQL language.
About Greenplum Database PL/pgSQL
Greenplum Database PL/pgSQL is a loadable procedural language that is installed and registered by default with Greenplum Database. You can create user-defined functions using SQL statements, functions, and operators.
With PL/pgSQL you can group a block of computation and a series of SQL queries inside the database server, thus having the power of a procedural language and the ease of use of SQL. Also, with PL/pgSQL you can use all the data types, operators and functions of Greenplum Database SQL.
The PL/pgSQL language is a subset of Oracle PL/SQL. Greenplum Database PL/pgSQL is based on Postgres PL/pgSQL. The Postgres PL/pgSQL documentation is at https://www.postgresql.org/docs/8.2/static/plpgsql.html
When using PL/pgSQL functions, function attributes affect how Greenplum Database creates query plans. You can specify the attribute IMMUTABLE, STABLE, or VOLATILE as part of the LANGUAGE clause to classify the type of function, For information about the creating functions and function attributes, see the CREATE FUNCTION command in the Greenplum Database Reference Guide.
Greenplum Database SQL Limitations
When using Greenplum Database PL/pgSQL, limitations include
- Triggers are not supported
- Cursors are forward moving only (not scrollable)
For information about Greenplum Database SQL conformance, see Summary of Greenplum Features in the Greenplum Database Reference Guide.
The PL/pgSQL Language
PL/pgSQL is a block-structured language. The complete text of a function definition must be a block. A block is defined as:
[ label ] [ DECLARE declarations ] BEGIN statements END [ label ];
Each declaration and each statement within a block is terminated by a semicolon (;). A block that appears within another block must have a semicolon after END, as shown in the previous block. The END that concludes a function body does not require a semicolon.
All key words and identifiers can be written in mixed upper and lower case. Identifiers are implicitly converted to lowercase unless enclosed in double-quotes ( " ).
You can add comments in PL/pgSQL in the following ways:
- A double dash (--) starts a comment that extends to the end of the line.
- A /* starts a block comment that extends to the next occurrence of */.
Block comments cannot be nested, but double dash comments can be enclosed into a block comment and a double dash can hide the block comment delimiters /* and */.
Any statement in the statement section of a block can be a subblock. Subblocks can be used for logical grouping or to localize variables to a small group of statements.
The variables declared in the declarations section preceding a block are initialized to their default values every time the block is entered, not only once per function call. For example declares the variable quantity several times:
CREATE FUNCTION testfunc() RETURNS integer AS $$ DECLARE quantity integer := 30; BEGIN RAISE NOTICE 'Quantity here is %', quantity; -- Quantity here is 30 quantity := 50; -- -- Create a subblock -- DECLARE quantity integer := 80; BEGIN RAISE NOTICE 'Quantity here is %', quantity; -- Quantity here is 80 END; RAISE NOTICE 'Quantity here is %', quantity; -- Quantity here is 50 RETURN quantity; END; $$ LANGUAGE plpgsql;
Executing SQL Commands
You can execute SQL commands with PL/pgSQL statements such as EXECUTE, PERFORM, and SELECT ... INTO. For information about the PL/pgSQL statements, see https://www.postgresql.org/docs/8.2/static/plpgsql-statements.html.
The following are examples of PL/pgSQL user-defined functions.
Example: Aliases for Function Parameters
Parameters passed to functions are named with the identifiers such as $1, $2. Optionally, aliases can be declared for $n parameter names for increased readability. Either the alias or the numeric identifier can then be used to refer to the parameter value.
There are two ways to create an alias. The preferred way is to give a name to the parameter in the CREATE FUNCTION command, for example:
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql;
You can also explicitly declare an alias, using the declaration syntax:
name ALIAS FOR $n;
This example, creates the same function with the DECLARE syntax.
CREATE FUNCTION sales_tax(real) RETURNS real AS $$ DECLARE subtotal ALIAS FOR $1; BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql;
Example: Using the Data Type of a Table Column
When declaring a variable, you can use %TYPE to specify the data type of a variable or table column. This is the syntax for declaring a variable with the data type of a table column:
You can use this to declare variables that will hold database values. For example, if you have a column named user_id in your users table. To declare the variable my_userid with the same data type as the users.user_id column:
%TYPE is particularly valuable in polymorphic functions, since the data types needed for internal variables may change from one call to the next. Appropriate variables can be created by applying %TYPE to the function’s arguments or result placeholders.
Example: Composite Type Based on a Table Row
The following syntax declares a composite variable based on table row:
Such a row variable can hold a whole row of a SELECT or FOR query result, so long as that query column set matches the declared type of the variable. The individual fields of the row value are accessed using the usual dot notation, for example rowvar.column.
Parameters to a function can be composite types (complete table rows). In that case, the corresponding identifier $n will be a row variable, and fields can be selected from it, for example $1.user_id.
Only the user-defined columns of a table row are accessible in a row-type variable, not the OID or other system columns. The fields of the row type inherit the table’s field size or precision for data types such as char(n).
CREATE TABLE table1 ( f1 text, f2 numeric, f3 integer ) distributed by (f1);
INSERT INTO table1 values ('test1', 14.1, 3), ('test2', 52.5, 2), ('test3', 32.22, 6), ('test4', 12.1, 4) ;
This function uses a variable and ROWTYPE composite variable based on table1.
CREATE OR REPLACE FUNCTION t1_calc( name text) RETURNS integer AS $$ DECLARE t1_row table1%ROWTYPE; calc_int table1.f3%TYPE; BEGIN SELECT * INTO t1_row FROM table1 WHERE table1.f1 = $1 ; calc_int = (t1_row.f2 * t1_row.f3)::integer ; RETURN calc_int ; END; $$ LANGUAGE plpgsql VOLATILE;
The following SELECT command uses the function.
select t1_calc( 'test1' );
The Postgres documentation about PL/pgSQL is at https://www.postgresql.org/docs/8.2/static/plpgsql.html
Also, see the CREATE FUNCTION command in the Greenplum Database Reference Guide.
For a summary of built-in Greenplum Database functions, see Summary of Built-in Functions in the Greenplum Database Reference Guide. For information about using Greenplum Database functions see "Querying Data" in the Greenplum Database Administrator Guide
For information about porting Oracle functions, see https://www.postgresql.org/docs/8.2/static/plpgsql-porting.html. For information about installing and using the Oracle compatibility functions with Greenplum Database, see "Oracle Compatibility Functions" in the Greenplum Database Utility Guide.