Post GIS Oracle 2005 06 24 v Post

Post. GIS, Oracle 연동 2005 -06 -24 박희현

차례 v Post. GIS 설치 과정 v Postgre. SQL의 SQL 문법 v Oracle의 SQL 문법 2

※ lwpostgis. sql의 내용 4

CREATE TYPE geometry CREATE OR REPLACE FUNCTION geometry_in(cstring) RETURNS geometry AS '$libdir/liblwgeom. so. 1. 0', 'LWGEOM_in' LANGUAGE 'C' IMMUTABLE STRICT; -- WITH (isstrict, iscachable); // Equivalent of IMMUTABLE, STRICT CREATE OR REPLACE FUNCTION geometry_out(geometry) RETURNS cstring AS '$libdir/liblwgeom. so. 1. 0', 'LWGEOM_out' LANGUAGE 'C' IMMUTABLE STRICT; -- WITH (isstrict, iscachable); CREATE OR REPLACE FUNCTION geometry_recv(internal) RETURNS geometry AS '$libdir/liblwgeom. so. 1. 0', 'LWGEOM_recv' LANGUAGE 'C' IMMUTABLE STRICT; -- WITH (isstrict); CREATE OR REPLACE FUNCTION geometry_send(geometry) RETURNS bytea AS '$libdir/liblwgeom. so. 1. 0', 'LWGEOM_send' LANGUAGE 'C' IMMUTABLE STRICT; -- WITH (isstrict); CREATE TYPE geometry ( internallength = variable, input = geometry_in, output = geometry_out, send = geometry_send, receive = geometry_recv, delimiter = ': ', storage = main ); 5

CREATE TYPE (Postgre. SQL) 1/2 CREATE TYPE name ( INPUT = input_function, OUTPUT = output_function 필수 [ , RECEIVE = receive_function ] [ , SEND = send_function ] [ , ANALYZE = analyze_function ] [ , INTERNALLENGTH = { internallength | VARIABLE } ] [ , PASSEDBYVALUE ] [ , ALIGNMENT = alignment ] 옵션 [ , STORAGE = storage ] [ , DEFAULT = default ] [ , ELEMENT = element ] [ , DELIMITER = delimiter ] ) 6

CREATE TYPE (Postgre. SQL) 2/2 항 목 설 명 input_function Converts data from the type's external textual form to its internal form. output_function Converts data from the type's internal form to its external textual form. receive_function Converts the type's external binary representation to the internal representation. send_function Converts from the internal representation to the external binary representation. analyze_function Performs type-specific statistics collection for columns of the data type. internallength. The length in bytes of the new type's internal representation. PASSEDBYVALUE (Flag) Indicates that values of this data type are passed by value alignment Specifies the storage alignment required for the data type. storage Allows selection of storage strategies for variable-length data types. DEFAULT The default value for the data type. ELEMENT The type being created is an array; this specifies the type of the array elements. delimiter The delimiter character to be used between values in arrays made of this type. 7

CREATE FUNCTION (Postgre. SQL) 1/2 CREATE [ OR REPLACE ] FUNCTION name ( [ [ argname ] argtype [, . . . ] ] ) RETURNS rettype { LANGUAGE langname | IMMUTABLE | STABLE | VOLATILE | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AS 'definition' | AS 'obj_file', 'link_symbol' }. . . [ WITH ( attribute [, . . . ] ) ] 8

CREATE FUNCTION (Postgre. SQL) 2/2 항목 설명 name The name of the function to create argname The name of an argument argtype Data type(s) of the function's arguments rettype Return data type langname The name of the language IMMUTABLE Indicates that the function always returns the same result when given the same argument values. CALLED ON NULL INPUT Indicates that the function will be called normally when some of its arguments are null. RETURNS NULL ON NULL INPUT or STRICT Indicates that the function always returns null whenever any of its arguments are null. [EXTERNAL] SECURITY INVOKER Indicates that the function is to be executed with the privileges of the user that calls it. [EXTERNAL] SECURITY DEFINER Specifies that the function is to be executed with the privileges of the user that created it. definition A string constant defining the function obj_file The name of the file containing the dynamically loadable object link_symbol The name of the function in the C language source code. 9

예) Postgre. SQL CREATE OR REPLACE FUNCTION geometry_in(cstring) RETURNS geometry AS '$libdir/liblwgeom. so. 1. 0', 'LWGEOM_in' LANGUAGE 'C' IMMUTABLE STRICT; -- WITH (isstrict, iscachable); Oracle CREATE OR REPLACE FUNCTION geometry_in(string) RETURN geometry AS EXTERNAL LIBRARY liblwgeom NAME "LWGEOM_mindistance 2 d" LANGUAGE C; / 10

난점 v Create type 문법의 차이 v Pl/sql문과 Pl/pg sql문의 차이 11