Trunklet

Jim C. Nasby
<Jim.Nasby@BlueTreble.com>
version 0.0.1, January 2015
Trunklet is an extensible templating system for Postgres. It supports storing templates to be used later, as well as creating new templating languages.
Table of Contents
Table of Contents

1. API

1.1. Template Languages

Trunklet is designed to allow for multiple different templating languages to be defined. This allows working in whatever template language is most comfortable to you.

1.1.1. template_language__add

template_language__add(
  language_name varchar(100)
  , parameter_type regtype
  , template_type regtype
  , process_function_options text
  , process_function_body text
  , extract_parameters_options text
  , extract_parameters_body text
) RETURNS void

PERMISSIONS: restricted to superuser. Please contact me if this doesn’t work for you.

Add a new template language.

language_name
must be non-blank and may not start or end with whitespace.

parameter_type
template_type
These are the data types that the language expects parameters and templates to be in.

process_function_options
process_function_body
Besides supplying a name for your language, you must also create a process() function. Trunklet will actually create the function for you. In particular, it will generate a name for the function. process_function_options are the options for the function (things like STRICT, LANGUAGE, etc). process_function_body is the body of the function.

The function must accept (template <template_type>, parameters <parameter_type>), where <template_type> and <parameter_type> will be replaced by the types specified via the respective arguments to template_language__add(). The function must return text.

Essentially, the SQL that trunklet runs is

CREATE FUNCTION <name>(template <template_type>, parameters <parameter_type>)
RETURNS text
<process_function_options>
AS <process_function_body>

extract_parameters_options
extract_parameters_body
Equivalent to the process_function parameters, except these support the [extract_parameters] function. Note that this function must also accept a text array of parameter names to extract. See [extract_parameters] for details.

Warning
 If you create a language as part of an extension, you need to manually ensure that template_language__remove is called. The best way to accomplish that is with extension_drop. You can find an example of this in trunklet-format.
A simple example based on format()
SELECT trunklet.template_language__add(
  get_test_language_name()
  , parameter_type := 'text[]'
  , template_type := 'text'
  , process_function_options := 'LANGUAGE plpgsql'
  , process_function_body := $process$
DECLARE
  -- Convert parameters into a string of ', <parameter 1>, <parameter 2>, ...'
  v_args CONSTANT text := array_to_string(
    array(
      SELECT ', ' || quote_nullable(a)
        FROM unnest(parameters) a(a) -- <1>
      )
    , ''
  );
  sql CONSTANT text := format( 'SELECT format( %L%s )', template, v_args ); -- <2>
  v_return text;
BEGIN
  RAISE DEBUG 'EXECUTE INTO using sql %', sql;
  EXECUTE sql INTO v_return;
  RETURN v_return;
END
$process$
  , extract_parameters_options := 'LANGUAGE sql'
  , extract_parameters_body :=
$extract$
SELECT array(
    SELECT parameters[i] -- <3>
      FROM generate_subscripts( parameters, 1 ) i -- <1>
      WHERE i = ANY( extract_list::int[] ) -- <4>
  )
$extract$
);

  1. Note use of parameters argument

  2. This is where the template argument is used

  3. parameters used with subscripting (since in this example it’s a text array)

  4. extract_list is always passed in as a text array, so in this example it must be re-cast

1.1.2. template_language__remove

template_language__remove( language_name varchar(100)[, cascade boolean][, ignore_missing_functions boolean] ) RETURNS void
template_language__remove( language_id int[, cascade boolean][, ignore_missing_functions boolean] ) RETURNS void

Remove an existing template language.

This will fail if there are any stored templates for that language, unless cascade is true. Even then, it can still fail if template dependencies have been added (see template__dependency__add).

Note
 Generally speaking, the ignore missing_functions option should NOT be used. It exists because if a language is registered as part of an extension then the functions will be dropped by Postgres. See template_language__add for more information.

1.1.3. template_language

VIEW template_language
  language_name varchar(100)
  , process_function_options text
  , process_function_body text
  , extract_parameters_options text
  , extract_parameters_body text

Returns information about registered template languages.

1.2. Storing Templates

You can have Trunklet store frequently used templates for you, so that you can refer to them with just a name.

1.2.1. template__add

template__add( language_name text, template_name text[, template_version int], template ) RETURNS int

Store a template. template_name and template_version must be unique across ALL templates, regardless of language. Any template with _ as the first character will be considered to be “hidden”, meaning it will not show up in template listings.

If template_version is not specified it defaults to 1.

Returns an ID for the stored template.

Note
 In the future we may add a template_specification type or something similar that allows for something finer-grained than a text field for identifying templates. Ideally this would even allow for users to add their own fields. Ideas on this welcome.

1.2.2. template__remove

template__remove( language_name text, template_name text[, template_version int] ) RETURNS void
template__remove( template_id int ) RETURNS void

Remove the specified template. template_version defaults to the latest version if not specified.

1.2.3. template__dependency__add

template__dependency__add( table_name text, field_name text ) RETURNS void

An expected use for storing templates is to allow other parts of a database to store templates and be able to refer to them later. That code may need to store a template identifier in a table. If it does so, you would want to have a foreign key that references the table that Trunklet uses to store templates in. template__dependency__add allows you to create such a dependency. Internally, it simply creates the required foreign key constraint. Note that any role executing this must be granted the trunklet__dependency role.

1.2.4. template__dependency__remove

template__dependency__remove( table_name text, field_name text ) RETURNS void

This function removes an existing template dependency.

1.3. Usage Functions

These are the functions you will use most commonly when dealing with templates. Most of these functions have two versions; one that accepts the name of a template language and an actual template, and a second that accepts a template name and an optional template version. If the template version is omitted the version of the template with the highest version number is used.

1.4. Using templates

These are the functions for actually using templates. Generally they have variations for both stored and ad-hoc templates.

Important
 These function treats a missing version number differently than the functions for [Storing_templates] do. Don’t get the two confused!

1.4.1. process

`process_language( language_name, template, parameters ) RETURNS text`
`process( template_name, [template_version int,] parameters ) RETURNS text`
`process( template_id, parameters ) RETURNS text`

Process the specified template and return the resulting text.

1.4.2. execute

`execute_language( language_name, template, parameters ) RETURNS void`
`execute( template_name, [template_version int,] parameters ) RETURNS void`

Call [process] and execute the result as SQL. This is an easy way to generate and execute dynamic commands.

1.4.3. execute_into

`execute_into_language( language_name, template, parameters ) RETURNS <template type>`
`execute_into( template_name[, template_version int], parameters ) RETURNS <template type>`
`execute_into( template_id, parameters ) RETURNS <template type>`

This is the same as [execute], except we capture the results of the executed SQL. The resulting command must return a single column that is the same type as the parameters.

1.4.4. extract_parameters

`extract_parameters( language_name, parameters, extract_list text[] ) RETURNS <template type>`

Return a specified set of parameter values. This takes parameters and filters it to only return parameters whose name matches one of the names in extract_list.

2. TODOs

Provide a means for callers to pass a set of options to template language functions.

3. Copyright

Copyright (c) 2015 Jim C. Nasby.

Version 0.0.1
Last updated 2017-06-17 20:17:29 CDT