SQL 예제로 배우는 PostgreSQL (ver 0.91a): 기초편 1

3. 기초편 1

3.1 문자열 상수 (String Constants)


 select text 'nogadax' AS CAST_str1;

    cast_str1 
   -----------
    nogadax
   (1 row)


 select 'ngd'::text AS CAST_str2;
 
   cast_str2 
   -----------
    ngd
   (1 row)


 select 'ngd\'s Home'::text AS CAST_str3;

    cast_str3  
   ------------
    ngd's Home
   (1 row)


 select 'ngd''s Home'::text AS CAST_str4;

    cast_str4  
   ------------
    ngd's Home
   (1 row)

3.2 Integer Constants


 select int8 '4000000000' as cast_i8_1;

    cast_i8_1  
   ------------
    4000000000
   (1 row)


 select 4000000000::int8 as cast_i8_2;

    cast_i8_2  
   ------------
    4000000000
   (1 row)


 select int8 '4000000000' + '4000000000'::int8 as cast_i8_3;

    cast_i8_3  
   ------------
    8000000000
   (1 row)

3.3 Floating Point Constants


 select float4 '1.55' as cast_f4_1;

    cast_f4_1 
   -----------
         1.55
   (1 row)


 select '1.55'::float4 as cast_f4_2;

    cast_f4_2 
   -----------
         1.55
   (1 row)

3.4 Data Types


 select bool 't' as bool_1;

    bool_1 
   --------
    t
   (1 row)


 select int2 '12', 13::int4, 14::int8, 100;

    ?column? | ?column? | ?column? | ?column? 
   ----------+----------+----------+----------
          12 |       13 |       14 |      100
   (1 row)


 select float4 '11.234511' as float4_1;

    float4_1 
   ----------
     11.2345
   (1 row)


 select float8 '11.234511' as float8_1;

    float8_1  
   -----------
    11.234511
   (1 row)


 select float8 '1211.2345112111' as float8_2;

       float8_2     
   -----------------
    1211.2345112111
   (1 row)


 select text 'ngd' as team_name , point '(1,2)' as location;

    team_name | location 
   -----------+----------
    ngd       | (1,2)
   (1 row)


select '2001-3-1'::date + 4 as date2;

    date2    
------------
 2001-03-5
(1 row)

select '2001-3-1'::date +'2 year'::interval as date3;

         date3          
------------------------
 2003-03-01 00:00:00+09
(1 row)

select '2001-3-1'::date +'3 month'::interval as date4;

         date4          
------------------------
 2001-06-01 00:00:00+09
(1 row)

select '2001-3-1'::date +'4 day'::interval as date5;

         date5          
------------------------
 2001-03-05 00:00:00+09
(1 row)

3.5 Function Constants

"getpgusername()"함수는 현재의 사용자 이름을 리턴한다.


 select getpgusername() AS cur_user_name ;

    cur_user_name 
   ---------------
    postgres
   (1 row)

"now" 는 현재시간을 갖는 특별한 변수이다.


 select date('now') as cur_date;

     cur_date  
   ------------
    2001-03-16
   (1 row)


 select time('now') as cur_time;

    cur_time 
   ----------
    19:14:17
   (1 row)


 select timestamp('now') as cur_transaction_date_time;

    cur_transaction_date_time 
   ---------------------------
    2001-03-16 19:14:17+09
   (1 row)

3.6 String Functions


 select char_length('nogadax') as char_length_1;

    char_length_1 
   ---------------
                7
   (1 row)


 select char_length('노가다') as char_length_2;

    char_length_2 
   ---------------
                3
   (1 row)


 select position('g' in 'nogada') as pos_1;

    pos_1 
   -------
        3
   (1 row)

3.7 SQL FUNCTIONs

NULLIF(input,value) 는 input==value 이면 NULL을 리턴하고 아니면 input 값을 리턴한다.

아래의 첫 예제는 NULL 을 리턴한다.


 select NULLIF('nn'::text,'nn'::text) as NULLIF_Test_1;

    nullif_test_1 
   ---------------
    
   (1 row)


 select NULLIF('nb'::text,'nn'::text) as NULLIF_Test_2;

    nullif_test_2 
   ---------------
    nb
   (1 row)

COALESCE(list) 의 list 는 "a,b,c,.."처럼 표현하고 리스트상의 인자중 NULL 값이 아닌 제일 첫 인자를 리턴한다.


 select COALESCE(1,2,3,4) AS COALESCE_Test_1;

    coalesce_test_1 
   -----------------
                  1
   (1 row)

 
 select COALESCE(5,6,7) AS COALESCE_Test_2;

    coalesce_test_2 
   -----------------
                  5
   (1 row)


 select COALESCE(8,9) AS COALESCE_Test_3;

    coalesce_test_3 
   -----------------
                  8
   (1 row)

"CASE WHEN expr THEN value1 ELSE value2 END" expr 이 참이면 value1을, 아니면 value2를 리턴한다.


 select 
  CASE 
    WHEN 'ngd'::text = 'ngd'::text
     THEN 'match'  ELSE 'no match'
  END ;


    case  
   -------
    match
   (1 row)



 select 
  CASE 
    WHEN 'ngd'::text = 'nogadax'::text
     THEN 'match'  ELSE 'no match'
  END ;


     case   
   ----------
    no match
   (1 row)

3.8 SQL FUNCTION을 이용한 함수 예제


 DROP FUNCTION SQL_FUNC_1(int4);
 DROP FUNCTION SQL_FUNC_2(int4);
 DROP TABLE NOGADA_TAB;

 create TABLE NOGADA_TAB (id_ngd int4 , name_ngd text);

 INSERT INTO NOGADA_TAB VALUES (1, 'ngd_1');
 INSERT INTO NOGADA_TAB VALUES (2, 'ngd_2');

 CREATE FUNCTION SQL_FUNC_1(int4) RETURNS text AS '
 SELECT
  CASE
     WHEN NOGADA_TAB.id_ngd = $1
       THEN ''있군요..''::text
       ELSE ''없군요..''::text
  END
 ' LANGUAGE 'sql';


  SELECT SQL_FUNC_1(1) ;


   sql_func_1 
  ------------
   있군요..
  (1 row)



 SELECT SQL_FUNC_1(3) ;


   sql_func_1 
  ------------
   없군요..
  (1 row)


 CREATE FUNCTION SQL_FUNC_2(int4) RETURNS text AS '
 SELECT
  CASE
     WHEN NOGADA_TAB.id_ngd = $1
       THEN NOGADA_TAB.name_ngd
       ELSE ''없군요..''::text
  END
 ' LANGUAGE 'sql';



  SELECT SQL_FUNC_2(1) as NOGADA_TAB_NAME;


   nogada_tab_name 
  -----------------
   ngd_1
  (1 row)

3.9 Full SQL SOURCE

다음은 위에서 설명한 전체 예제이다. copy하여 파일로 저장 후 PSQL 에서 테스트 할 수 있다. 참고로 "--" 시작하는 라인은 주석이다.



------------------------------------------------
-- By NoGaDa-X  (nogadax@kldp.org)
-- PostgreSQL's SQL Examples
-- (http://ngd.pe.kr)
-- /2001/03/15
------------------------------------------------

--String Constants

select text 'nogadax' AS CAST_str1;
select 'ngd'::text AS CAST_str2;
select 'ngd\'s Home'::text AS CAST_str3;
select 'ngd''s Home'::text AS CAST_str4;

--Integer Constants

select int8 '4000000000' as cast_i8_1;
select 4000000000::int8 as cast_i8_2;
select int8 '4000000000' + '4000000000'::int8 as cast_i8_3;

--Floating Point Constants

select float4 '1.55' as cast_f4_1;
select '1.55'::float4 as cast_f4_2;

--Data Types

select bool 't' as bool_1;
select int2 '12', 13::int4, 14::int8, 100;
select float4 '11.234511' as float4_1;
select float8 '11.234511' as float8_1;
select float8 '1211.2345112111' as float8_2;
select text 'ngd' as team_name , point '(1,2)' as location;

--Function Constants

select getpgusername() AS cur_user_name ;
select date('now') as cur_date;
select time('now') as cur_time;
select timestamp('now') as cur_transaction_date_time;

--String Functions

select char_length('nogadax') as char_length_1;
select char_length('노가다') as char_length_2;
select position('g' in 'nogada') as pos_1;

--SQL FUNCTIONs
--SQL FUNCTIONs

--NULLIF(input,value) 
--IF input==value THEN return NULL ELSE input

select NULLIF('nn'::text,'nn'::text) as NULLIF_Test_1;
select NULLIF('nb'::text,'nn'::text) as NULLIF_Test_2;

--COALESCE(list)
--list is (a,b,c,..)
--First-Non-NULL-value in list is returned

select COALESCE(1,2,3,4) AS COALESCE_Test_1;
select COALESCE(5,6,7) AS COALESCE_Test_2;
select COALESCE(8,9) AS COALESCE_Test_3;

--CASE WHEN expr THEN value1 ELSE value2 END

select 
  CASE 
    WHEN 'ngd'::text = 'ngd'::text
     THEN 'match'  ELSE 'no match'
  END ;
select 
  CASE 
    WHEN 'ngd'::text = 'nogadax'::text
     THEN 'match'  ELSE 'no match'
  END ;

--User-defined Function with SQL's Functions

DROP FUNCTION SQL_FUNC_1(int4);
DROP FUNCTION SQL_FUNC_2(int4);
DROP TABLE NOGADA_TAB;

create TABLE NOGADA_TAB (id_ngd int4 , name_ngd text);

INSERT INTO NOGADA_TAB VALUES (1, 'ngd_1');
INSERT INTO NOGADA_TAB VALUES (2, 'ngd_2');

CREATE FUNCTION SQL_FUNC_1(int4) RETURNS text AS '
SELECT
  CASE
     WHEN NOGADA_TAB.id_ngd = $1
       THEN ''있군요..''::text
       ELSE ''없군요..''::text
  END
' LANGUAGE 'sql';

SELECT SQL_FUNC_1(1) ;
SELECT SQL_FUNC_1(3) ;


CREATE FUNCTION SQL_FUNC_2(int4) RETURNS text AS '
SELECT
  CASE
     WHEN NOGADA_TAB.id_ngd = $1
       THEN NOGADA_TAB.name_ngd
       ELSE ''없군요..''::text
  END
' LANGUAGE 'sql';

SELECT SQL_FUNC_2(1) as NOGADA_TAB_NAME;

Next Previous Contents