### Test PLV8 Installation Source: https://plv8.github.io/ Verify the PLV8 installation by running the test suite. ```bash $ make installcheck ``` -------------------------------- ### PLV8 Info Output Example Source: https://plv8.github.io/ Example JSON output from the plv8_info() function, detailing heap sizes, memory limits, and the number of native contexts for each user. ```json [ { "user": "user1", "total_heap_size": 1327104, "total_physical_size": 474336, "used_heap_size": 386680, "heap_size_limit": 270008320, "external_memory": 0, "number_of_native_contexts": 2, "contexts": [] }, { "user": "user2", "total_heap_size": 1327104, "total_physical_size": 474336, "used_heap_size": 386680, "heap_size_limit": 270008320, "external_memory": 0, "number_of_native_contexts": 3, "contexts": ["my context"] } ] ``` -------------------------------- ### Install PLV8 Source: https://plv8.github.io/ Install the built PLV8 extension for PostgreSQL. This may require root privileges. ```bash $ make install ``` ```bash $ sudo make install ``` -------------------------------- ### Install PLV8 Extension Source: https://plv8.github.io/ Use this command to install the PLV8 extension into your PostgreSQL database after it has been installed on the system. ```sql CREATE EXTENSION plv8; ``` -------------------------------- ### Verify PLV8 Installation with plv8.version Source: https://plv8.github.io/ An alternative method to verify PLV8 installation across all versions by logging the PLV8 version using plv8.elog. ```sql DO $$ plv8.elog(NOTICE, plv8.version); $$ LANGUAGE plv8; ``` -------------------------------- ### Verify PLV8 Installation with plv8_version() Source: https://plv8.github.io/ Check if PLV8 is installed and get its version using the plv8_version() stored procedure, available from PLV8 2.0.0 onwards. ```sql SELECT plv8_version(); ``` -------------------------------- ### Get PLV8 Runtime Information Source: https://plv8.github.io/ Retrieve information about all currently running PLV8 environments on a specific database connection. This command can only be executed by a superuser. ```sql SELECT plv8_info(); ``` -------------------------------- ### Build PLV8 for Specific PostgreSQL Version Source: https://plv8.github.io/ Specify the PG_CONFIG variable when running make to build PLV8 for a particular PostgreSQL version, especially when multiple versions are installed. ```bash $ make PG_CONFIG=/usr/lib/postgresql/13/bin/pg_config ``` -------------------------------- ### Update PLV8 Extension Source: https://plv8.github.io/ Upgrade an existing PLV8 installation to a newer version using the ALTER EXTENSION command. Note that the old version remains loaded until the database is restarted. ```sql ALTER EXTENSION plv8 UPDATE TO `3.2.0`; ``` -------------------------------- ### Get PLV8 Memory Usage Source: https://plv8.github.io/ Retrieves the current memory usage statistics for the PLV8 environment. The output object provides details on heap size, physical size, and used memory. ```javascript { "total_heap_size":1327104, "total_physical_size":472712, "used_heap_size":381748, "heap_size_limit":270008320, "external_memory":0, "number_of_native_contexts":2 } ``` -------------------------------- ### Download and Build PLV8 from Source Source: https://plv8.github.io/ Clone the PLV8 repository and compile it using make. This process downloads and builds the V8 engine as well. ```bash $ git clone https://github.com/plv8/plv8 $ cd plv8 $ make ``` ```bash $ make ``` -------------------------------- ### plv8.prepare Source: https://plv8.github.io/ Opens or creates a prepared statement for executing SQL queries efficiently. Returns a `PreparedPlan` object that must be freed. ```APIDOC ## plv8.prepare ### Description Opens or creates a prepared statement for executing SQL queries efficiently. Returns a `PreparedPlan` object that must be freed. ### Parameters * `sql` (string) - Required - The SQL statement to prepare. * `typenames` (array) - Optional - An array of strings representing the PostgreSQL type names for each bind parameter. ### Returns A `PreparedPlan` object. ### Usage ```javascript var plan = plv8.prepare('SELECT * FROM tbl WHERE col = $1', [ 'int' ]); // ... use plan.execute() or plan.cursor() plan.free(); ``` ``` -------------------------------- ### Define and Call PLV8 Functions Source: https://plv8.github.io/ Demonstrates how to define a PLV8 function and then find and call it from another PLV8 function using plv8.find_function. Ensure functions are registered in the database before calling. ```plv8 CREATE FUNCTION callee(a int) RETURNS int AS $$ return a * a $$ LANGUAGE plv8; CREATE FUNCTION caller(a int, t int) RETURNS int AS $$ var func = plv8.find_function("callee"); return func(a); $$ LANGUAGE plv8; ``` -------------------------------- ### PreparedPlan.execute Source: https://plv8.github.io/ Executes a prepared statement. The result is the same as `plv8.execute()`. ```APIDOC ## PreparedPlan.execute ### Description Executes the prepared statement. The result of this method is the same as `plv8.execute()`. ### Parameters * `args` (array) - Optional - An array of arguments to pass to the prepared statement. ### Returns An array of objects for `SELECT` queries, or the number of rows affected for other queries. ### Usage ```javascript var rows = plan.execute([ 1 ]); ``` ``` -------------------------------- ### Prepare and Execute SQL Statements Source: https://plv8.github.io/ Prepares an SQL statement for repeated execution using plv8.prepare, specifying parameter types. The prepared plan must be freed using plan.free() after use. Results are fetched using plan.execute(). ```javascript var plan = plv8.prepare('SELECT * FROM tbl WHERE col = $1', [ 'int' ]); var rows = plan.execute([ 1 ]); var sum = 0; for (var i = 0; i < rows.length; i++) { sum += rows[i].num; } plan.free(); return sum; ``` -------------------------------- ### Logging with plv8.elog Source: https://plv8.github.io/ Demonstrates how to use the plv8.elog function to emit messages at different logging levels. The message is sent to the client or PostgreSQL log file. ```javascript var msg = 'world'; plv8.elog(DEBUG1, 'Hello', `${msg}!`); ``` -------------------------------- ### Create and Use Cursor from Prepared Statement Source: https://plv8.github.io/ Opens a cursor from a prepared statement using plan.cursor to iterate over query results row by row. The cursor must be closed using cursor.close() and the plan freed using plan.free(). ```javascript var plan = plv8.prepare('SELECT * FROM tbl WHERE col = $1', [ 'int' ]); var cursor = plan.cursor([ 1 ]); var sum = 0, row; while (row = cursor.fetch()) { sum += row.num; } cursor.close(); plan.free(); return sum; ``` -------------------------------- ### PreparedPlan.free Source: https://plv8.github.io/ Frees the resources associated with a prepared statement. ```APIDOC ## PreparedPlan.free ### Description Frees the resources associated with a prepared statement. ### Usage ```javascript plan.free(); ``` ``` -------------------------------- ### PreparedPlan.cursor Source: https://plv8.github.io/ Opens a cursor from a prepared statement for iterating over results. Returns a `Cursor` object that must be closed. ```APIDOC ## PreparedPlan.cursor ### Description Opens a cursor from a prepared statement for iterating over results. Returns a `Cursor` object that must be closed. ### Parameters * `args` (array) - Optional - An array of arguments to pass to the prepared statement. ### Returns A `Cursor` object. ### Usage ```javascript var cursor = plan.cursor([ 1 ]); // ... use cursor.fetch(), cursor.move(), cursor.close() ``` ``` -------------------------------- ### Handling IN/OUT Parameters in PLV8 Functions Source: https://plv8.github.io/ Demonstrates a PLV8 function with IN and INOUT parameters. Only named parameters in the function definition are returned. ```plv8 CREATE FUNCTION inout_test(IN t1 TEXT, INOUT i1 INTEGER, OUT o1 TEXT) AS $$ return { i1: 23, o1: t1 + i1, foo: 'bar' }; $$ LANGUAGE plv8; ``` ```sql SELECT * FROM inout_test('hello', 5); ``` -------------------------------- ### Summing an Integer Array with PLV8 Source: https://plv8.github.io/ Demonstrates how to use PLV8 to sum elements of a plv8_int4array. The function expects a 1-dimensional array without NULL elements. ```plv8 CREATE FUNCTION int4sum(ary plv8_int4array) RETURNS int8 AS $$ var sum = 0; for (var i = 0; i < ary.length; i++) { sum += ary[i]; } return sum; $$ LANGUAGE plv8 IMMUTABLE STRICT; ``` ```sql SELECT int4sum(ARRAY[1, 2, 3, 4, 5]); ``` -------------------------------- ### Set PLV8 Initialization Procedure Source: https://plv8.github.io/ Configure PLV8 to run an initialization function when the runtime is initialized. This function can be used to set up global variables or properties on the plv8 object. ```sql SET plv8.start_proc = 'plv8_init'; SELECT plv8_test(10); ``` -------------------------------- ### Handling IN/OUT Parameters in PLV8 Procedures Source: https://plv8.github.io/ Illustrates a PLV8 procedure with IN and INOUT parameters. OUT arguments must be explicitly used in the CALL statement. ```plv8 CREATE PROCEDURE procedure_inout_test(IN t1 TEXT, INOUT i1 INTEGER, OUT o1 TEXT) AS $$ return { i1: 23, o1: t1 + i1, foo: 'bar' }; $$ LANGUAGE plv8; ``` ```sql CALL procedure_inout_test('hello', 5, 'foo'); ``` -------------------------------- ### Build PLV8 with Execution Timeout Source: https://plv8.github.io/ Compile PLV8 with an execution timeout enabled. The default timeout is 300 seconds, but can be overridden. ```bash $ make EXECUTION_TIMEOUT=1 ``` -------------------------------- ### Query PLV8 Language, Procedures, and Types Source: https://plv8.github.io/ Explore the objects associated with the PLV8 extension within PostgreSQL, including its language, procedures, and types. ```sql SELECT lanname FROM pg_catalog.pg_language WHERE lanname = 'plv8'; ``` ```sql SELECT proname FROM pg_proc p WHERE p.proname LIKE 'plv8%'; ``` ```sql SELECT typname FROM pg_catalog.pg_type WHERE typname LIKE 'plv8%'; ``` -------------------------------- ### Window Function API Source: https://plv8.github.io/ Provides interfaces for defining user-defined window functions in PLV8, wrapping the C-level window function API. ```APIDOC ## Window Function API ### Description Allows the creation of user-defined window functions by providing access to PLV8's window function capabilities. This involves obtaining a window object and utilizing its methods to interact with partitions and rows. ### Getting a Window Object Use `plv8.get_window_object()` to obtain an object with window function interfaces. ### Available Methods on Window Object: #### `WindowObject.get_current_position` * **Description**: Returns the current row's position within the partition, starting from 0. * **Method**: `WindowObject.get_current_position()` #### `WindowObject.get_partition_row_count` * **Description**: Returns the total number of rows in the current partition. * **Method**: `WindowObject.get_partition_row_count()` #### `WindowObject.set_mark_position` * **Description**: Sets a mark at a specified row position. Rows above this mark become inaccessible. * **Method**: `WindowObject.set_mark_position(pos)` * **Parameters**: * **pos** (integer) - The row position to set the mark at. #### `WindowObject.rows_are_peers` * **Description**: Checks if two rows at the given positions are peers within the partition. * **Method**: `WindowObject.rows_are_peers(pos1, pos2)` * **Parameters**: * **pos1** (integer) - The position of the first row. * **pos2** (integer) - The position of the second row. * **Returns**: `true` if the rows are peers, `false` otherwise. #### `WindowObject.get_func_arg_in_partition` * **Description**: Retrieves the value of a function argument at a specified position within the partition. * **Method**: `WindowObject.get_func_arg_in_partition(argno, relpos, seektype, mark_pos)` * **Parameters**: * **argno** (integer) - The index of the function argument (starting from 0). * **relpos** (integer) - The relative position from the seek type. * **seektype** (enum) - Specifies the reference point for `relpos`. Can be `WindowObject.SEEK_HEAD`, `WindowObject.SEEK_CURRENT`, or `WindowObject.SEEK_TAIL`. * **mark_pos** (boolean) - If true, marks the row from which the argument is fetched. * **Returns**: The argument value, or `undefined` if out of bounds. #### `WindowObject.get_func_arg_in_frame` * **Description**: Retrieves the value of a function argument at a specified position within the current frame. * **Method**: `WindowObject.get_func_arg_in_frame(argno, relpos, seektype, mark_pos)` * **Parameters**: * **argno** (integer) - The index of the function argument (starting from 0). * **relpos** (integer) - The relative position from the seek type. * **seektype** (enum) - Specifies the reference point for `relpos`. Can be `WindowObject.SEEK_HEAD`, `WindowObject.SEEK_CURRENT`, or `WindowObject.SEEK_TAIL`. * **mark_pos** (boolean) - If true, marks the row from which the argument is fetched. * **Returns**: The argument value, or `undefined` if out of bounds. #### `WindowObject.get_func_arg_in_current` * **Description**: Returns the value of a function argument at the current row. * **Method**: `WindowObject.get_func_arg_in_current(argno)` * **Parameters**: * **argno** (integer) - The index of the function argument (starting from 0). * **Returns**: The argument value. #### `WindowObject.get_partition_local` * **Description**: Retrieves a partition-local value. This value is released at the end of the current partition. * **Method**: `WindowObject.get_partition_local([ size ])` * **Parameters**: * **size** (integer, optional) - The byte size for allocated memory (defaults to 1000). The size is fixed after the first call. * **Returns**: The stored partition-local value, or `undefined` if nothing is stored. #### `WindowObject.set_partition_local` * **Description**: Stores a partition-local value. The object is serialized using `JSON.stringify()`. Throws an exception if the serialized value exceeds allocated memory. * **Method**: `WindowObject.set_partition_local(obj)` * **Parameters**: * **obj** (any) - The object to store as a partition-local value. ### Further Information Refer to `sql/window.sql` for examples and the PostgreSQL manual's CREATE FUNCTION page for general window function details. ``` -------------------------------- ### plv8.version Source: https://plv8.github.io/ Retrieves the version string of the PLV8 module. ```APIDOC ## plv8.version ### Description Retrieves the version string of the PLV8 module. ### Usage ```javascript plv8.version ``` ``` -------------------------------- ### Create Set-Returning PLV8 Function Source: https://plv8.github.io/ Define a set-returning function in PLV8 that returns multiple records. Records can be added using `plv8.return_next()` or by returning an array of JSON objects. ```sql CREATE TYPE rec AS (i integer, t text); CREATE FUNCTION set_of_records() RETURNS SETOF rec AS $$ // plv8.return_next() stores records in an internal tuplestore, // and return all of them at the end of function. plv8.return_next( { "i": 1, "t": "a" } ); plv8.return_next( { "i": 2, "t": "b" } ); // You can also return records with an array of JSON. return [ { "i": 3, "t": "c" }, { "i": 4, "t": "d" } ]; $$ LANGUAGE plv8; ``` ```sql =# SELECT * FROM set_of_records(); i | t ---+--- 1 | a 2 | b 3 | c 4 | d (4 rows) ``` -------------------------------- ### Returning a Generic Record with PLV8 Source: https://plv8.github.io/ Illustrates a PLV8 function that returns a generic RECORD type. The query must explicitly define the output structure. ```plv8 CREATE FUNCTION return_record(i integer, t text) RETURNS record AS $$ return { "i": i, "t": t }; $$ LANGUAGE plv8; ``` ```sql SELECT * FROM return_record(17, 'skidoo') AS ( i INT, t TEXT ); ``` -------------------------------- ### Drop and Recreate PLV8 Extension Source: https://plv8.github.io/ Manually update PLV8 by dropping the existing extension and creating it again with the new version. This is an alternative to using ALTER EXTENSION. ```sql DROP EXTENSION plv8; ``` ```sql CREATE EXTENSION plv8; ``` -------------------------------- ### Execute Inline PLV8 Statement Source: https://plv8.github.io/ Run an inline PLV8 code block using the `DO` statement, available in PostgreSQL 9.0 and above. ```sql DO $$ plv8.elog(NOTICE, 'this', 'is', 'inline', 'code'); $$ LANGUAGE plv8; ``` -------------------------------- ### Cursor.fetch Source: https://plv8.github.io/ Fetches rows from a cursor. Can fetch a single row or multiple rows. ```APIDOC ## Cursor.fetch ### Description Fetches rows from a cursor. Can fetch a single row or multiple rows. ### Parameters * `nrows` (integer) - Optional - The number of rows to fetch. If omitted, fetches a single row. A negative value fetches backward. ### Returns An object representing a single row, or an array of objects if `nrows` is specified. ### Usage ```javascript var row = cursor.fetch(); var rows = cursor.fetch(10); ``` ``` -------------------------------- ### Run Script from Source Code Source: https://plv8.github.io/ Executes a JavaScript script from a string using plv8.run_script. This is useful for debugging and dynamic code execution. The second argument specifies the script name for error reporting. ```javascript const sourceCode = `globalThis.myFunc = () => 42` try { plv8.run_script(sourceCode, 'myScript.js') myFunc() } catch (e) { plv8.elog(NOTICE, e.message) } ``` -------------------------------- ### plv8.execute Source: https://plv8.github.io/ Executes SQL statements and retrieves results. For `SELECT` queries, returns an array of objects representing rows. For other queries, returns the number of rows affected. ```APIDOC ## plv8.execute ### Description Executes SQL statements and retrieves results. For `SELECT` queries, returns an array of objects representing rows. For other queries, returns the number of rows affected. ### Parameters * `sql` (string) - Required - The SQL statement to execute. * `args` (array) - Optional - An array of arguments to pass to the SQL query. ### Usage ```javascript var json_result = plv8.execute('SELECT * FROM tbl'); var num_affected = plv8.execute('DELETE FROM tbl WHERE price > $1', [ 1000 ]); ``` ``` -------------------------------- ### Execute SQL Statements Source: https://plv8.github.io/ Executes SQL queries using plv8.execute. It supports both SELECT statements, returning an array of objects, and non-SELECT statements, returning the number of affected rows. Optional arguments can be passed for parameterized queries. ```javascript var json_result = plv8.execute('SELECT * FROM tbl'); var num_affected = plv8.execute('DELETE FROM tbl WHERE price > $1', [ 1000 ]); ``` -------------------------------- ### Reset PLV8 Context Source: https://plv8.github.io/ Reset a specific PLV8 context or all contexts for a user. Resetting a context will re-create its globalThis on the next invocation. Superusers can reset a specific user's entire environment. ```sql SELECT plv8_reset('my context'); ``` ```sql SELECT plv8_reset(); ``` ```sql SET ROLE "some_user"; SELECT plv8_reset(); RESET ROLE; ``` -------------------------------- ### plv8.run_script Source: https://plv8.github.io/ Executes a JavaScript script from a source string, similar to `eval()`, but allows specifying a script name for debugging purposes. ```APIDOC ## plv8.run_script ### Description Executes a JavaScript script from a source string, similar to `eval()`, but allows specifying a script name for debugging purposes. ### Usage ```javascript const sourceCode = `globalThis.myFunc = () => 42` plv8.run_script(sourceCode, 'myScript.js') ``` ``` -------------------------------- ### plv8.memory_usage Source: https://plv8.github.io/ Returns an object containing statistics about the V8 JavaScript engine's memory usage. ```APIDOC ## plv8.memory_usage ### Description Returns an object containing statistics about the V8 JavaScript engine's memory usage, similar to `v8.getHeapStatistics()`. ### Usage ```javascript plv8.memory_usage() ``` ### Returns An object with memory statistics, including `total_heap_size`, `used_heap_size`, `heap_size_limit`, etc. ``` -------------------------------- ### Mutating a Record Type with PLV8 Source: https://plv8.github.io/ Shows how to define a function that accepts and returns a custom record type, modifying its fields within PLV8. ```sql CREATE TYPE record_type AS (i integer, t text); ``` ```plv8 CREATE FUNCTION mutate_record(rec record_type) RETURNS record_type AS $$ return { i: i * 2, t: `t => ${t}` }; $$ LANGUAGE plv8; ``` ```sql SELECT * FROM mutate_record('(17, skidoo)'::record_type); ``` -------------------------------- ### Create PLV8 Trigger Function Source: https://plv8.github.io/ Define a trigger function in PLV8 to execute before insert, update, or delete operations. It can log trigger state and modify the `NEW` tuple on update. ```sql CREATE FUNCTION test_trigger() RETURNS TRIGGER AS $$ plv8.elog(NOTICE, "NEW = ", JSON.stringify(NEW)); plv8.elog(NOTICE, "OLD = ", JSON.stringify(OLD)); plv8.elog(NOTICE, "TG_OP = ", TG_OP); plv8.elog(NOTICE, "TG_ARGV = ", TG_ARGV); if (TG_OP == "UPDATE") { NEW.i = 102; return NEW; } $$ LANGUAGE "plv8"; CREATE TRIGGER test_trigger BEFORE INSERT OR UPDATE OR DELETE ON test_tbl FOR EACH ROW EXECUTE PROCEDURE test_trigger('foo', 'bar'); ``` -------------------------------- ### plv8.find_function Source: https://plv8.github.io/ Looks up other PLV8 functions registered in the database. Throws an error if the function is not a PLV8 function. Accepts function identifiers as `regproc` or `regprocedure`. ```APIDOC ## plv8.find_function ### Description Looks up other PLV8 functions registered in the database. Throws an error if the function is not a PLV8 function. Accepts function identifiers as `regproc` or `regprocedure`. ### Usage ```javascript var func = plv8.find_function("callee"); func(a); ``` ``` -------------------------------- ### Execute SQL within a Subtransaction Source: https://plv8.github.io/ Use plv8.subtransaction() to create an atomic operation block. If any SQL execution within the block fails, all operations are rolled back. Use a try...catch block to handle Javascript exceptions. ```javascript try{ plv8.subtransaction(function(){ plv8.execute("INSERT INTO tbl VALUES(1)"); // should be rolled back! plv8.execute("INSERT INTO tbl VALUES(1/0)"); // occurs an exception }); } catch(e) { ... execute fall back plan ... } ``` -------------------------------- ### Cursor.move Source: https://plv8.github.io/ Moves the cursor position by a specified number of rows. ```APIDOC ## Cursor.move ### Description Moves the cursor position by a specified number of rows. ### Parameters * `nrows` (integer) - Required - The number of rows to move. A negative value moves backward. ### Usage ```javascript cursor.move(5); ``` ``` -------------------------------- ### Handling INOUT Scalar Parameter in PLV8 Function Source: https://plv8.github.io/ Shows a PLV8 function with a single INOUT scalar parameter. The return type must be a scalar value. ```plv8 CREATE FUNCTION scalar_test(INOUT i1 INTEGER) AS $$ return i1 + 5; $$ LANGUAGE plv8; ``` ```sql SELECT * FROM scalar_test(23); ``` -------------------------------- ### plv8.subtransaction Source: https://plv8.github.io/ Creates a subtransaction block for atomic operations. If any SQL execution within the block fails, all operations are rolled back. Javascript exceptions are propagated. ```APIDOC ## plv8.subtransaction ### Description Manages subtransactions to ensure atomic operations within a block. If any SQL statement within the block fails, the entire block is rolled back. Javascript exceptions are also handled and can be caught. ### Method `plv8.subtransaction(func)` ### Parameters * **func** (function) - A function containing the SQL operations to be executed within the subtransaction. ### Request Example ```javascript try{ plv8.subtransaction(function(){ plv8.execute("INSERT INTO tbl VALUES(1)"); // should be rolled back! plv8.execute("INSERT INTO tbl VALUES(1/0)"); // occurs an exception }); } catch(e) { // execute fall back plan } ``` ### Response This function does not return a value directly but manages transaction state. Exceptions thrown within the `func` will be propagated. ``` -------------------------------- ### Cursor.close Source: https://plv8.github.io/ Closes the cursor, releasing associated resources. ```APIDOC ## Cursor.close ### Description Closes the cursor, releasing associated resources. ### Usage ```javascript cursor.close(); ``` ``` -------------------------------- ### Create Scalar PLV8 Function Source: https://plv8.github.io/ Define a scalar function in PLV8 that takes arrays of keys and values and returns a JSON object. Arguments are type-checked by PostgreSQL. ```sql CREATE FUNCTION plv8_test(keys TEXT[], vals TEXT[]) RETURNS JSON AS $$ var o = {}; for(var i=0; i