Javascript in PostgreSQL pl/v8 – tutorial 1 – overview

Extension pl/v8 contains 3 different flavors of JavaScript:

1. PL/CoffeeScript (v8) trusted procedural language:
   • enable in pq:

     CREATE EXTENSION plcoffee;

2. PL/LiveScript (v8) trusted procedural language:

   • CREATE EXTENSION plls;

3. PL/JavaScript (v8) trusted procedural language:

   • CREATE EXTENSION plv8;

The same keywords for language you use also in function definition:

• LANGUAGE plcoffee
• LANGUAGE plls
• LANGUAGE plv8

To check what is available and installed use:

select * from pg_available_extensions where comment like '%(v8)%' order by name

Implemented functions – see documentation for PL/v8:

• database access:
  • plv8.execute( sql, [args] )
  • plv8.prepare( sql. [typenames] )
  • PreparedPlan.execute( [args] )
  • PreparedPlan.cursor( [args] )
  • PreparedPlan.free()
  • Cursor.fetch( [nrows] )
  • Cursor.fetch( [nrows] )
  • Cursor.move( [nrows] )
  • Cursor.close()
• subtransaction
  • plv8.subtransaction( func )
• trigger function
  • the same as in PL/pgSQL- special arguments available:
    • NEW, OLD
    • TG_NAME, TG_WHEN, TG_LEVEL, TG_OP
    • TG_RELID, TG_TABLE_NAME, TG_TABLE_SCHEMA
    • TG_ARGV
• utility functions
  • plv8.elog(level, msg1, msg2,….)  =RAISE in pl/pgsql
  • plv8.find_function(str) – gives pointer
  • plv8.nullable(str)
  • plv8.quote_literal(str)
  • plv8.quote_ident(str)
  • plv8.version
• window functions
  • plv8.get_window_object()
  • WindowObject. functions:
    • get_current_position
    • get_partition_row_count
• start-up procedure:
  • specify in variable:
    • SET plv8.start_proc = ‘<name>’;

CoffeeScript, LiveScript and JavaScript already have lots of modules. But PostgreSQL extensions are considered to be “trusted” which mean they are sandboxed and cannot load modules from the file system.

But we can prepare modules in the database and then load them inside the database. See Andrew Dunstan’s PostgreSQL and Technical blog