1/* contrib/hstore/hstore--1.4.sql */ 2 3-- complain if script is sourced in psql, rather than via CREATE EXTENSION 4\echo Use "CREATE EXTENSION hstore" to load this file. \quit 5 6CREATE TYPE hstore; 7 8CREATE FUNCTION hstore_in(cstring) 9RETURNS hstore 10AS 'MODULE_PATHNAME' 11LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 12 13CREATE FUNCTION hstore_out(hstore) 14RETURNS cstring 15AS 'MODULE_PATHNAME' 16LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 17 18CREATE FUNCTION hstore_recv(internal) 19RETURNS hstore 20AS 'MODULE_PATHNAME' 21LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 22 23CREATE FUNCTION hstore_send(hstore) 24RETURNS bytea 25AS 'MODULE_PATHNAME' 26LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 27 28CREATE TYPE hstore ( 29 INTERNALLENGTH = -1, 30 INPUT = hstore_in, 31 OUTPUT = hstore_out, 32 RECEIVE = hstore_recv, 33 SEND = hstore_send, 34 STORAGE = extended 35); 36 37CREATE FUNCTION hstore_version_diag(hstore) 38RETURNS integer 39AS 'MODULE_PATHNAME','hstore_version_diag' 40LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 41 42CREATE FUNCTION fetchval(hstore,text) 43RETURNS text 44AS 'MODULE_PATHNAME','hstore_fetchval' 45LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 46 47CREATE OPERATOR -> ( 48 LEFTARG = hstore, 49 RIGHTARG = text, 50 PROCEDURE = fetchval 51); 52 53CREATE FUNCTION slice_array(hstore,text[]) 54RETURNS text[] 55AS 'MODULE_PATHNAME','hstore_slice_to_array' 56LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 57 58CREATE OPERATOR -> ( 59 LEFTARG = hstore, 60 RIGHTARG = text[], 61 PROCEDURE = slice_array 62); 63 64CREATE FUNCTION slice(hstore,text[]) 65RETURNS hstore 66AS 'MODULE_PATHNAME','hstore_slice_to_hstore' 67LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 68 69CREATE FUNCTION isexists(hstore,text) 70RETURNS bool 71AS 'MODULE_PATHNAME','hstore_exists' 72LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 73 74CREATE FUNCTION exist(hstore,text) 75RETURNS bool 76AS 'MODULE_PATHNAME','hstore_exists' 77LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 78 79CREATE OPERATOR ? ( 80 LEFTARG = hstore, 81 RIGHTARG = text, 82 PROCEDURE = exist, 83 RESTRICT = contsel, 84 JOIN = contjoinsel 85); 86 87CREATE FUNCTION exists_any(hstore,text[]) 88RETURNS bool 89AS 'MODULE_PATHNAME','hstore_exists_any' 90LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 91 92CREATE OPERATOR ?| ( 93 LEFTARG = hstore, 94 RIGHTARG = text[], 95 PROCEDURE = exists_any, 96 RESTRICT = contsel, 97 JOIN = contjoinsel 98); 99 100CREATE FUNCTION exists_all(hstore,text[]) 101RETURNS bool 102AS 'MODULE_PATHNAME','hstore_exists_all' 103LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 104 105CREATE OPERATOR ?& ( 106 LEFTARG = hstore, 107 RIGHTARG = text[], 108 PROCEDURE = exists_all, 109 RESTRICT = contsel, 110 JOIN = contjoinsel 111); 112 113CREATE FUNCTION isdefined(hstore,text) 114RETURNS bool 115AS 'MODULE_PATHNAME','hstore_defined' 116LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 117 118CREATE FUNCTION defined(hstore,text) 119RETURNS bool 120AS 'MODULE_PATHNAME','hstore_defined' 121LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 122 123CREATE FUNCTION delete(hstore,text) 124RETURNS hstore 125AS 'MODULE_PATHNAME','hstore_delete' 126LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 127 128CREATE FUNCTION delete(hstore,text[]) 129RETURNS hstore 130AS 'MODULE_PATHNAME','hstore_delete_array' 131LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 132 133CREATE FUNCTION delete(hstore,hstore) 134RETURNS hstore 135AS 'MODULE_PATHNAME','hstore_delete_hstore' 136LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 137 138CREATE OPERATOR - ( 139 LEFTARG = hstore, 140 RIGHTARG = text, 141 PROCEDURE = delete 142); 143 144CREATE OPERATOR - ( 145 LEFTARG = hstore, 146 RIGHTARG = text[], 147 PROCEDURE = delete 148); 149 150CREATE OPERATOR - ( 151 LEFTARG = hstore, 152 RIGHTARG = hstore, 153 PROCEDURE = delete 154); 155 156CREATE FUNCTION hs_concat(hstore,hstore) 157RETURNS hstore 158AS 'MODULE_PATHNAME','hstore_concat' 159LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 160 161CREATE OPERATOR || ( 162 LEFTARG = hstore, 163 RIGHTARG = hstore, 164 PROCEDURE = hs_concat 165); 166 167CREATE FUNCTION hs_contains(hstore,hstore) 168RETURNS bool 169AS 'MODULE_PATHNAME','hstore_contains' 170LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 171 172CREATE FUNCTION hs_contained(hstore,hstore) 173RETURNS bool 174AS 'MODULE_PATHNAME','hstore_contained' 175LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 176 177CREATE OPERATOR @> ( 178 LEFTARG = hstore, 179 RIGHTARG = hstore, 180 PROCEDURE = hs_contains, 181 COMMUTATOR = '<@', 182 RESTRICT = contsel, 183 JOIN = contjoinsel 184); 185 186CREATE OPERATOR <@ ( 187 LEFTARG = hstore, 188 RIGHTARG = hstore, 189 PROCEDURE = hs_contained, 190 COMMUTATOR = '@>', 191 RESTRICT = contsel, 192 JOIN = contjoinsel 193); 194 195-- obsolete: 196CREATE OPERATOR @ ( 197 LEFTARG = hstore, 198 RIGHTARG = hstore, 199 PROCEDURE = hs_contains, 200 COMMUTATOR = '~', 201 RESTRICT = contsel, 202 JOIN = contjoinsel 203); 204 205CREATE OPERATOR ~ ( 206 LEFTARG = hstore, 207 RIGHTARG = hstore, 208 PROCEDURE = hs_contained, 209 COMMUTATOR = '@', 210 RESTRICT = contsel, 211 JOIN = contjoinsel 212); 213 214CREATE FUNCTION tconvert(text,text) 215RETURNS hstore 216AS 'MODULE_PATHNAME','hstore_from_text' 217LANGUAGE C IMMUTABLE PARALLEL SAFE; -- not STRICT; needs to allow (key,NULL) 218 219CREATE FUNCTION hstore(text,text) 220RETURNS hstore 221AS 'MODULE_PATHNAME','hstore_from_text' 222LANGUAGE C IMMUTABLE PARALLEL SAFE; -- not STRICT; needs to allow (key,NULL) 223 224CREATE FUNCTION hstore(text[],text[]) 225RETURNS hstore 226AS 'MODULE_PATHNAME', 'hstore_from_arrays' 227LANGUAGE C IMMUTABLE PARALLEL SAFE; -- not STRICT; allows (keys,null) 228 229CREATE FUNCTION hstore(text[]) 230RETURNS hstore 231AS 'MODULE_PATHNAME', 'hstore_from_array' 232LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 233 234CREATE CAST (text[] AS hstore) 235 WITH FUNCTION hstore(text[]); 236 237CREATE FUNCTION hstore_to_json(hstore) 238RETURNS json 239AS 'MODULE_PATHNAME', 'hstore_to_json' 240LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 241 242CREATE CAST (hstore AS json) 243 WITH FUNCTION hstore_to_json(hstore); 244 245CREATE FUNCTION hstore_to_json_loose(hstore) 246RETURNS json 247AS 'MODULE_PATHNAME', 'hstore_to_json_loose' 248LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 249 250CREATE FUNCTION hstore_to_jsonb(hstore) 251RETURNS jsonb 252AS 'MODULE_PATHNAME', 'hstore_to_jsonb' 253LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 254 255CREATE CAST (hstore AS jsonb) 256 WITH FUNCTION hstore_to_jsonb(hstore); 257 258CREATE FUNCTION hstore_to_jsonb_loose(hstore) 259RETURNS jsonb 260AS 'MODULE_PATHNAME', 'hstore_to_jsonb_loose' 261LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 262 263CREATE FUNCTION hstore(record) 264RETURNS hstore 265AS 'MODULE_PATHNAME', 'hstore_from_record' 266LANGUAGE C IMMUTABLE PARALLEL SAFE; -- not STRICT; allows (null::recordtype) 267 268CREATE FUNCTION hstore_to_array(hstore) 269RETURNS text[] 270AS 'MODULE_PATHNAME','hstore_to_array' 271LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 272 273CREATE OPERATOR %% ( 274 RIGHTARG = hstore, 275 PROCEDURE = hstore_to_array 276); 277 278CREATE FUNCTION hstore_to_matrix(hstore) 279RETURNS text[] 280AS 'MODULE_PATHNAME','hstore_to_matrix' 281LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 282 283CREATE OPERATOR %# ( 284 RIGHTARG = hstore, 285 PROCEDURE = hstore_to_matrix 286); 287 288CREATE FUNCTION akeys(hstore) 289RETURNS text[] 290AS 'MODULE_PATHNAME','hstore_akeys' 291LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 292 293CREATE FUNCTION avals(hstore) 294RETURNS text[] 295AS 'MODULE_PATHNAME','hstore_avals' 296LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 297 298CREATE FUNCTION skeys(hstore) 299RETURNS setof text 300AS 'MODULE_PATHNAME','hstore_skeys' 301LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 302 303CREATE FUNCTION svals(hstore) 304RETURNS setof text 305AS 'MODULE_PATHNAME','hstore_svals' 306LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 307 308CREATE FUNCTION each(IN hs hstore, 309 OUT key text, 310 OUT value text) 311RETURNS SETOF record 312AS 'MODULE_PATHNAME','hstore_each' 313LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 314 315CREATE FUNCTION populate_record(anyelement,hstore) 316RETURNS anyelement 317AS 'MODULE_PATHNAME', 'hstore_populate_record' 318LANGUAGE C IMMUTABLE PARALLEL SAFE; -- not STRICT; allows (null::rectype,hstore) 319 320CREATE OPERATOR #= ( 321 LEFTARG = anyelement, 322 RIGHTARG = hstore, 323 PROCEDURE = populate_record 324); 325 326-- btree support 327 328CREATE FUNCTION hstore_eq(hstore,hstore) 329RETURNS boolean 330AS 'MODULE_PATHNAME','hstore_eq' 331LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 332 333CREATE FUNCTION hstore_ne(hstore,hstore) 334RETURNS boolean 335AS 'MODULE_PATHNAME','hstore_ne' 336LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 337 338CREATE FUNCTION hstore_gt(hstore,hstore) 339RETURNS boolean 340AS 'MODULE_PATHNAME','hstore_gt' 341LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 342 343CREATE FUNCTION hstore_ge(hstore,hstore) 344RETURNS boolean 345AS 'MODULE_PATHNAME','hstore_ge' 346LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 347 348CREATE FUNCTION hstore_lt(hstore,hstore) 349RETURNS boolean 350AS 'MODULE_PATHNAME','hstore_lt' 351LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 352 353CREATE FUNCTION hstore_le(hstore,hstore) 354RETURNS boolean 355AS 'MODULE_PATHNAME','hstore_le' 356LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 357 358CREATE FUNCTION hstore_cmp(hstore,hstore) 359RETURNS integer 360AS 'MODULE_PATHNAME','hstore_cmp' 361LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 362 363CREATE OPERATOR = ( 364 LEFTARG = hstore, 365 RIGHTARG = hstore, 366 PROCEDURE = hstore_eq, 367 COMMUTATOR = =, 368 NEGATOR = <>, 369 RESTRICT = eqsel, 370 JOIN = eqjoinsel, 371 MERGES, 372 HASHES 373); 374CREATE OPERATOR <> ( 375 LEFTARG = hstore, 376 RIGHTARG = hstore, 377 PROCEDURE = hstore_ne, 378 COMMUTATOR = <>, 379 NEGATOR = =, 380 RESTRICT = neqsel, 381 JOIN = neqjoinsel 382); 383 384-- the comparison operators have funky names (and are undocumented) 385-- in an attempt to discourage anyone from actually using them. they 386-- only exist to support the btree opclass 387 388CREATE OPERATOR #<# ( 389 LEFTARG = hstore, 390 RIGHTARG = hstore, 391 PROCEDURE = hstore_lt, 392 COMMUTATOR = #>#, 393 NEGATOR = #>=#, 394 RESTRICT = scalarltsel, 395 JOIN = scalarltjoinsel 396); 397CREATE OPERATOR #<=# ( 398 LEFTARG = hstore, 399 RIGHTARG = hstore, 400 PROCEDURE = hstore_le, 401 COMMUTATOR = #>=#, 402 NEGATOR = #>#, 403 RESTRICT = scalarltsel, 404 JOIN = scalarltjoinsel 405); 406CREATE OPERATOR #># ( 407 LEFTARG = hstore, 408 RIGHTARG = hstore, 409 PROCEDURE = hstore_gt, 410 COMMUTATOR = #<#, 411 NEGATOR = #<=#, 412 RESTRICT = scalargtsel, 413 JOIN = scalargtjoinsel 414); 415CREATE OPERATOR #>=# ( 416 LEFTARG = hstore, 417 RIGHTARG = hstore, 418 PROCEDURE = hstore_ge, 419 COMMUTATOR = #<=#, 420 NEGATOR = #<#, 421 RESTRICT = scalargtsel, 422 JOIN = scalargtjoinsel 423); 424 425CREATE OPERATOR CLASS btree_hstore_ops 426DEFAULT FOR TYPE hstore USING btree 427AS 428 OPERATOR 1 #<# , 429 OPERATOR 2 #<=# , 430 OPERATOR 3 = , 431 OPERATOR 4 #>=# , 432 OPERATOR 5 #># , 433 FUNCTION 1 hstore_cmp(hstore,hstore); 434 435-- hash support 436 437CREATE FUNCTION hstore_hash(hstore) 438RETURNS integer 439AS 'MODULE_PATHNAME','hstore_hash' 440LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 441 442CREATE OPERATOR CLASS hash_hstore_ops 443DEFAULT FOR TYPE hstore USING hash 444AS 445 OPERATOR 1 = , 446 FUNCTION 1 hstore_hash(hstore); 447 448-- GiST support 449 450CREATE TYPE ghstore; 451 452CREATE FUNCTION ghstore_in(cstring) 453RETURNS ghstore 454AS 'MODULE_PATHNAME' 455LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 456 457CREATE FUNCTION ghstore_out(ghstore) 458RETURNS cstring 459AS 'MODULE_PATHNAME' 460LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 461 462CREATE TYPE ghstore ( 463 INTERNALLENGTH = -1, 464 INPUT = ghstore_in, 465 OUTPUT = ghstore_out 466); 467 468CREATE FUNCTION ghstore_compress(internal) 469RETURNS internal 470AS 'MODULE_PATHNAME' 471LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 472 473CREATE FUNCTION ghstore_decompress(internal) 474RETURNS internal 475AS 'MODULE_PATHNAME' 476LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 477 478CREATE FUNCTION ghstore_penalty(internal,internal,internal) 479RETURNS internal 480AS 'MODULE_PATHNAME' 481LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 482 483CREATE FUNCTION ghstore_picksplit(internal, internal) 484RETURNS internal 485AS 'MODULE_PATHNAME' 486LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 487 488CREATE FUNCTION ghstore_union(internal, internal) 489RETURNS ghstore 490AS 'MODULE_PATHNAME' 491LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 492 493CREATE FUNCTION ghstore_same(ghstore, ghstore, internal) 494RETURNS internal 495AS 'MODULE_PATHNAME' 496LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 497 498CREATE FUNCTION ghstore_consistent(internal,hstore,smallint,oid,internal) 499RETURNS bool 500AS 'MODULE_PATHNAME' 501LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 502 503CREATE OPERATOR CLASS gist_hstore_ops 504DEFAULT FOR TYPE hstore USING gist 505AS 506 OPERATOR 7 @> , 507 OPERATOR 9 ?(hstore,text) , 508 OPERATOR 10 ?|(hstore,text[]) , 509 OPERATOR 11 ?&(hstore,text[]) , 510 --OPERATOR 8 <@ , 511 OPERATOR 13 @ , 512 --OPERATOR 14 ~ , 513 FUNCTION 1 ghstore_consistent (internal, hstore, smallint, oid, internal), 514 FUNCTION 2 ghstore_union (internal, internal), 515 FUNCTION 3 ghstore_compress (internal), 516 FUNCTION 4 ghstore_decompress (internal), 517 FUNCTION 5 ghstore_penalty (internal, internal, internal), 518 FUNCTION 6 ghstore_picksplit (internal, internal), 519 FUNCTION 7 ghstore_same (ghstore, ghstore, internal), 520 STORAGE ghstore; 521 522-- GIN support 523 524CREATE FUNCTION gin_extract_hstore(hstore, internal) 525RETURNS internal 526AS 'MODULE_PATHNAME' 527LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 528 529CREATE FUNCTION gin_extract_hstore_query(hstore, internal, int2, internal, internal) 530RETURNS internal 531AS 'MODULE_PATHNAME' 532LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 533 534CREATE FUNCTION gin_consistent_hstore(internal, int2, hstore, int4, internal, internal) 535RETURNS bool 536AS 'MODULE_PATHNAME' 537LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 538 539CREATE OPERATOR CLASS gin_hstore_ops 540DEFAULT FOR TYPE hstore USING gin 541AS 542 OPERATOR 7 @>, 543 OPERATOR 9 ?(hstore,text), 544 OPERATOR 10 ?|(hstore,text[]), 545 OPERATOR 11 ?&(hstore,text[]), 546 FUNCTION 1 bttextcmp(text,text), 547 FUNCTION 2 gin_extract_hstore(hstore, internal), 548 FUNCTION 3 gin_extract_hstore_query(hstore, internal, int2, internal, internal), 549 FUNCTION 4 gin_consistent_hstore(internal, int2, hstore, int4, internal, internal), 550 STORAGE text; 551