hstore.sql

PostgreSQL 8.2中增加了很多企业用户所需要的功能和性能上的提高,其开发团队说,该版本将加速更多企业向该数据库移植.核心开发成员之一Bruce Momjian表示,在新版PostgreSQL

SET search_path = public;

BEGIN;

CREATE FUNCTION hstore_in(cstring)
RETURNS hstore
AS '$libdir/hstore'
LANGUAGE 'C' with (isstrict);

CREATE FUNCTION hstore_out(hstore)
RETURNS cstring
AS '$libdir/hstore'
LANGUAGE 'C' with (isstrict);

CREATE TYPE hstore (
        INTERNALLENGTH = -1,
        INPUT = hstore_in,
        OUTPUT = hstore_out,
        STORAGE = extended
);

CREATE FUNCTION fetchval(hstore,text)
RETURNS text
AS '$libdir/hstore'
LANGUAGE 'C' with (isstrict,iscachable);

CREATE OPERATOR -> (
	LEFTARG = hstore,
	RIGHTARG = text,
	PROCEDURE = fetchval
);

CREATE FUNCTION isexists(hstore,text)
RETURNS bool
AS '$libdir/hstore','exists'
LANGUAGE 'C' with (isstrict,iscachable);

CREATE FUNCTION exist(hstore,text)
RETURNS bool
AS '$libdir/hstore','exists'
LANGUAGE 'C' with (isstrict,iscachable);

CREATE FUNCTION isdefined(hstore,text)
RETURNS bool
AS '$libdir/hstore','defined'
LANGUAGE 'C' with (isstrict,iscachable);

CREATE FUNCTION defined(hstore,text)
RETURNS bool
AS '$libdir/hstore','defined'
LANGUAGE 'C' with (isstrict,iscachable);

CREATE FUNCTION delete(hstore,text)
RETURNS hstore
AS '$libdir/hstore','delete'
LANGUAGE 'C' with (isstrict,iscachable);

CREATE FUNCTION hs_concat(hstore,hstore)
RETURNS hstore
AS '$libdir/hstore'
LANGUAGE 'C' with (isstrict,iscachable);

CREATE OPERATOR || (
	LEFTARG = hstore,
	RIGHTARG = hstore,
	PROCEDURE = hs_concat
);

CREATE FUNCTION hs_contains(hstore,hstore)
RETURNS bool
AS '$libdir/hstore'
LANGUAGE 'C' with (isstrict,iscachable);

CREATE FUNCTION hs_contained(hstore,hstore)
RETURNS bool
AS '$libdir/hstore'
LANGUAGE 'C' with (isstrict,iscachable);

CREATE OPERATOR @> (
	LEFTARG = hstore,
	RIGHTARG = hstore,
	PROCEDURE = hs_contains,
	COMMUTATOR = '<@',
	RESTRICT = contsel,
	JOIN = contjoinsel
);

CREATE OPERATOR <@ (
	LEFTARG = hstore,
	RIGHTARG = hstore,
	PROCEDURE = hs_contained,
	COMMUTATOR = '@>',
	RESTRICT = contsel,
	JOIN = contjoinsel
);

-- obsolete:
CREATE OPERATOR @ (
	LEFTARG = hstore,
	RIGHTARG = hstore,
	PROCEDURE = hs_contains,
	COMMUTATOR = '~',
	RESTRICT = contsel,
	JOIN = contjoinsel
);

CREATE OPERATOR ~ (
	LEFTARG = hstore,
	RIGHTARG = hstore,
	PROCEDURE = hs_contained,
	COMMUTATOR = '@',
	RESTRICT = contsel,
	JOIN = contjoinsel
);

CREATE FUNCTION tconvert(text,text)
RETURNS hstore
AS '$libdir/hstore'
LANGUAGE 'C' with (isstrict,iscachable);

CREATE OPERATOR => (
	LEFTARG = text,
	RIGHTARG = text,
	PROCEDURE = tconvert
);

CREATE FUNCTION akeys(hstore)
RETURNS _text
AS '$libdir/hstore'
LANGUAGE 'C' with (isstrict,iscachable);

CREATE FUNCTION avals(hstore)
RETURNS _text
AS '$libdir/hstore'
LANGUAGE 'C' with (isstrict,iscachable);

CREATE FUNCTION skeys(hstore)
RETURNS setof text
AS '$libdir/hstore'
LANGUAGE 'C' with (isstrict,iscachable);

CREATE FUNCTION svals(hstore)
RETURNS setof text
AS '$libdir/hstore'
LANGUAGE 'C' with (isstrict,iscachable);

create type hs_each as (key text, value text);

CREATE FUNCTION each(hstore)
RETURNS setof hs_each
AS '$libdir/hstore'
LANGUAGE 'C' with (isstrict,iscachable);



-- define the GiST support methods

CREATE FUNCTION ghstore_in(cstring)
RETURNS ghstore
AS '$libdir/hstore'
LANGUAGE 'C' with (isstrict);

CREATE FUNCTION ghstore_out(ghstore)
RETURNS cstring
AS '$libdir/hstore'
LANGUAGE 'C' with (isstrict);

CREATE TYPE ghstore (
        INTERNALLENGTH = -1,
        INPUT = ghstore_in,
        OUTPUT = ghstore_out
);

CREATE FUNCTION ghstore_compress(internal)
RETURNS internal
AS '$libdir/hstore'
LANGUAGE 'C';

CREATE FUNCTION ghstore_decompress(internal)
RETURNS internal
AS '$libdir/hstore'
LANGUAGE 'C';

CREATE FUNCTION ghstore_penalty(internal,internal,internal)
RETURNS internal
AS '$libdir/hstore'
LANGUAGE 'C' WITH (isstrict);

CREATE FUNCTION ghstore_picksplit(internal, internal)
RETURNS internal
AS '$libdir/hstore'
LANGUAGE 'C';

CREATE FUNCTION ghstore_union(internal, internal)
RETURNS internal
AS '$libdir/hstore'
LANGUAGE 'C';

CREATE FUNCTION ghstore_same(internal, internal, internal)
RETURNS internal
AS '$libdir/hstore'
LANGUAGE 'C';

CREATE FUNCTION ghstore_consistent(internal,internal,int4)
RETURNS bool
AS '$libdir/hstore'
LANGUAGE 'C';

-- register the opclass for indexing (not as default)
CREATE OPERATOR CLASS gist_hstore_ops
DEFAULT FOR TYPE hstore USING gist
AS
        OPERATOR        7       @>       RECHECK,
        --OPERATOR        8       <@       RECHECK,
        OPERATOR        13      @       RECHECK,
        --OPERATOR        14      ~       RECHECK,
        FUNCTION        1       ghstore_consistent (internal, internal, int4),
        FUNCTION        2       ghstore_union (internal, internal),
        FUNCTION        3       ghstore_compress (internal),
        FUNCTION        4       ghstore_decompress (internal),
        FUNCTION        5       ghstore_penalty (internal, internal, internal),
        FUNCTION        6       ghstore_picksplit (internal, internal),
        FUNCTION        7       ghstore_same (internal, internal, internal),
        STORAGE         ghstore;

END;