달력

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

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

  1. 2020.07.01 Window function 정리
  2. 2019.03.28 plpython3u ...
  3. 2018.11.15 UPSERT !!??
  4. 2018.09.05 postgresql pg_index, pg_indexes
  5. 2018.08.29 Postgresql 에서 function 확인
  6. 2014.12.04 tablespace location
  7. 2014.07.31 Postgresql Pivot
  8. 2014.07.09 function을 이용한 select
2020. 7. 1. 13:15

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

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

 

 

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

 

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 update​2. 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
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