달력

11

« 2024/11 »

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30

'function'에 해당되는 글 1건

  1. 2014.07.09 function을 이용한 select
2014. 7. 9. 14:34

function을 이용한 select Work/PostgreSQL2014. 7. 9. 14:34

Description & Report

PostgreSql에서는 Stored Procedure 라는 것을 제공하지 않고, Function 만 제공하는 것으로 파악된다.
Return Value를 적당히 결정해 주면 되므로 어찌되었든 종합적인 Query 저장 기능 및 pre-compile 을 할 수 있으므로 이정도면 충분하다.
Oracle에서는 PL/SQL, SQL Server에서는 TSQL, Postgresql에서는 plpgsql 로 부르는 것으로 판단된다.

생성 문법은 Oracle과 같이 CREATE OR REPLACE 를 지원하며 변수선언도 유사하고, SQL Server와 같이 function body에 DDL(Date Define Language)을 사용해도 Compile이 Invalid 되지 않는 것으로 Test 되었다. *(*여기까지는 Oracle의 장점과 SQL Server의 장점을 더한 것으로 보여 매우 만족스럽다)
어느 정도까지 사용할 수 있을지는 다양한 형태로 사용해 보야하겠지만, 지금 수준에서는 사용할만 하다.

Create plpgsql
pgAdmin
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE OR REPLACE FUNCTION agt.usp_jttr_jtpr_YEAR_CNT() RETURNS int AS $$
DECLARE
--  v_Return int;
BEGIN
    truncate table agt.tb_jttr_01_YEAR_CNT;
     
    insert into agt.tb_jttr_01_YEAR_CNT
    select case when a.cyear is null then b.cyear else a.cyear end cyear
        , coalesce(a.app_cnt, 0) app_cnt
        , coalesce(b.pub_cnt, 0) pub_cnt
    from (select substr(app_date,1,4) CYEAR, COUNT(*) app_cnt FROM tb_jttr_01 GROUP BY substr(app_date,1,4)) A
        full outer join (select substr(pub_date,1,4) CYEAR, COUNT(*) pub_cnt FROM tb_jttr_01 GROUP BY substr(pub_date,1,4)) B
            on A.cyear = B.cyear;
 
    truncate table agt.tb_jptr_01_YEAR_CNT;
 
    insert into agt.tb_jptr_01_YEAR_CNT
    select case when a.cyear is null then b.cyear else a.cyear end cyear
        , coalesce(a.app_cnt, 0) app_cnt
        , coalesce(b.pub_cnt, 0) pub_cnt
    from (select substr(app_date,1,4) CYEAR, COUNT(*) app_cnt FROM tb_jptr_01 GROUP BY substr(app_date,1,4)) A
        full outer join (select substr(pub_date,1,4) CYEAR, COUNT(*) pub_cnt FROM tb_jptr_01 GROUP BY substr(pub_date,1,4)) B
            on A.cyear = B.cyear;
    RETURN 0;
END;
$$ LANGUAGE plpgsql;
Execute plpgsql
pgAdmin
select agt.usp_jttr_jtpr_YEAR_CNT();

 

Return RecordSet

SQL Server와 같이 Recordset을 return 하게 하는 방법.

pgAdmin
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Definition
CREATE OR REPLACE FUNCTION db_xml_kr.usp_test_rs(v_seq_xml bigint)
  RETURNS TABLE (
    seq_xml bigint,
  filepath_converted character varying(256)
  )
LANGUAGE SQL AS $$
    SELECT seq_xml
        , filepath_converted
    FROM db_xml_kr.x_filepath
    WHERE seq_xml = $1
    limit 10;
$$;
-- Execute
SELECT * FROM db_xml_kr.usp_test_rs(13);

위와 같이 간단히 Parameter 만 받아서 처리하는 경우는 비교적 쉽게 작성할 수 있지만, 조건문 변수 선언 및 할당 등 plpgsql에서 제공하는 기능을 모두 사용하려면 조금 다르게 작성해야 한다.

Return 받을 recordset 이 Table의 Column을 다 받는다면 역시 비교적 쉽게 작성할 수 있다. (예:http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html)

만일 생성되어 있는 Table과 Return 받을 Recordset 의 형태가 다르다면 아래처럼 Type 을 생성한 후 받는 방법을 사용한다.

pgAdmin (203.242.170.203 db_patent)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- create type
create type rs_info_table as (ordinal_position integer, column_name character varying, data_type character varying, character_maximum_length  integer);
 
-- create function
CREATE OR REPLACE FUNCTION asp_info_table(IN v_tname character varying)
  RETURNS SETOF rs_info_table AS
$BODY$
DECLARE
    rs rs_info_table%rowtype;
BEGIN
    IF position('.' in $1 ) > 0 THEN
        for rs in
            SELECT A.ordinal_position, A.column_name, A.data_type, A.character_maximum_length
            FROM information_schema.columns A
            WHERE table_schema = substring($1, 1, position('.' in $1) - 1)
                AND table_name = substring($1, position('.' in $1) + 1, character_length($1))
            ORDER BY ordinal_position      
        loop
            return next rs;
        end loop;
    ELSE
        for rs in
            SELECT A.ordinal_position, A.column_name, A.data_type, A.character_maximum_length
            FROM information_schema.columns A
            WHERE table_schema = 'public'
                AND table_name = $1
            ORDER BY ordinal_position
        loop
            return next rs;
        end loop;
    END IF;
    return;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION asp_info_table(IN v_tname character varying) OWNER TO nips;
 
-- Execute
select * from asp_info_table('rdb_jt.t_publish');

위 방법만 가능한 줄 알았다..

정말 다른 방법이 없는 것으로 확인되었는데... 최근 입수한 PostgreSQL Server Programming 이라는 책에서 아래와 같은 아주 간단한 방법을 제공하는 것을 확인했다.

CREATE OR REPLACE FUNCTION db_pas.usp_inven_select_with_proj_id_tech_id_stream(p_proj_id character varying, p_tech_id_stream character varying)
  RETURNS TABLE(id_kipi character varying, dt_sys   character varying, query_info   character varying) AS
$BODY$
DECLARE
    rs db_pas.t_inven%rowtype;
BEGIN
    IF length(p_tech_id_stream) = 0 THEN
        return query
        select distinct b.id_kipi, b.dt_sys, coalesce(b.query_info, '')
        from db_pas.t_inven_tech a inner join db_pas.t_inven b on a.id_kipi = b.id_kipi and a.proj_id = b.proj_id
        where a.proj_id = '{0}';
    ELSE
        return query
        select distinct b.id_kipi, b.dt_sys, coalesce(b.query_info, '')
        from db_pas.t_inven_tech a inner join db_pas.t_inven b on a.id_kipi = b.id_kipi and a.proj_id = b.proj_id
        where a.proj_id = p_proj_id
            and a.tech_id in (select unnest(string_to_array(p_tech_id_stream, ',')));
    END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

함수를 생성할 때마다 타입을 같이 생성해 주어야 하는 번거로움과 loop을 해야 하는 두려움을 모두 해결할 수 있는 좋은 방법을 찾았다.

postgresql-9.2 에서 test 되었다.

저작자표시 비영리 변경금지
:
Posted by Elick

티스토리툴바