isn.sql

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

--
--	PostgreSQL code for ISNs (ISBN, ISMN, ISSN, EAN13, UPC)
--  Copyright (c) 2004-2006, German Mendez Bravo (Kronuz)
--
--	$PostgreSQL: pgsql/contrib/isn/isn.sql.in,v 1.4 2006/11/24 18:44:37 tgl Exp $
--
-- Example:
--   create table test ( id isbn );
--   insert into test values('978-0-393-04002-9');
--
--   select isbn('978-0-393-04002-9');
--   select isbn13('0-901690-54-6');
--

-- Adjust this setting to control where the objects get created.
SET search_path = public;

BEGIN;

--
--	Input and output functions and data types:
--
---------------------------------------------------
CREATE FUNCTION ean13_in(cstring)
	RETURNS ean13
	AS '$libdir/isn'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE FUNCTION ean13_out(ean13)
	RETURNS cstring
	AS '$libdir/isn'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE TYPE ean13 (
	INPUT = ean13_in,
	OUTPUT = ean13_out,
	INTERNALLENGTH = 8,
	ALIGNMENT = double,
	STORAGE = PLAIN
);
COMMENT ON TYPE ean13
	IS 'International European Article Number (EAN13)';

CREATE FUNCTION isbn13_in(cstring)
	RETURNS isbn13
	AS '$libdir/isn', 'isbn_in'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE FUNCTION ean13_out(isbn13)
	RETURNS cstring
	AS '$libdir/isn'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE TYPE isbn13 (
	INPUT = isbn13_in,
	OUTPUT = ean13_out,
	INTERNALLENGTH = 8,
	ALIGNMENT = double,
	STORAGE = PLAIN
);
COMMENT ON TYPE isbn13
	IS 'International Standard Book Number 13 (ISBN13)';

CREATE FUNCTION ismn13_in(cstring)
	RETURNS ismn13
	AS '$libdir/isn', 'ismn_in'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE FUNCTION ean13_out(ismn13)
	RETURNS cstring
	AS '$libdir/isn'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE TYPE ismn13 (
	INPUT = ismn13_in,
	OUTPUT = ean13_out,
	INTERNALLENGTH = 8,
	ALIGNMENT = double,
	STORAGE = PLAIN
);
COMMENT ON TYPE ismn13
	IS 'International Standard Music Number 13 (ISMN13)';

CREATE FUNCTION issn13_in(cstring)
	RETURNS issn13
	AS '$libdir/isn', 'issn_in'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE FUNCTION ean13_out(issn13)
	RETURNS cstring
	AS '$libdir/isn'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE TYPE issn13 (
	INPUT = issn13_in,
	OUTPUT = ean13_out,
	INTERNALLENGTH = 8,
	ALIGNMENT = double,
	STORAGE = PLAIN
);
COMMENT ON TYPE issn13
	IS 'International Standard Serial Number 13 (ISSN13)';

-- Short format:
	
CREATE FUNCTION isbn_in(cstring)
	RETURNS isbn
	AS '$libdir/isn'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE FUNCTION isn_out(isbn)
	RETURNS cstring
	AS '$libdir/isn'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE TYPE isbn (
	INPUT = isbn_in,
	OUTPUT = isn_out,
	INTERNALLENGTH = 8,
	ALIGNMENT = double,
	STORAGE = PLAIN
);
COMMENT ON TYPE isbn
	IS 'International Standard Book Number (ISBN)';

CREATE FUNCTION ismn_in(cstring)
	RETURNS ismn
	AS '$libdir/isn'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE FUNCTION isn_out(ismn)
	RETURNS cstring
	AS '$libdir/isn'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE TYPE ismn (
	INPUT = ismn_in,
	OUTPUT = isn_out,
	INTERNALLENGTH = 8,
	ALIGNMENT = double,
	STORAGE = PLAIN
);
COMMENT ON TYPE ismn
	IS 'International Standard Music Number (ISMN)';

CREATE FUNCTION issn_in(cstring)
	RETURNS issn
	AS '$libdir/isn'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE FUNCTION isn_out(issn)
	RETURNS cstring
	AS '$libdir/isn'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE TYPE issn (
	INPUT = issn_in,
	OUTPUT = isn_out,
	INTERNALLENGTH = 8,
	ALIGNMENT = double,
	STORAGE = PLAIN
);
COMMENT ON TYPE issn
	IS 'International Standard Serial Number (ISSN)';

CREATE FUNCTION upc_in(cstring)
	RETURNS upc
	AS '$libdir/isn'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE FUNCTION isn_out(upc)
	RETURNS cstring
	AS '$libdir/isn'
	LANGUAGE 'C'
	IMMUTABLE STRICT;
CREATE TYPE upc (
	INPUT = upc_in,
	OUTPUT = isn_out,
	INTERNALLENGTH = 8,
	ALIGNMENT = double,
	STORAGE = PLAIN
);
COMMENT ON TYPE upc
	IS 'Universal Product Code (UPC)';

--
-- Operator functions:
--
---------------------------------------------------
-- EAN13:
CREATE FUNCTION isnlt(ean13, ean13)
	RETURNS boolean
	AS 'int8lt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnle(ean13, ean13)
	RETURNS boolean
	AS 'int8le'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isneq(ean13, ean13)
	RETURNS boolean
	AS 'int8eq'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnge(ean13, ean13)
	RETURNS boolean
	AS 'int8ge'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isngt(ean13, ean13)
	RETURNS boolean
	AS 'int8gt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnne(ean13, ean13)
	RETURNS boolean
	AS 'int8ne'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;

CREATE FUNCTION isnlt(ean13, isbn13)
	RETURNS boolean
	AS 'int8lt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnle(ean13, isbn13)
	RETURNS boolean
	AS 'int8le'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isneq(ean13, isbn13)
	RETURNS boolean
	AS 'int8eq'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnge(ean13, isbn13)
	RETURNS boolean
	AS 'int8ge'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isngt(ean13, isbn13)
	RETURNS boolean
	AS 'int8gt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnne(ean13, isbn13)
	RETURNS boolean
	AS 'int8ne'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;

CREATE FUNCTION isnlt(ean13, ismn13)
	RETURNS boolean
	AS 'int8lt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnle(ean13, ismn13)
	RETURNS boolean
	AS 'int8le'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isneq(ean13, ismn13)
	RETURNS boolean
	AS 'int8eq'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnge(ean13, ismn13)
	RETURNS boolean
	AS 'int8ge'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isngt(ean13, ismn13)
	RETURNS boolean
	AS 'int8gt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnne(ean13, ismn13)
	RETURNS boolean
	AS 'int8ne'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;

CREATE FUNCTION isnlt(ean13, issn13)
	RETURNS boolean
	AS 'int8lt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnle(ean13, issn13)
	RETURNS boolean
	AS 'int8le'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isneq(ean13, issn13)
	RETURNS boolean
	AS 'int8eq'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnge(ean13, issn13)
	RETURNS boolean
	AS 'int8ge'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isngt(ean13, issn13)
	RETURNS boolean
	AS 'int8gt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnne(ean13, issn13)
	RETURNS boolean
	AS 'int8ne'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;

CREATE FUNCTION isnlt(ean13, isbn)
	RETURNS boolean
	AS 'int8lt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnle(ean13, isbn)
	RETURNS boolean
	AS 'int8le'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isneq(ean13, isbn)
	RETURNS boolean
	AS 'int8eq'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnge(ean13, isbn)
	RETURNS boolean
	AS 'int8ge'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isngt(ean13, isbn)
	RETURNS boolean
	AS 'int8gt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnne(ean13, isbn)
	RETURNS boolean
	AS 'int8ne'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;

CREATE FUNCTION isnlt(ean13, ismn)
	RETURNS boolean
	AS 'int8lt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnle(ean13, ismn)
	RETURNS boolean
	AS 'int8le'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isneq(ean13, ismn)
	RETURNS boolean
	AS 'int8eq'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnge(ean13, ismn)
	RETURNS boolean
	AS 'int8ge'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isngt(ean13, ismn)
	RETURNS boolean
	AS 'int8gt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnne(ean13, ismn)
	RETURNS boolean
	AS 'int8ne'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;

CREATE FUNCTION isnlt(ean13, issn)
	RETURNS boolean
	AS 'int8lt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnle(ean13, issn)
	RETURNS boolean
	AS 'int8le'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isneq(ean13, issn)
	RETURNS boolean
	AS 'int8eq'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnge(ean13, issn)
	RETURNS boolean
	AS 'int8ge'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isngt(ean13, issn)
	RETURNS boolean
	AS 'int8gt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnne(ean13, issn)
	RETURNS boolean
	AS 'int8ne'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;

CREATE FUNCTION isnlt(ean13, upc)
	RETURNS boolean
	AS 'int8lt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnle(ean13, upc)
	RETURNS boolean
	AS 'int8le'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isneq(ean13, upc)
	RETURNS boolean
	AS 'int8eq'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnge(ean13, upc)
	RETURNS boolean
	AS 'int8ge'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isngt(ean13, upc)
	RETURNS boolean
	AS 'int8gt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnne(ean13, upc)
	RETURNS boolean
	AS 'int8ne'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;

---------------------------------------------------
-- ISBN13:
CREATE FUNCTION isnlt(isbn13, isbn13)
	RETURNS boolean
	AS 'int8lt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnle(isbn13, isbn13)
	RETURNS boolean
	AS 'int8le'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isneq(isbn13, isbn13)
	RETURNS boolean
	AS 'int8eq'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnge(isbn13, isbn13)
	RETURNS boolean
	AS 'int8ge'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isngt(isbn13, isbn13)
	RETURNS boolean
	AS 'int8gt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnne(isbn13, isbn13)
	RETURNS boolean
	AS 'int8ne'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;

CREATE FUNCTION isnlt(isbn13, isbn)
	RETURNS boolean
	AS 'int8lt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnle(isbn13, isbn)
	RETURNS boolean
	AS 'int8le'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isneq(isbn13, isbn)
	RETURNS boolean
	AS 'int8eq'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnge(isbn13, isbn)
	RETURNS boolean
	AS 'int8ge'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isngt(isbn13, isbn)
	RETURNS boolean
	AS 'int8gt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnne(isbn13, isbn)
	RETURNS boolean
	AS 'int8ne'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;

CREATE FUNCTION isnlt(isbn13, ean13)
	RETURNS boolean
	AS 'int8lt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnle(isbn13, ean13)
	RETURNS boolean
	AS 'int8le'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isneq(isbn13, ean13)
	RETURNS boolean
	AS 'int8eq'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnge(isbn13, ean13)
	RETURNS boolean
	AS 'int8ge'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isngt(isbn13, ean13)
	RETURNS boolean
	AS 'int8gt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnne(isbn13, ean13)
	RETURNS boolean
	AS 'int8ne'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;

---------------------------------------------------
-- ISBN:
CREATE FUNCTION isnlt(isbn, isbn)
	RETURNS boolean
	AS 'int8lt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnle(isbn, isbn)
	RETURNS boolean
	AS 'int8le'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isneq(isbn, isbn)
	RETURNS boolean
	AS 'int8eq'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnge(isbn, isbn)
	RETURNS boolean
	AS 'int8ge'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isngt(isbn, isbn)
	RETURNS boolean
	AS 'int8gt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnne(isbn, isbn)
	RETURNS boolean
	AS 'int8ne'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;

CREATE FUNCTION isnlt(isbn, isbn13)
	RETURNS boolean
	AS 'int8lt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnle(isbn, isbn13)
	RETURNS boolean
	AS 'int8le'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isneq(isbn, isbn13)
	RETURNS boolean
	AS 'int8eq'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnge(isbn, isbn13)
	RETURNS boolean
	AS 'int8ge'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isngt(isbn, isbn13)
	RETURNS boolean
	AS 'int8gt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnne(isbn, isbn13)
	RETURNS boolean
	AS 'int8ne'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;

CREATE FUNCTION isnlt(isbn, ean13)
	RETURNS boolean
	AS 'int8lt'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnle(isbn, ean13)
	RETURNS boolean
	AS 'int8le'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isneq(isbn, ean13)
	RETURNS boolean
	AS 'int8eq'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isnge(isbn, ean13)
	RETURNS boolean
	AS 'int8ge'
	LANGUAGE 'internal' 
	IMMUTABLE STRICT;
CREATE FUNCTION isngt(isbn, ean13)
	RETURNS boolean
	AS 'int8gt'
	LANGUAGE 'internal'