codekingpro/portable-devtools

1/* contrib/hstore/hstore--1.4.sql */2 3-- complain if script is sourced in psql, rather than via CREATE EXTENSION4\echo Use "CREATE EXTENSION hstore" to load this file. \quit5 6CREATE TYPE hstore;7 8CREATE FUNCTION hstore_in(cstring)9RETURNS hstore10AS 'MODULE_PATHNAME'11LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;12 13CREATE FUNCTION hstore_out(hstore)14RETURNS cstring15AS 'MODULE_PATHNAME'16LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;17 18CREATE FUNCTION hstore_recv(internal)19RETURNS hstore20AS 'MODULE_PATHNAME'21LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;22 23CREATE FUNCTION hstore_send(hstore)24RETURNS bytea25AS '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 = extended35);36 37CREATE FUNCTION hstore_version_diag(hstore)38RETURNS integer39AS 'MODULE_PATHNAME','hstore_version_diag'40LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;41 42CREATE FUNCTION fetchval(hstore,text)43RETURNS text44AS 'MODULE_PATHNAME','hstore_fetchval'45LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;46 47CREATE OPERATOR -> (48	LEFTARG = hstore,49	RIGHTARG = text,50	PROCEDURE = fetchval51);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_array62);63 64CREATE FUNCTION slice(hstore,text[])65RETURNS hstore66AS 'MODULE_PATHNAME','hstore_slice_to_hstore'67LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;68 69CREATE FUNCTION isexists(hstore,text)70RETURNS bool71AS 'MODULE_PATHNAME','hstore_exists'72LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;73 74CREATE FUNCTION exist(hstore,text)75RETURNS bool76AS '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 = contjoinsel85);86 87CREATE FUNCTION exists_any(hstore,text[])88RETURNS bool89AS '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 = contjoinsel98);99 100CREATE FUNCTION exists_all(hstore,text[])101RETURNS bool102AS '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 = contjoinsel111);112 113CREATE FUNCTION isdefined(hstore,text)114RETURNS bool115AS 'MODULE_PATHNAME','hstore_defined'116LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;117 118CREATE FUNCTION defined(hstore,text)119RETURNS bool120AS 'MODULE_PATHNAME','hstore_defined'121LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;122 123CREATE FUNCTION delete(hstore,text)124RETURNS hstore125AS 'MODULE_PATHNAME','hstore_delete'126LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;127 128CREATE FUNCTION delete(hstore,text[])129RETURNS hstore130AS 'MODULE_PATHNAME','hstore_delete_array'131LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;132 133CREATE FUNCTION delete(hstore,hstore)134RETURNS hstore135AS 'MODULE_PATHNAME','hstore_delete_hstore'136LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;137 138CREATE OPERATOR - (139	LEFTARG = hstore,140	RIGHTARG = text,141	PROCEDURE = delete142);143 144CREATE OPERATOR - (145	LEFTARG = hstore,146	RIGHTARG = text[],147	PROCEDURE = delete148);149 150CREATE OPERATOR - (151	LEFTARG = hstore,152	RIGHTARG = hstore,153	PROCEDURE = delete154);155 156CREATE FUNCTION hs_concat(hstore,hstore)157RETURNS hstore158AS 'MODULE_PATHNAME','hstore_concat'159LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;160 161CREATE OPERATOR || (162	LEFTARG = hstore,163	RIGHTARG = hstore,164	PROCEDURE = hs_concat165);166 167CREATE FUNCTION hs_contains(hstore,hstore)168RETURNS bool169AS 'MODULE_PATHNAME','hstore_contains'170LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;171 172CREATE FUNCTION hs_contained(hstore,hstore)173RETURNS bool174AS '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 = contjoinsel184);185 186CREATE OPERATOR <@ (187	LEFTARG = hstore,188	RIGHTARG = hstore,189	PROCEDURE = hs_contained,190	COMMUTATOR = '@>',191	RESTRICT = contsel,192	JOIN = contjoinsel193);194 195-- obsolete:196CREATE OPERATOR @ (197	LEFTARG = hstore,198	RIGHTARG = hstore,199	PROCEDURE = hs_contains,200	COMMUTATOR = '~',201	RESTRICT = contsel,202	JOIN = contjoinsel203);204 205CREATE OPERATOR ~ (206	LEFTARG = hstore,207	RIGHTARG = hstore,208	PROCEDURE = hs_contained,209	COMMUTATOR = '@',210	RESTRICT = contsel,211	JOIN = contjoinsel212);213 214CREATE FUNCTION tconvert(text,text)215RETURNS hstore216AS 'MODULE_PATHNAME','hstore_from_text'217LANGUAGE C IMMUTABLE PARALLEL SAFE; -- not STRICT; needs to allow (key,NULL)218 219CREATE FUNCTION hstore(text,text)220RETURNS hstore221AS 'MODULE_PATHNAME','hstore_from_text'222LANGUAGE C IMMUTABLE PARALLEL SAFE; -- not STRICT; needs to allow (key,NULL)223 224CREATE FUNCTION hstore(text[],text[])225RETURNS hstore226AS 'MODULE_PATHNAME', 'hstore_from_arrays'227LANGUAGE C IMMUTABLE PARALLEL SAFE; -- not STRICT; allows (keys,null)228 229CREATE FUNCTION hstore(text[])230RETURNS hstore231AS '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 json239AS '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 json247AS 'MODULE_PATHNAME', 'hstore_to_json_loose'248LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;249 250CREATE FUNCTION hstore_to_jsonb(hstore)251RETURNS jsonb252AS '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 jsonb260AS 'MODULE_PATHNAME', 'hstore_to_jsonb_loose'261LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;262 263CREATE FUNCTION hstore(record)264RETURNS hstore265AS '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_array276);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_matrix286);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 text300AS 'MODULE_PATHNAME','hstore_skeys'301LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;302 303CREATE FUNCTION svals(hstore)304RETURNS setof text305AS '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 record312AS 'MODULE_PATHNAME','hstore_each'313LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;314 315CREATE FUNCTION populate_record(anyelement,hstore)316RETURNS anyelement317AS '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_record324);325 326-- btree support327 328CREATE FUNCTION hstore_eq(hstore,hstore)329RETURNS boolean330AS 'MODULE_PATHNAME','hstore_eq'331LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;332 333CREATE FUNCTION hstore_ne(hstore,hstore)334RETURNS boolean335AS 'MODULE_PATHNAME','hstore_ne'336LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;337 338CREATE FUNCTION hstore_gt(hstore,hstore)339RETURNS boolean340AS 'MODULE_PATHNAME','hstore_gt'341LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;342 343CREATE FUNCTION hstore_ge(hstore,hstore)344RETURNS boolean345AS 'MODULE_PATHNAME','hstore_ge'346LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;347 348CREATE FUNCTION hstore_lt(hstore,hstore)349RETURNS boolean350AS 'MODULE_PATHNAME','hstore_lt'351LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;352 353CREATE FUNCTION hstore_le(hstore,hstore)354RETURNS boolean355AS 'MODULE_PATHNAME','hstore_le'356LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;357 358CREATE FUNCTION hstore_cmp(hstore,hstore)359RETURNS integer360AS '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       HASHES373);374CREATE OPERATOR <> (375       LEFTARG = hstore,376       RIGHTARG = hstore,377       PROCEDURE = hstore_ne,378       COMMUTATOR = <>,379       NEGATOR = =,380       RESTRICT = neqsel,381       JOIN = neqjoinsel382);383 384-- the comparison operators have funky names (and are undocumented)385-- in an attempt to discourage anyone from actually using them. they386-- only exist to support the btree opclass387 388CREATE OPERATOR #<# (389       LEFTARG = hstore,390       RIGHTARG = hstore,391       PROCEDURE = hstore_lt,392       COMMUTATOR = #>#,393       NEGATOR = #>=#,394       RESTRICT = scalarltsel,395       JOIN = scalarltjoinsel396);397CREATE OPERATOR #<=# (398       LEFTARG = hstore,399       RIGHTARG = hstore,400       PROCEDURE = hstore_le,401       COMMUTATOR = #>=#,402       NEGATOR = #>#,403       RESTRICT = scalarltsel,404       JOIN = scalarltjoinsel405);406CREATE OPERATOR #># (407       LEFTARG = hstore,408       RIGHTARG = hstore,409       PROCEDURE = hstore_gt,410       COMMUTATOR = #<#,411       NEGATOR = #<=#,412       RESTRICT = scalargtsel,413       JOIN = scalargtjoinsel414);415CREATE OPERATOR #>=# (416       LEFTARG = hstore,417       RIGHTARG = hstore,418       PROCEDURE = hstore_ge,419       COMMUTATOR = #<=#,420       NEGATOR = #<#,421       RESTRICT = scalargtsel,422       JOIN = scalargtjoinsel423);424 425CREATE OPERATOR CLASS btree_hstore_ops426DEFAULT FOR TYPE hstore USING btree427AS428	OPERATOR	1	#<# ,429	OPERATOR	2	#<=# ,430	OPERATOR	3	= ,431	OPERATOR	4	#>=# ,432	OPERATOR	5	#># ,433	FUNCTION	1	hstore_cmp(hstore,hstore);434 435-- hash support436 437CREATE FUNCTION hstore_hash(hstore)438RETURNS integer439AS 'MODULE_PATHNAME','hstore_hash'440LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;441 442CREATE OPERATOR CLASS hash_hstore_ops443DEFAULT FOR TYPE hstore USING hash444AS445	OPERATOR	1	= ,446	FUNCTION	1	hstore_hash(hstore);447 448-- GiST support449 450CREATE TYPE ghstore;451 452CREATE FUNCTION ghstore_in(cstring)453RETURNS ghstore454AS 'MODULE_PATHNAME'455LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;456 457CREATE FUNCTION ghstore_out(ghstore)458RETURNS cstring459AS 'MODULE_PATHNAME'460LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;461 462CREATE TYPE ghstore (463        INTERNALLENGTH = -1,464        INPUT = ghstore_in,465        OUTPUT = ghstore_out466);467 468CREATE FUNCTION ghstore_compress(internal)469RETURNS internal470AS 'MODULE_PATHNAME'471LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;472 473CREATE FUNCTION ghstore_decompress(internal)474RETURNS internal475AS 'MODULE_PATHNAME'476LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;477 478CREATE FUNCTION ghstore_penalty(internal,internal,internal)479RETURNS internal480AS 'MODULE_PATHNAME'481LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;482 483CREATE FUNCTION ghstore_picksplit(internal, internal)484RETURNS internal485AS 'MODULE_PATHNAME'486LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;487 488CREATE FUNCTION ghstore_union(internal, internal)489RETURNS ghstore490AS 'MODULE_PATHNAME'491LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;492 493CREATE FUNCTION ghstore_same(ghstore, ghstore, internal)494RETURNS internal495AS 'MODULE_PATHNAME'496LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;497 498CREATE FUNCTION ghstore_consistent(internal,hstore,smallint,oid,internal)499RETURNS bool500AS 'MODULE_PATHNAME'501LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;502 503CREATE OPERATOR CLASS gist_hstore_ops504DEFAULT FOR TYPE hstore USING gist505AS506	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 support523 524CREATE FUNCTION gin_extract_hstore(hstore, internal)525RETURNS internal526AS 'MODULE_PATHNAME'527LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;528 529CREATE FUNCTION gin_extract_hstore_query(hstore, internal, int2, internal, internal)530RETURNS internal531AS 'MODULE_PATHNAME'532LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;533 534CREATE FUNCTION gin_consistent_hstore(internal, int2, hstore, int4, internal, internal)535RETURNS bool536AS 'MODULE_PATHNAME'537LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;538 539CREATE OPERATOR CLASS gin_hstore_ops540DEFAULT FOR TYPE hstore USING gin541AS542	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