Amazon Redshift: 登録済みUDFの一覧情報を取得する

Amazon Redshiftで良く使いそうな便利系SQL

#ビッグデータ

#Redshift UDF

#Amazon Redshift

#AWS

しんや

2015.12.02

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

下記エントリにてAmazon Redshiftに於けるUDF(User Defined Functions: ユーザー定義の独自関数)の実際の作成手順についてご紹介しました。このUDFはPythonで必要な処理コードを書けていれば、その内容をSQLを通じてRedshiftクラスタに関数として登録する事が出来、Amazon Redshiftのシステム側で用意されている関数と同じような扱いでユーザー独自で作成した関数を使う事が出来ます。

Amazon Redshift: UDFを使って日付・時刻の文字列をタイムスタンプ型に変換してみる｜ Developers.IO

『無いものをUDFを使って作る』という側面があると思われるので、当然ながら利用者の皆さんにも『こういう関数を作りましたので良ければ使ってください』という事を伝える為に『UDFの一覧』を用意する必要があるのかなと思い、その際に活用出来る情報について言及したのが当エントリの内容となります。

登録済UDFの情報の参照先について

pg_procテーブルに、作成したUDFの一覧に関する情報があります。カタログテーブルの情報として、Amazon Redshiftが準拠しているPostgreSQLの当該テーブルの仕様で情報を参照する事が可能です。

テーブル定義はこんな感じ。

# \d pg_proc
;      Table "pg_catalog.pg_proc"
    Column    |   Type    | Modifiers 
--------------+-----------+-----------
 proname      | name      | not null
 pronamespace | oid       | not null
 proowner     | integer   | not null
 prolang      | oid       | not null
 proisagg     | boolean   | not null
 prosecdef    | boolean   | not null
 proisstrict  | boolean   | not null
 proretset    | boolean   | not null
 provolatile  | "char"    | not null
 pronargs     | smallint  | not null
 prorettype   | oid       | not null
 proargtypes  | oidvector | not null
 proargnames  | text[]    | 
 prosrc       | text      | 
 probin       | bytea     | 
 proacl       | aclitem[] | 
Indexes:
    "pg_proc_oid_index" UNIQUE, btree (oid)
    "pg_proc_proname_args_nsp_index" UNIQUE, btree (proname, pronargs, proargtypes, pronamespace)

そして実行結果は以下の通り。

# select * from pg_proc where proname ilike 'cm%';
     proname      | pronamespace | proowner | prolang | proisagg | prosecdef | proisstrict | proretset | provolatile | pronargs | prorettype |     proargtypes     |            proargnames             |                     prosrc                      |     probin     | proacl 
------------------+--------------+----------+---------+----------+-----------+-------------+-----------+-------------+----------+------------+---------------------+------------------------------------+-------------------------------------------------+----------------+--------
 cm_to_timestamp  |         2200 |      100 |  100057 | f        | f         | f           | f         | s           |        4 |       1114 | 1043 1043 1043 1043 | {datestr,delimchar,timestr,format} |                                                +| -1,-1,-1,-1:-1 | 
                  |              |          |         |          |           |             |           |             |          |            |                     |                                    | from datetime import datetime                  +|                | 
                  |              |          |         |          |           |             |           |             |          |            |                     |                                    | timestamp_str = datestr + delimchar + timestr  +|                | 
                  |              |          |         |          |           |             |           |             |          |            |                     |                                    | return datetime.strptime(timestamp_str, format)+|                | 
                  |              |          |         |          |           |             |           |             |          |            |                     |                                    |                                                 |                | 
 cm_to_timestamp2 |         2200 |      100 |  100057 | f        | f         | f           | f         | s           |        4 |       1114 | 1043 1043 1043 1043 | {datestr,delimchar,timestr,format} |                                                +| -1,-1,-1,-1:-1 | 
                  |              |          |         |          |           |             |           |             |          |            |                     |                                    | from datetime import datetime                  +|                | 
                  |              |          |         |          |           |             |           |             |          |            |                     |                                    | timestamp_str = datestr + delimchar + timestr  +|                | 
                  |              |          |         |          |           |             |           |             |          |            |                     |                                    | return datetime.strptime(timestamp_str, format)+|                | 
                  |              |          |         |          |           |             |           |             |          |            |                     |                                    |                                                 |                | 
(2 rows)

引数の型(proargtypes)の内容については、pg_typeテーブルに目ぼしいものが参照出来る様ですので宜しければご参考に。

# SELECT
    typname,
    typelem
  FROM
    pg_type
  WHERE
    typelem != 0 AND
    ((typname ilike '%char%') OR
     (typname ilike '%int%') OR
     (typname ilike '%date%') OR
     (typname ilike '%timestamp%')
    );
    
   typname    | typelem 
--------------+---------
 int2vector   |      21
 point        |     701
 _char        |      18
 _int2        |      21
 _int2vector  |      22
 _int4        |      23
 _bpchar      |    1042
 _varchar     |    1043
 _int8        |      20
 _point       |     600
 _tinterval   |     704
 _timestamp   |    1114
 _date        |    1082
 _timestamptz |    1184
 _interval    |    1186
(15 rows)