/* 

Creates a normalised table and insert function

Needed variables:

  :entity_plural
  :entity

use function get_XXX_id(YYY) to get the ID of a XXX for value YYY.
The generated function is fast and reliable (race conditions!).


Example for entity host, hosts:

Creates a function calles get_host_id(VARCHAR) and a table with an ID and 
a "host" column.
When calling get_host_id, it returns the ID of an already existing 
row with the given host or inserts a new one and returns this ID.

For reliability get_host_id(host) has three steps:

  1. Try to SELECT the ID, finish when found some
  2. When none found, try to INSERT a new row and return ID, finish when no conflice
  3. When conflict (concurrent insert of the same host), then again try to SELECT.

*/

\set insert_function _insert_ :entity
\set get_function    get_ :entity _id
\set entity_in       :entity _in


CREATE TABLE :entity_plural 
    (
    id      SERIAL  NOT NULL PRIMARY KEY,
    :entity VARCHAR NOT NULL
    );

\set table :entity_plural
\ir 21-grant-reader.sql

CREATE UNIQUE INDEX ON :entity_plural(:entity varchar_pattern_ops);



-- only internal use:
-- insert or do nothing when already exists

-- ugly, to trick out psql and psql variables in quoted strings ...
\set code '$CODE$ INSERT INTO ' :entity_plural ' (' :entity ') VALUES (' :entity_in ') ON CONFLICT (' :entity ') DO NOTHING RETURNING id; $CODE$'

CREATE OR REPLACE FUNCTION :insert_function(:entity_in VARCHAR)
RETURNS INTEGER AS
    :code
    LANGUAGE sql
    VOLATILE
    RETURNS NULL ON NULL INPUT;


-- Select ID; or Insert; or select (because possible race condition: other process may inserted this ID in the meantime concurrently!).

\set _select '(SELECT id FROM ' :entity_plural ' WHERE ' :entity ' = ' :entity_in ')'
\set _insert :insert_function '(' :entity_in ')'
\set code   '$CODE$ SELECT COALESCE( ' :_select ', ' :_insert ', ' :_select '); $CODE$'

CREATE OR REPLACE FUNCTION :get_function(:entity_in VARCHAR)
    RETURNS INTEGER AS
        :code
        LANGUAGE sql
        VOLATILE
        RETURNS NULL ON NULL INPUT
        SECURITY DEFINER
        SET search_path TO :schema, pg_temp;


\set function get_ :entity _id(VARCHAR)
\ir 52-function-inserter.sql