plpgsql/types.sql

   1 -- ---------------------------------------------------------
   2 -- list of types
   3
   4 CREATE TABLE type (
   5     type_id  integer NOT NULL,
   6     name     char(10)
   7 );
   8 ALTER TABLE type ADD CONSTRAINT pk_types_id
   9     PRIMARY KEY (type_id);
  10
  11 INSERT INTO type VALUES (0, 'nil');
  12 INSERT INTO type VALUES (1, 'false');
  13 INSERT INTO type VALUES (2, 'true');
  14 INSERT INTO type VALUES (3, 'integer');
  15 INSERT INTO type VALUES (4, 'float');
  16 INSERT INTO type VALUES (5, 'string');
  17 --INSERT INTO type VALUES (6, 'keyword');
  18 INSERT INTO type VALUES (7, 'symbol');
  19 INSERT INTO type VALUES (8, 'list');
  20 INSERT INTO type VALUES (9, 'vector');
  21 INSERT INTO type VALUES (10, 'hashmap');
  22 INSERT INTO type VALUES (11, 'function');
  23 INSERT INTO type VALUES (12, 'malfunc');
  24 INSERT INTO type VALUES (13, 'atom');
  25
  26
  27 -- ---------------------------------------------------------
  28 -- collections/groupings
  29
  30 CREATE TABLE collection (
  31     collection_id   integer NOT NULL,  -- same for items of a collection
  32     idx             integer,           -- set for list and vector items
  33     key_string      varchar,           -- set for hashmap items
  34     value_id        integer,           -- set for all items (ast for functions)
  35     params_id       integer,           -- set for functions
  36     env_id          integer,           -- set for functions
  37     macro           boolean            -- set for macro functions
  38 );
  39 -- ALTER TABLE collection ADD CONSTRAINT pk_collection
  40 --     PRIMARY KEY (collection_id, idx, key_string);
  41 -- value_id, params_id foreign keys are after value table
  42 CREATE INDEX ON collection (collection_id);
  43 CREATE INDEX ON collection (collection_id, idx);
  44 CREATE INDEX ON collection (collection_id, key_string);
  45
  46
  47 -- ---------------------------------------------------------
  48 -- persistent values
  49
  50 CREATE SEQUENCE value_id_seq START WITH 3; -- skip nil, false, true
  51 CREATE TABLE value (
  52     value_id        integer NOT NULL DEFAULT nextval('value_id_seq'),
  53     type_id         integer NOT NULL,
  54     val_int         bigint,   -- set for integers
  55     val_string      varchar,  -- set for strings, keywords, and symbols
  56     collection_id   integer,  -- set for lists, vectors and hashmaps
  57                               -- (NULL for empty collection)
  58     function_name   varchar,  -- set for native function types
  59     meta_id         integer   -- can be set for any collection
  60 );
  61 ALTER TABLE value ADD CONSTRAINT pk_value_id
  62     PRIMARY KEY (value_id);
  63 -- drop sequence when table dropped
  64 ALTER SEQUENCE value_id_seq OWNED BY value.value_id;
  65 ALTER TABLE value ADD CONSTRAINT fk_type_id
  66     FOREIGN KEY (type_id) REFERENCES type(type_id);
  67 ALTER TABLE value ADD CONSTRAINT fk_meta_id
  68     FOREIGN KEY (meta_id) REFERENCES value(value_id);
  69 -- ALTER TABLE value ADD CONSTRAINT fk_collection_id
  70 --    FOREIGN KEY (collection_id) REFERENCES collection(collection_id, idx, key_string);
  71 -- References from collection back to value
  72 ALTER TABLE collection ADD CONSTRAINT fk_value_id
  73     FOREIGN KEY (value_id) REFERENCES value(value_id);
  74 ALTER TABLE collection ADD CONSTRAINT fk_params_id
  75     FOREIGN KEY (params_id) REFERENCES value(value_id);
  76
  77 CREATE INDEX ON value (value_id, type_id);
  78
  79 INSERT INTO value (value_id, type_id) VALUES (0, 0); -- nil
  80 INSERT INTO value (value_id, type_id) VALUES (1, 1); -- false
  81 INSERT INTO value (value_id, type_id) VALUES (2, 2); -- true
  82
  83
  84 -- ---------------------------------------------------------
  85 -- general functions
  86
  87 CREATE FUNCTION _wraptf(val boolean) RETURNS integer AS $$
  88 BEGIN
  89     IF val THEN
  90         RETURN 2;
  91     ELSE
  92         RETURN 1;
  93     END IF;
  94 END; $$ LANGUAGE plpgsql;
  95
  96 -- pun both NULL and false to false
  97 CREATE FUNCTION _tf(val boolean) RETURNS boolean AS $$
  98 BEGIN
  99     IF val IS NULL OR val = false THEN
 100         RETURN false;
 101     END IF;
 102     RETURN true;
 103 END; $$ LANGUAGE plpgsql;
 104
 105 -- pun both NULL and 0 to false
 106 CREATE FUNCTION _tf(val integer) RETURNS boolean AS $$
 107 BEGIN
 108     IF val IS NULL OR val = 0 THEN
 109         RETURN false;
 110     END IF;
 111     RETURN true;
 112 END; $$ LANGUAGE plpgsql;
 113
 114 -- return the type of the given value_id
 115 CREATE FUNCTION _type(obj integer) RETURNS integer AS $$
 116 BEGIN
 117     RETURN (SELECT type_id FROM value WHERE value_id = obj);
 118 END; $$ LANGUAGE plpgsql;
 119
 120
 121
 122
 123 CREATE FUNCTION _equal_Q(a integer, b integer) RETURNS boolean AS $$
 124 DECLARE
 125     atype  integer;
 126     btype  integer;
 127     akey   varchar;
 128     bkey   varchar;
 129     avid   integer;
 130     bvid   integer;
 131     acid   integer;
 132     bcid   integer;
 133     i      integer;
 134 BEGIN
 135     atype := _type(a);
 136     btype := _type(b);
 137     IF NOT ((atype = btype) OR (_sequential_Q(a) AND _sequential_Q(b))) THEN
 138         RETURN false;
 139     END IF;
 140     CASE
 141     WHEN atype = 3 THEN -- integer
 142         SELECT val_int FROM value INTO avid WHERE value_id = a;
 143         SELECT val_int FROM value INTO bvid WHERE value_id = b;
 144         RETURN avid = bvid;
 145     WHEN atype = 5 OR atype = 7 THEN -- string/symbol
 146         RETURN _valueToString(a) = _valueToString(b);
 147     WHEN atype IN (8, 9, 10) THEN -- list/vector/hash-map
 148         IF _count(a) <> _count(b) THEN
 149             RETURN false;
 150         END IF;
 151         SELECT collection_id FROM value INTO acid WHERE value_id = a;
 152         SELECT collection_id FROM value INTO bcid WHERE value_id = b;
 153         IF atype = 10 THEN  -- hash-map
 154             FOR akey, avid IN (SELECT key_string, value_id FROM collection
 155                                WHERE collection_id = acid)
 156             LOOP
 157                 SELECT key_string, value_id INTO bkey, bvid FROM collection
 158                     WHERE collection_id = bcid AND key_string = akey;
 159                 IF bvid IS NULL OR NOT _equal_Q(avid, bvid) THEN
 160                     return false;
 161                 END IF;
 162             END LOOP;
 163         ELSE
 164             FOR i IN 0 .. _count(a)-1
 165             LOOP
 166                 SELECT value_id INTO avid FROM collection
 167                     WHERE collection_id = acid AND idx = i;
 168                 SELECT value_id INTO bvid FROM collection
 169                     WHERE collection_id = bcid AND idx = i;
 170                 IF NOT _equal_Q(avid, bvid) THEN
 171                     return false;
 172                 END IF;
 173             END LOOP;
 174         END IF;
 175         RETURN true;
 176     ELSE
 177         RETURN a = b;
 178     END CASE;
 179 END; $$ LANGUAGE plpgsql;
 180
 181
 182 -- _clone:
 183 -- take a value_id of a collection
 184 -- returns a new value_id of a cloned collection
 185 CREATE FUNCTION _clone(id integer) RETURNS integer AS $$
 186 DECLARE
 187     src_coll_id  integer;
 188     dst_coll_id  integer;
 189     result       integer;
 190 BEGIN
 191     SELECT collection_id FROM value INTO src_coll_id
 192         WHERE value_id = id;
 193
 194     -- copy value and change collection_id to new value
 195     INSERT INTO value (type_id,collection_id)
 196         (SELECT type_id,COALESCE((SELECT Max(collection_id) FROM value)+1,0)
 197             FROM value
 198             WHERE value_id = id)
 199         RETURNING value_id, collection_id INTO result, dst_coll_id;
 200
 201     -- copy collection and change collection_id
 202     INSERT INTO collection
 203         (collection_id,idx,key_string,value_id,params_id,env_id,macro)
 204         (SELECT dst_coll_id,idx,key_string,value_id,params_id,env_id,macro
 205             FROM collection
 206             WHERE collection_id = src_coll_id);
 207
 208     RETURN result;
 209 END; $$ LANGUAGE plpgsql;
 210
 211
 212 -- ---------------------------------------------------------
 213 -- scalar functions
 214
 215
 216 -- _nil_Q:
 217 -- takes a value_id
 218 -- returns the whether value_id is nil
 219 CREATE FUNCTION _nil_Q(id integer) RETURNS boolean AS $$
 220 BEGIN
 221     RETURN id = 0;
 222 END; $$ LANGUAGE plpgsql;
 223
 224 -- _true_Q:
 225 -- takes a value_id
 226 -- returns the whether value_id is true
 227 CREATE FUNCTION _true_Q(id integer) RETURNS boolean AS $$
 228 BEGIN
 229     RETURN id = 2;
 230 END; $$ LANGUAGE plpgsql;
 231
 232 -- _false_Q:
 233 -- takes a value_id
 234 -- returns the whether value_id is false
 235 CREATE FUNCTION _false_Q(id integer) RETURNS boolean AS $$
 236 BEGIN
 237     RETURN id = 1;
 238 END; $$ LANGUAGE plpgsql;
 239
 240 -- _string_Q:
 241 -- takes a value_id
 242 -- returns the whether value_id is string type
 243 CREATE FUNCTION _string_Q(id integer) RETURNS boolean AS $$
 244 BEGIN
 245     IF (SELECT 1 FROM value WHERE type_id = 5 AND value_id = id) THEN
 246         RETURN NOT _keyword_Q(id);
 247     END IF;
 248     RETURN false;
 249 END; $$ LANGUAGE plpgsql;
 250
 251
 252 -- _valueToString:
 253 -- takes a value_id for a string
 254 -- returns the varchar value of the string
 255 CREATE FUNCTION _valueToString(sid integer) RETURNS varchar AS $$
 256 BEGIN
 257     RETURN (SELECT val_string FROM value WHERE value_id = sid);
 258 END; $$ LANGUAGE plpgsql;
 259
 260 -- _stringish:
 261 -- takes a varchar string
 262 -- returns the value_id of a stringish type (string, symbol, keyword)
 263 CREATE FUNCTION _stringish(str varchar, type integer) RETURNS integer AS $$
 264 DECLARE
 265     result  integer;
 266 BEGIN
 267     -- TODO: share string data between string types
 268     -- lookup if it exists
 269     SELECT value_id FROM value INTO result
 270         WHERE val_string = str AND type_id = type;
 271     IF result IS NULL THEN
 272         -- Create string entry
 273         INSERT INTO value (type_id, val_string)
 274             VALUES (type, str)
 275             RETURNING value_id INTO result;
 276     END IF;
 277     RETURN result;
 278 END; $$ LANGUAGE plpgsql;
 279
 280 -- _stringv:
 281 -- takes a varchar string
 282 -- returns the value_id of a string (new or existing)
 283 CREATE FUNCTION _stringv(str varchar) RETURNS integer AS $$
 284 BEGIN
 285     RETURN _stringish(str, 5);
 286 END; $$ LANGUAGE plpgsql;
 287
 288 -- _keywordv:
 289 -- takes a varchar string
 290 -- returns the value_id of a keyword (new or existing)
 291 CREATE FUNCTION _keywordv(name varchar) RETURNS integer AS $$
 292 BEGIN
 293     RETURN _stringish(chr(CAST(x'29e' AS integer)) || name, 5);
 294 END; $$ LANGUAGE plpgsql;
 295
 296 -- _keyword_Q:
 297 -- takes a value_id
 298 -- returns the whether value_id is keyword type
 299 CREATE FUNCTION _keyword_Q(id integer) RETURNS boolean AS $$
 300 DECLARE
 301     str  varchar;
 302 BEGIN
 303     IF (SELECT 1 FROM value WHERE type_id = 5 AND value_id = id) THEN
 304         str := _valueToString(id);
 305         IF char_length(str) > 0 AND
 306            chr(CAST(x'29e' AS integer)) = substring(str FROM 1 FOR 1) THEN
 307             RETURN true;
 308         END IF;
 309     END IF;
 310     RETURN false;
 311 END; $$ LANGUAGE plpgsql;
 312
 313 -- _symbolv:
 314 -- takes a varchar string
 315 -- returns the value_id of a symbol (new or existing)
 316 CREATE FUNCTION _symbolv(name varchar) RETURNS integer AS $$
 317 BEGIN
 318     RETURN _stringish(name, 7);
 319 END; $$ LANGUAGE plpgsql;
 320
 321 -- _symbol_Q:
 322 -- takes a value_id
 323 -- returns the whether value_id is symbol type
 324 CREATE FUNCTION _symbol_Q(id integer) RETURNS boolean AS $$
 325 BEGIN
 326     RETURN _tf((SELECT 1 FROM value WHERE type_id = 7 AND value_id = id));
 327 END; $$ LANGUAGE plpgsql;
 328
 329 ---- _numToValue:
 330 ---- takes an integer number
 331 ---- returns the value_id for the number
 332 --CREATE FUNCTION _numToValue(num integer) RETURNS integer AS $$
 333 --DECLARE
 334 --    result  integer;
 335 --BEGIN
 336 --    SELECT value_id FROM value INTO result
 337 --        WHERE val_int = CAST(num AS bigint) AND type_id = 3;
 338 --    IF result IS NULL THEN
 339 --        -- Create an integer entry
 340 --        INSERT INTO value (type_id, val_int)
 341 --            VALUES (3, CAST(num AS bigint))
 342 --            RETURNING value_id INTO result;
 343 --    END IF;
 344 --    RETURN result;
 345 --END; $$ LANGUAGE plpgsql;
 346
 347 -- _numToValue:
 348 -- takes an bigint number
 349 -- returns the value_id for the number
 350 CREATE FUNCTION _numToValue(num bigint) RETURNS integer AS $$
 351 DECLARE
 352     result  integer;
 353 BEGIN
 354     SELECT value_id FROM value INTO result
 355         WHERE val_int = num AND type_id = 3;
 356     IF result IS NULL THEN
 357         -- Create an integer entry
 358         INSERT INTO value (type_id, val_int)
 359             VALUES (3, num)
 360             RETURNING value_id INTO result;
 361     END IF;
 362     RETURN result;
 363 END; $$ LANGUAGE plpgsql;
 364
 365 -- ---------------------------------------------------------
 366 -- sequence functions
 367
 368 -- _sequential_Q:
 369 -- return true if obj value_id is a list or vector
 370 CREATE FUNCTION _sequential_Q(obj integer) RETURNS boolean AS $$
 371 BEGIN
 372     RETURN _tf((SELECT 1 FROM value
 373                 WHERE value_id = obj AND (type_id = 8 OR type_id = 9)));
 374 END; $$ LANGUAGE plpgsql;
 375
 376 -- _collection:
 377 -- takes a array of value_id integers
 378 -- returns the value_id of a new list (8), vector (9) or hash-map (10)
 379 CREATE FUNCTION _collection(items integer[], type integer) RETURNS integer AS $$
 380 DECLARE
 381     cid  integer = NULL;
 382     idx  integer;
 383     key  varchar = NULL;
 384     vid  integer;
 385 BEGIN
 386     IF type = 10 AND (array_length(items, 1) % 2) = 1 THEN
 387         RAISE EXCEPTION 'hash-map: odd number of arguments';
 388     END IF;
 389
 390     -- Create value entry pointing to collection (or NULL)
 391     INSERT INTO value (type_id, collection_id)
 392         VALUES (type, COALESCE((SELECT Max(collection_id) FROM value)+1,0))
 393         RETURNING value_id, collection_id INTO vid, cid;
 394
 395     IF array_length(items, 1) > 0 THEN
 396         idx := 1;
 397         LOOP
 398             IF idx > array_length(items, 1) THEN EXIT; END IF;
 399             IF type = 10 THEN -- hash-map
 400                 key := _valueToString(items[idx]);
 401                 idx := idx + 1;
 402             END IF;
 403             -- Create entries
 404             INSERT INTO collection (collection_id, idx, key_string, value_id)
 405                 VALUES (cid, idx-1, key, items[idx]);
 406             idx := idx + 1;
 407         END LOOP;
 408     END IF;
 409     RETURN vid;
 410 END; $$ LANGUAGE plpgsql;
 411
 412 -- _append:
 413 -- return value_id of the collection with new elements appended
 414 CREATE FUNCTION _append(coll integer, items integer[]) RETURNS integer AS $$
 415 DECLARE
 416     type       integer;
 417     cid        integer = NULL;
 418     start_idx  integer;
 419     cur_idx    integer;
 420     key        varchar = NULL;
 421 BEGIN
 422     SELECT type_id, COALESCE(collection_id,
 423                              (SELECT Max(collection_id) FROM value)+1)
 424         FROM value INTO type, cid WHERE value_id = coll;
 425     IF type = 10 AND (array_length(items, 1) % 2) = 1 THEN
 426         RAISE EXCEPTION 'hash-map: odd number of arguments';
 427     END IF;
 428     SELECT COALESCE(Max(idx)+1, 0)
 429         FROM collection INTO start_idx WHERE collection_id = cid;
 430     cur_idx := 1;
 431     LOOP
 432         IF cur_idx > array_length(items, 1) THEN EXIT; END IF;
 433         IF type = 10 THEN -- hash-map
 434             key := _valueToString(items[cur_idx]);
 435             cur_idx := cur_idx + 1;
 436         END IF;
 437         -- Create entries
 438         INSERT INTO collection (collection_id, idx, key_string, value_id)
 439             VALUES (cid, cur_idx+start_idx-1, key, items[cur_idx]);
 440         cur_idx := cur_idx + 1;
 441     END LOOP;
 442     RETURN coll;
 443 END; $$ LANGUAGE plpgsql;
 444
 445
 446 -- _list:
 447 -- takes a array of value_id integers
 448 -- returns the value_id of a new list
 449 CREATE FUNCTION _list(items integer[]) RETURNS integer AS $$
 450 BEGIN
 451     RETURN _collection(items, 8);
 452 END; $$ LANGUAGE plpgsql;
 453
 454 -- _vector:
 455 -- takes a array of value_id integers
 456 -- returns the value_id of a new list
 457 CREATE FUNCTION _vector(items integer[]) RETURNS integer AS $$
 458 BEGIN
 459     RETURN _collection(items, 9);
 460 END; $$ LANGUAGE plpgsql;
 461
 462 -- _list_Q:
 463 -- return true if obj value_id is a list
 464 CREATE FUNCTION _list_Q(obj integer) RETURNS boolean AS $$
 465 BEGIN
 466     RETURN _tf((SELECT 1 FROM value WHERE value_id = obj and type_id = 8));
 467 END; $$ LANGUAGE plpgsql;
 468
 469 -- _vector_Q:
 470 -- return true if obj value_id is a list
 471 CREATE FUNCTION _vector_Q(obj integer) RETURNS boolean AS $$
 472 BEGIN
 473     RETURN _tf((SELECT 1 FROM value WHERE value_id = obj and type_id = 9));
 474 END; $$ LANGUAGE plpgsql;
 475
 476
 477 -- _valueToArray:
 478 -- takes an value_id referring to a list or vector
 479 -- returns an array of the value_ids from the list/vector
 480 CREATE FUNCTION _valueToArray(seq integer) RETURNS integer[] AS $$
 481 BEGIN
 482     RETURN ARRAY(SELECT value_id FROM collection
 483                  WHERE collection_id = (SELECT collection_id FROM value
 484                                         WHERE value_id = seq));
 485 END; $$ LANGUAGE plpgsql;
 486
 487
 488 -- _nth:
 489 -- takes value_id and an index
 490 -- returns the value_id of nth element in list/vector
 491 CREATE FUNCTION _nth(seq_id integer, indx integer) RETURNS integer AS $$
 492 DECLARE
 493     result  integer;
 494 BEGIN
 495     SELECT value_id INTO result FROM collection
 496         WHERE collection_id = (SELECT collection_id FROM value
 497                                WHERE value_id = seq_id)
 498         AND idx = indx;
 499     RETURN result;
 500 END; $$ LANGUAGE plpgsql;
 501
 502 -- _first:
 503 -- takes value_id
 504 -- returns the value_id of first element in list/vector
 505 CREATE FUNCTION _first(seq_id integer) RETURNS integer AS $$
 506 BEGIN
 507     RETURN _nth(seq_id, 0);
 508 END; $$ LANGUAGE plpgsql;
 509
 510
 511 -- _restArray:
 512 -- takes value_id
 513 -- returns the array of value_ids
 514 CREATE FUNCTION _restArray(seq_id integer) RETURNS integer[] AS $$
 515 BEGIN
 516     RETURN ARRAY(SELECT value_id FROM collection
 517                  WHERE collection_id = (SELECT collection_id FROM value
 518                                         WHERE value_id = seq_id)
 519                  AND idx > 0);
 520 END; $$ LANGUAGE plpgsql;
 521
 522 -- _slice:
 523 -- takes value_id, a first index and an last index
 524 -- returns the value_id of new list from first (inclusive) to last (exclusive)
 525 CREATE FUNCTION _slice(seq_id integer, first integer, last integer)
 526 RETURNS integer AS $$
 527 DECLARE
 528     dst_coll_id    integer = NULL;
 529     vid            integer;
 530     i              integer;
 531     result         integer;
 532 BEGIN
 533     FOR vid, i IN (SELECT value_id, idx FROM collection
 534                    WHERE collection_id = (SELECT collection_id FROM value
 535                                           WHERE value_id = seq_id)
 536                    AND idx >= first AND idx < last
 537                    ORDER BY idx)
 538     LOOP
 539         IF dst_coll_id IS NULL THEN
 540             INSERT INTO collection (collection_id, idx, value_id)
 541                 VALUES (COALESCE((SELECT Max(collection_id) FROM collection)+1,0), i-1, vid)
 542                 RETURNING collection_id INTO dst_coll_id;
 543         ELSE
 544             INSERT INTO collection (collection_id, idx, value_id)
 545                 VALUES (dst_coll_id, i-1, vid);
 546         END IF;
 547     END LOOP;
 548     INSERT INTO value (type_id, collection_id)
 549         VALUES (8, dst_coll_id)
 550         RETURNING value_id INTO result;
 551     RETURN result;
 552 END; $$ LANGUAGE plpgsql;
 553
 554 -- _rest:
 555 -- takes value_id
 556 -- returns the value_id of new list
 557 CREATE FUNCTION _rest(seq_id integer) RETURNS integer AS $$
 558 BEGIN
 559     RETURN _slice(seq_id, 1, _count(seq_id));
 560 END; $$ LANGUAGE plpgsql;
 561
 562 -- _count:
 563 -- takes value_id
 564 -- returns a count (not value_id)
 565 CREATE FUNCTION _count(seq_id integer) RETURNS integer AS $$
 566 DECLARE
 567     result  integer;
 568 BEGIN
 569     SELECT count(*) INTO result FROM collection
 570         WHERE collection_id = (SELECT collection_id FROM value
 571                                WHERE value_id = seq_id);
 572     RETURN result;
 573 END; $$ LANGUAGE plpgsql;
 574
 575
 576 -- ---------------------------------------------------------
 577 -- hash-map functions
 578
 579 -- _hash_map:
 580 -- return value_id of a new hash-map
 581 CREATE FUNCTION _hash_map(items integer[]) RETURNS integer AS $$
 582 BEGIN
 583     RETURN _collection(items, 10);
 584 END; $$ LANGUAGE plpgsql;
 585
 586 -- _hash_map_Q:
 587 -- return true if obj value_id is a list
 588 CREATE FUNCTION _hash_map_Q(obj integer) RETURNS boolean AS $$
 589 BEGIN
 590     RETURN _tf((SELECT 1 FROM value WHERE value_id = obj and type_id = 10));
 591 END; $$ LANGUAGE plpgsql;
 592
 593 -- _dissoc:
 594 -- return value_id of the hash-map with elements removed
 595 CREATE FUNCTION _dissoc(hm integer, items integer[]) RETURNS integer AS $$
 596 DECLARE
 597     cid        integer = NULL;
 598 BEGIN
 599     SELECT collection_id FROM value INTO cid WHERE value_id = hm;
 600     FOR i IN 1 .. array_length(items, 1)
 601     LOOP
 602         -- Delete matching entries
 603         DELETE FROM collection
 604             WHERE collection_id = cid
 605             AND key_string = _valueToString(items[i]);
 606     END LOOP;
 607     RETURN hm;
 608 END; $$ LANGUAGE plpgsql;
 609
 610 -- _get:
 611 -- return value_id of the hash-map entry matching key
 612 CREATE FUNCTION _get(hm integer, key varchar) RETURNS integer AS $$
 613 BEGIN
 614     RETURN (SELECT value_id FROM collection
 615             WHERE collection_id = (SELECT collection_id FROM value
 616                                    WHERE value_id = hm)
 617             AND key_string = key);
 618 END; $$ LANGUAGE plpgsql;
 619
 620 -- _contains_Q:
 621 -- return true if hash-map contains entry matching key
 622 CREATE FUNCTION _contains_Q(hm integer, key varchar) RETURNS boolean AS $$
 623 BEGIN
 624     RETURN _tf((SELECT 1 FROM collection
 625                 WHERE collection_id = (SELECT collection_id FROM value
 626                                        WHERE value_id = hm)
 627                 AND key_string = key));
 628 END; $$ LANGUAGE plpgsql;
 629
 630 -- _keys:
 631 -- return array of key value_ids from hash-map
 632 CREATE FUNCTION _keys(hm integer) RETURNS integer[] AS $$
 633 BEGIN
 634     RETURN ARRAY(SELECT _stringv(key_string) FROM collection
 635                  WHERE collection_id = (SELECT collection_id FROM value
 636                                         WHERE value_id = hm));
 637 END; $$ LANGUAGE plpgsql;
 638
 639 -- _vals:
 640 -- return array of value value_ids from hash-map
 641 CREATE FUNCTION _vals(hm integer) RETURNS integer[] AS $$
 642 BEGIN
 643     RETURN ARRAY(SELECT value_id FROM collection
 644                  WHERE collection_id = (SELECT collection_id FROM value
 645                                         WHERE value_id = hm));
 646 END; $$ LANGUAGE plpgsql;
 647
 648
 649 -- ---------------------------------------------------------
 650 -- function functions
 651
 652 -- _function:
 653 -- takes a ast value_id, params value_id and env_id
 654 -- returns the value_id of a new function
 655 CREATE FUNCTION _function(ast integer, params integer, env integer)
 656 RETURNS integer AS $$
 657 DECLARE
 658     cid     integer = NULL;
 659     result  integer;
 660 BEGIN
 661     -- Create function entry
 662     INSERT INTO collection (collection_id, value_id, params_id, env_id)
 663         VALUES (COALESCE((SELECT Max(collection_id) FROM collection)+1,0),
 664                 ast, params, env)
 665         RETURNING collection_id INTO cid;
 666     INSERT INTO value (type_id, collection_id)
 667         VALUES (12, cid)
 668         RETURNING value_id into result;
 669     RETURN result;
 670 END; $$ LANGUAGE plpgsql;
 671
 672 -- _macro:
 673 CREATE FUNCTION _macro(func integer) RETURNS integer AS $$
 674 DECLARE
 675     newfunc  integer;
 676     cid      integer;
 677 BEGIN
 678     newfunc := _clone(func);
 679     SELECT collection_id FROM value INTO cid WHERE value_id = newfunc;
 680     UPDATE collection SET macro = true
 681         WHERE collection_id = cid;
 682     RETURN newfunc;
 683 END; $$ LANGUAGE plpgsql;
 684
 685 CREATE FUNCTION _apply(func integer, args integer[]) RETURNS integer AS $$
 686 DECLARE
 687     type     integer;
 688     fcid     integer;
 689     fname    varchar;
 690     fast     integer;
 691     fparams  integer;
 692     fenv     integer;
 693     result   integer;
 694 BEGIN
 695     SELECT type_id, collection_id, function_name
 696         INTO type, fcid, fname
 697         FROM value WHERE value_id = func;
 698     IF type = 11 THEN
 699         EXECUTE format('SELECT %s($1);', fname)
 700             INTO result USING args;
 701         RETURN result;
 702     ELSIF type = 12 THEN
 703         SELECT value_id, params_id, env_id
 704             INTO fast, fparams, fenv
 705             FROM collection
 706             WHERE collection_id = fcid;
 707         -- NOTE: forward reference to current step EVAL function
 708         RETURN EVAL(fast, env_new_bindings(fenv, fparams, args));
 709     ELSE
 710         RAISE EXCEPTION 'Invalid function call';
 711     END IF;
 712 END; $$ LANGUAGE plpgsql;
 713
 714 -- ---------------------------------------------------------
 715 -- atom functions
 716
 717 -- _atom:
 718 -- takes an ast value_id
 719 -- returns a new atom value_id
 720 CREATE FUNCTION _atom(val integer) RETURNS integer AS $$
 721 DECLARE
 722     cid     integer = NULL;
 723     result  integer;
 724 BEGIN
 725     -- Create function entry
 726     INSERT INTO collection (collection_id, value_id)
 727         VALUES (COALESCE((SELECT Max(collection_id) FROM collection)+1,0), val)
 728         RETURNING collection_id INTO cid;
 729     INSERT INTO value (type_id, collection_id) VALUES (13, cid)
 730         RETURNING value_id INTO result;
 731     RETURN result;
 732 END; $$ LANGUAGE plpgsql;
 733
 734 -- _atom_Q:
 735 -- takes a value_id
 736 -- returns the whether value_id is an atom
 737 CREATE FUNCTION _atom_Q(id integer) RETURNS boolean AS $$
 738 BEGIN
 739     RETURN EXISTS(SELECT 1 FROM value WHERE type_id = 13 AND value_id = id);
 740 END; $$ LANGUAGE plpgsql;
 741
 742 -- _deref:
 743 -- takes an atom value_id
 744 -- returns a atom value value_id
 745 CREATE FUNCTION _deref(atm integer) RETURNS integer AS $$
 746 DECLARE
 747     result  integer;
 748 BEGIN
 749     SELECT value_id INTO result FROM collection
 750         WHERE collection_id = (SELECT collection_id FROM value
 751                                WHERE value_id = atm);
 752     RETURN result;
 753 END; $$ LANGUAGE plpgsql;
 754
 755 -- _reset_BANG:
 756 -- takes an atom value_id and new value value_id
 757 -- returns a new value value_id
 758 CREATE FUNCTION _reset_BANG(atm integer, newval integer) RETURNS integer AS $$
 759 BEGIN
 760     UPDATE collection SET value_id = newval
 761         WHERE collection_id = (SELECT collection_id FROM value
 762                                WHERE value_id = atm);
 763     RETURN newval;
 764 END; $$ LANGUAGE plpgsql;