'Work/PostgreSQL' 카테고리의 글 목록

2020. 7. 1. 13:15

Window function 정리 Work/PostgreSQL2020. 7. 1. 13:15

윈도우 함수 : 현재 행과 관련된 결과집합의 여러가지 연산을 수행. 집계함수와 유사하나 단일 행으로 그룹화 되지 않는다.

일반적인 windows fuction : https://www.postgresql.org/docs/9.3/functions-window.html

PostgreSQL: Documentation: 9.3: Window Functions

Window functions provide the ability to perform calculations across sets of rows that are related to the current query row. See Section 3.5 for an introduction to this feature. The built-in window functions are listed in Table 9-49. Note that these functio

www.postgresql.org

조금 난이도 있는 window function 활용 : https://www.cybertec-postgresql.com/en/calculating-differences-between-rows-in-sql/

Calculating differences between rows in SQL - Cybertec

Calculating differences between rows | How can one calculate the difference between the current and the previous row? | timeseries data

www.cybertec-postgresql.com

위 페이지의 sample code

cypex=# CREATE TABLE t_oil

(

region text,

country text,

year int,

production int,

consumption int

);

cypex=# COPY t_oil

FROM PROGRAM 'curl https://www.cybertec-postgresql.com/secret/oil_ext.txt';

COPY 644

cypex=# SELECT country, year, production,

lag(production, 1) OVER (ORDER BY year)

FROM t_oil

WHERE country = 'USA'

LIMIT 10;

country | year | production | lag

---------+------+------------+-------

USA | 1965 | 9014 |

USA | 1966 | 9579 | 9014

USA | 1967 | 10219 | 9579

USA | 1968 | 10600 | 10219

USA | 1969 | 10828 | 10600

USA | 1970 | 11297 | 10828

USA | 1971 | 11156 | 11297

USA | 1972 | 11185 | 11156

USA | 1973 | 10946 | 11185

USA | 1974 | 10461 | 10946

(10 rows)

cypex=# SELECT country, year,

production - lag(production, 1) OVER (ORDER BY year) AS diff

FROM t_oil

WHERE country = 'USA'

LIMIT 10;

country | year | diff

---------+------+------

USA | 1965 |

USA | 1966 | 565

USA | 1967 | 640

USA | 1968 | 381

USA | 1969 | 228

USA | 1970 | 469

USA | 1971 | -141

USA | 1972 | 29

USA | 1973 | -239

USA | 1974 | -485

(10 rows)

cypex=# SELECT t_oil.country,

t_oil.year,

t_oil.production,

t_oil.production - first_value(t_oil.production) OVER (ORDER BY t_oil.year) AS diff_first

FROM t_oil

WHERE t_oil.country = 'USA'::text

LIMIT 10;

country | year | production | diff_first

---------+------+------------+------------

USA | 1965 | 9014 | 0

USA | 1966 | 9579 | 565

USA | 1967 | 10219 | 1205

USA | 1968 | 10600 | 1586

USA | 1969 | 10828 | 1814

USA | 1970 | 11297 | 2283

USA | 1971 | 11156 | 2142

USA | 1972 | 11185 | 2171

USA | 1973 | 10946 | 1932

USA | 1974 | 10461 | 1447

(10 rows)

cypex=# SELECT country, year, production,

lag(production) OVER (PARTITION BY country ORDER BY year) AS diff

FROM t_oil

WHERE country IN ('Canada', 'Mexico')

AND year < 1970;

country | year | production | diff

---------+------+------------+------

Canada | 1965 | 920 |

Canada | 1966 | 1012 | 920

Canada | 1967 | 1106 | 1012

Canada | 1968 | 1194 | 1106

Canada | 1969 | 1306 | 1194

Mexico | 1965 | 362 |

Mexico | 1966 | 370 | 362

Mexico | 1967 | 411 | 370

Mexico | 1968 | 439 | 411

Mexico | 1969 | 461 | 439

(10 rows)

cypex=# SELECT country, year, production,

avg(production) OVER (ORDER BY year

ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS mov

FROM t_oil

WHERE country IN ('Saudi Arabien')

AND year BETWEEN 1975 AND 1990;

country | year | production | mov

---------------+------+------------+-----------------------

Saudi Arabien | 1975 | 7216 | 8465.6666666666666667

Saudi Arabien | 1976 | 8762 | 8487.7500000000000000

Saudi Arabien | 1977 | 9419 | 8758.4000000000000000

Saudi Arabien | 1978 | 8554 | 9369.2000000000000000

Saudi Arabien | 1979 | 9841 | 9668.0000000000000000

Saudi Arabien | 1980 | 10270 | 9176.4000000000000000

Saudi Arabien | 1981 | 10256 | 8455.8000000000000000

Saudi Arabien | 1982 | 6961 | 7394.4000000000000000

Saudi Arabien | 1983 | 4951 | 6060.6000000000000000

Saudi Arabien | 1984 | 4534 | 5051.0000000000000000

Saudi Arabien | 1985 | 3601 | 4578.6000000000000000

Saudi Arabien | 1986 | 5208 | 4732.4000000000000000

Saudi Arabien | 1987 | 4599 | 4952.6000000000000000

Saudi Arabien | 1988 | 5720 | 5653.4000000000000000

Saudi Arabien | 1989 | 5635 | 5764.7500000000000000

Saudi Arabien | 1990 | 7105 | 6153.3333333333333333

(16 rows)

cypex=# SELECT t_oil, lag(t_oil) OVER (ORDER BY year)

FROM t_oil

WHERE country = 'Mexico'

AND year IN (1980, 1981);

t_oil | lag

-----------------------------------------+-----------------------------------------

("North America",Mexico,1980,2129,1048) |

("North America",Mexico,1981,2553,1172) | ("North America",Mexico,1980,2129,1048)

(2 rows)

cypex=# SELECT t_oil = lag(t_oil) OVER (ORDER BY year)

FROM t_oil

WHERE country = 'Mexico'

AND year IN (1980, 1981);

?column?

----------

f

(2 rows)

:

Posted by Elick

2019. 3. 28. 14:07

plpython3u ... Work/PostgreSQL2019. 3. 28. 14:07

postgresql 11 을 설치해서 사용해보고 있는데...

plpython 을 사용해야 할 일이 있어 버전 확인을 해보았다.

기본으로 2.7.5 를 사용할 수 있는 듯...

이전에도 phtyon 3 버전을 사용할 수 있는 방법을 찾고 있었는데, 별 수 없는듯...

그런데.. plpython3u 설치에 대한 한국 블로그를 보고 감동...

http://blog.naver.com/PostView.nhn?blogId=kwnam4u&logNo=221413731215&categoryNo=7&parentCategoryNo=0&viewDate=¤tPage=1&postListTopCurrentPage=1&from=postView

CentOS7에서 PostgreSQL 11과 Pl/Python3u와 PL/Java 1.5.1 설치

Installation of PostgreSQL 11 1. Up-to-date$su$yum update2. Installation of PostgreSQL...

blog.naver.com

이대로 하면 될지 안될지 모르지만... 정말 감사..

[Installation of PostgreSQL 11 ]
1. Up-to-date
    $su
    $yum update

2. Installation of PostgreSQL
    $yum install postgresql11-server.x86_64 postgresql11.x86_64

3. Creating a initial database
    $cd /usr/pgsql-11/bin/
    $./postgresql-11-setup initdb

4. Setting for Automatic restart of PostgreSQL
    $systemctl enable postgresql-11
    $systemctl enable postgresql-11.service
    $systemctl start postgresql-11.service

5. Firewall Setting to open port 5432
    $firewall-cmd --zone=public --add-port=5432/tcp

6. Setting for External connection to DBMS
    $cd /var/lib/pgsql/11/data
    $vi postgresql.conf

    -- edit / add the following line
    listen_addresses = '*'

7. Setting for Network connection from external client
    $cd /var/lib/pgsql/11/data
    $vi pg_hba.conf

    -- edit the pg_hba.conf
    local all all peer   =>  local all all md5
    host all all 127.0.0.1/32 ident   =>  host all all 0.0.0.0/0 md5
    host all all ::1/128 ident   =>  host all all ::1/128 md5

    -- Final result
    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    # "local" is for Unix domain socket connections only
    local   all             all                                     md5
    # IPv4 local connections:
    host    all             all             0.0.0.0/0              md5
    # IPv6 local connections:
    host    all             all             ::1/128                md5

8. Setting for postgres
    $su postgres
    $psql
    \password postgres
    \q

9. Return to root or another user
    $exit

10. Restart of PostgreSQL
    $systemctl  restart  postgresql-11.service

11.  UUID 확장 기능 설치를 위해 다음 명령이 필요함
    $systemctl stop postgresql-11.service
    $yum install postgresql11-contrib.x86_64
    $systemctl start postgresql-11.service

[Installation of PgAdmin4(optional)]
    $yum  install pgadmin4-desktop-gnome

[Installation of PostGIS on PostgreSQL 11]
    $yum  install  epel-release

    --For version 2.5 on postgresql 11
    $yum  install  postgis25_11.x86_64
    $systemctl  restart  postgresql-11.service

[Installation of PL/Python3u on PostgreSQL 11]
1.Installation of Python 3.6
    $sudo yum -y update
    $sudo yum -y install yum-utils
    $sudo yum -y groupinstall development
    $sudo yum -y install https://centos7.iuscommunity.org/ius-release.rpm
    $sudo yum -y install   python36u.x86_64   python36u-pip.noarch python36u-devel.x86_64

2.Compile and Install of PL/Python3u
    PosgreSQL 11 Source Code Download : https://www.postgresql.org/ftp/source/

    1) Download postgresql source code
        $wget https://ftp.postgresql.org/pub/source/v11.1/postgresql-11.1.tar.gz
        $gzip -d posgresql-11.1.tar.gz
        $tar xvf postgresql-11.1.tar
        $cd   postgresql-11.1

    2) Compile and make
        $yum install readline-devel zlib.devel
        $./configure  \--prefix=/usr/pgsql-11 \--with-python \--enable-nls=ko  \PYTHON='/usr/bin/python3.6'
        $make

    3) plpython3.so and *.control file install
        $cd src/pl/plpython
        $sudo  cp   plpython3u.*   /usr/pgsql-11/share/extension
        $sudo  cp   plpython3.so   /usr/pgsql-11/lib

    4) In psql and pgAdmin4
        CREATE LANGUAGE plpython3u;

[Installation of PL/Java (1.5.1-.rhel7) on CentOS 7]
    1) Installation of OpenJDK and Pl/Java
        $yum install java-1.8.0-openjdk-devel.x86_64
        $ yum install
        $ yum install pljava-11.x86_64

    2) Setting in PostgreSQL for  pl/java
        $cd /var/lib/pgsql/11/data
        $vi postgresql.conf

        #Add the following lines for the pljava setting
        pljava.classpath='/usr/pgsql-11/share/pljava/pljava-1.5.1.jar'
        pljava.libjvm_location='/usr/lib/jvm/java-1.8.0-openjdk/jre/lib/amd64/server/libjvm.so'

    3) Restart of PostgreSQL
        $systemctl restart postgresql-11.service

    4) In psql or pgAdmin4
        CREATE EXTENSION pljava;

[Perfect uninstall of PostgreSQL]
    1) Removing pgAdmin4
    $yum erase pgadmin4-desktop-gnome.noarch

    2) Removing postgres* packages and directories
    $yum erase postgres*
    $rm-rf /var/lib/pgsql
    $rm -rf /usr/pgsql-11
    $rm -f /tmp/.s.PGSQL.5432*

[Installation of AnyDesk 4.0.1 for Remote Desktop]
    $sudo wget https://download.anydesk.com/linux/rhel7/anydesk.x86_64 0:4.0.1-1.el7
    $sudo yum localinstall anydesk-4.0.1-1.el7.x86_64.rpm

:

Posted by Elick

2018. 11. 15. 17:04

UPSERT !!?? Work/PostgreSQL2018. 11. 15. 17:04

예전에... 테이블 내의 pk 와 같으면, update 혹은 버리고... 같지 않은 데이터는 입력하는 작업을 많이 했었다. 그 때마다 차집합 구해서 입력하고, pk 와 동일하면, 어떻게 할지 결정하여 작업하고...

이제는 안그러는 것 같다... postgresql에서 는 아래처럼 기능을 제공한다.

다른 DB도 비슷한 문법이 있겠지....

create table test_conflict (a int unique, b varchar);

insert into test_conflict values (1, 'a'), (2, 'b'), (3, 'c');

select * from test_conflict;

/*

1 a

2 b

3 c

*/

with base as (

select 3, 'd' union all

select 4, 'e' union all

select 5, 'f'

)

insert into test_conflict

select *

from base

on conflict (a) do nothing;

select * from test_conflict;

/*

1 a

2 b

3 c

4 e

5 f

*/

with base as (

select 3, 'g' union all

select 4, 'h' union all

select 5, 'i'

)

insert into test_conflict

select *

from base

on conflict (a) do

update set b = excluded.b || ', ' || test_conflict.b

select * from test_conflict;

/*

1 a

2 b

3 g, c

4 h, e

5 i, f

*/

저작자표시 비영리 변경금지

:

Posted by Elick

2018. 9. 5. 21:40

postgresql pg_index, pg_indexes Work/PostgreSQL2018. 9. 5. 21:40

Postgresql 관리시 인덱스의 테이블스페이스 이동을 하고 싶은 때가 있어서...

인덱스의 테이블 스페이스 정보를 조회해 보려고...pg_index 를 열심히 뒤졌다..

select indexrelid::regclass, indrelid::regclass from pg_index;

막 이런식으로 형변환해서... 인덱스 이름 찾고, 인덱스의 테이블 이름 찾고...

한참 뒤.. 우연히 "select * from pg_indexes" 를 알게되고...

덧없는 노력이었다는 것을 알았다...

select * from pg_table; -- 이건 에러.. 이런 relation은 없다...

select * from pg_tables; -- 이건 실행..

select * from pg_index; -- 실행 된다.

select * from pg_indexes; -- 실행 된다.

아... 눈물만 흐른다..

저작자표시 비영리 변경금지

:

Posted by Elick

2018. 8. 29. 11:33

Postgresql 에서 function 확인 Work/PostgreSQL2018. 8. 29. 11:33

Postgresql에서 제공하는 pg_dump에서.. function만 받을 수 있는 기능을 못찾았다.

그래서... 살짝 구글링한 결과...

아래처럼 하는 것이 제일 편했다.

select pg_get_functiondef(oid) || ';'

from pg_proc

where pronamespace in (

select oid

from pg_namespace

where nspname in ('schema_name_1', 'schema_name_2', 'schema_name_3')

)

and proname like '%function_name_like%';

다행...

저작자표시 비영리 변경금지

:

Posted by Elick

2014. 12. 4. 16:20

tablespace location Work/PostgreSQL2014. 12. 4. 16:20

-- table 정보

select * from pg_tables;

-- tablespace 정보

select spcname, pg_tablespace_location(oid), pg_tablespace_size(oid) 

from pg_tablespace;

저작자표시 비영리 변경금지

:

Posted by Elick

2014. 7. 31. 10:26

Postgresql Pivot Work/PostgreSQL2014. 7. 31. 10:26

집계 테이블을 표현 할 때 흔히 격자형 형태로 표현하기를 원하는 경우들이 있다.

이런 경우 엑셀에서는 pivot 이라는 기능을 이용한다.

데이터베이스에서도 비슷한 상황이 존재할 수 있는데, 이를 위해서 DBMS마다 약간씩 다른 방법을 제공한다. Postgresql 의 경우는 아래은 방법을 제공한다.

SELECT a::date AS cdate,

b.desc AS TYPE,

(random() * 10000 + 1)::int AS val

FROM generate_series((now() - '10 days'::interval)::date, now()::date, '1 day'::interval) a,

(SELECT unnest(ARRAY['OSX', 'Windows', 'Linux']) AS DESC) b;

위의 쿼리 결과는 아래와 같다.

cdate	type	val
2014-07-21	OSX	3128
2014-07-21	Windows	8118
2014-07-21	Linux	6699
2014-07-22	OSX	6364
2014-07-22	Windows	4439
2014-07-22	Linux	3727
2014-07-23	OSX	6010
2014-07-23	Windows	5006
2014-07-23	Linux	2249
2014-07-24	OSX	3664
2014-07-24	Windows	2377
2014-07-24	Linux	6907
2014-07-25	OSX	6145
2014-07-25	Windows	728
2014-07-25	Linux	5920
2014-07-26	OSX	6704
2014-07-26	Windows	1331
2014-07-26	Linux	1472
2014-07-27	OSX	5668
2014-07-27	Windows	1408
2014-07-27	Linux	6162
2014-07-28	OSX	6805
2014-07-28	Windows	4762
2014-07-28	Linux	5977
2014-07-29	OSX	8917
2014-07-29	Windows	9468
2014-07-29	Linux	7261
2014-07-30	OSX	1913
2014-07-30	Windows	1495
2014-07-30	Linux	5639
2014-07-31	OSX	5195
2014-07-31	Windows	4623
2014-07-31	Linux	3756

이런 결과의 격자형 형태를 위해 아래처럼 case를 이용한 쿼리를 작성하는 방법이 일반적이다.

select cdate

, sum(case when type = 'OSX' then val else 0 end) OSX

, sum(case when type = 'Windows' then val else 0 end) Windows

, sum(case when type = 'Linux' then val else 0 end) Linux

from (

SELECT a::date AS cdate,

b.desc AS TYPE,

(random() * 10000 + 1)::int AS val

FROM generate_series((now() - '10 days'::interval)::date, now()::date, '1 day'::interval) a,

(SELECT unnest(ARRAY['OSX', 'Windows', 'Linux']) AS DESC) b

) a

group by cdate

order by 1;

결과는 아래와 같다.

cdate	osx	windows	linux
2014-07-21	7095	287	1101
2014-07-22	9741	3417	3237
2014-07-23	6321	7028	9647
2014-07-24	569	8523	7655
2014-07-25	7371	4659	2482
2014-07-26	8352	9015	559
2014-07-27	604	5552	8965
2014-07-28	79	4691	1138
2014-07-29	3355	9816	2113
2014-07-30	4707	1285	2997
2014-07-31	2029	8379	3283

Postgresql 에서 제공하는 또다른 방법은 crosstab을 이용하는 벙법이다. 쿼리는 아래와 같다.

-- crosstab 을 이용하기 위한 extension 설치

CREATE EXTENSION tablefunc;

-- Query

SELECT *

FROM crosstab(

'SELECT

a cdate,

b.desc AS type,

(random() * 10000 + 1)::int AS val

FROM generate_series((now() - ''10 days''::interval)::date, now()::date, ''1 day''::interval) a,

(SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux'']) AS DESC) b ORDER BY 1,2'

,'SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux''])'

)

AS ct(cdate date, OSX int, Windows int, Linux int);

결과는 위의 case를 이용한 Query와 동일하다.

출처 : http://www.craigkerstiens.com/2013/06/27/Pivoting-in-Postgres/

참고 : http://www.postgresql.org/docs/9.1/static/tablefunc.html

저작자표시 비영리 변경금지

:

Posted by Elick

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

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

-- 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)

-- 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

달력

« 2024/4 »

'Work/PostgreSQL'에 해당되는 글 32건

Window function 정리 Work/PostgreSQL2020. 7. 1. 13:15

plpython3u ... Work/PostgreSQL2019. 3. 28. 14:07

UPSERT !!?? Work/PostgreSQL2018. 11. 15. 17:04

postgresql pg_index, pg_indexes Work/PostgreSQL2018. 9. 5. 21:40

Postgresql 에서 function 확인 Work/PostgreSQL2018. 8. 29. 11:33

tablespace location Work/PostgreSQL2014. 12. 4. 16:20

Postgresql Pivot Work/PostgreSQL2014. 7. 31. 10:26

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

Description & Report

Create plpgsql

Execute plpgsql

Return RecordSet

Study Log

카테고리

공지사항

태그목록

최근에 올라온 글

최근에 달린 댓글

글 보관함

링크

티스토리툴바