ablog

不器用で落着きのない技術者のメモ

Redshift で特定スキーマの持つファンクションを調べる

select a.proname, a.proacl, a.pronamespace, b.nspname 
from pg_proc a, pg_namespace b 
where b.nspname = 'aws_teradata_ext' 
and a.pronamespace = b.oid 
order by a.proname;
  • 実行結果
            proname            |              proacl               | pronamespace |     nspname
-------------------------------+-----------------------------------+--------------+------------------
 ascii                         | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 bcl_monthnumber_of_calendar   | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 bcl_monthnumber_of_quarter    | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 bcl_monthnumber_of_year       | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 bcl_quarternumber_of_calendar | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 bcl_quarternumber_of_year     | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 bcl_weeknumber_of_calendar    | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 bcl_weeknumber_of_year        | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 bcl_yearnumber_of_calendar    | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 bitand                        | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 bitnot                        | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 bitor                         | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 bitxor                        | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 cast_c2c                      | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 cast_c2f                      | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 cast_c2i                      | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 cast_c2v                      | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 day_of_month                  | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 day_of_week                   | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 day_of_year                   | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 daynumber_of_calendar         | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 daynumber_of_month            | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 daynumber_of_week             | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 daynumber_of_year             | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 dayoccurrence_of_month        | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 instr                         |                                   |       101595 | aws_teradata_ext
 instr                         | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 month_of_calendar             | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 month_of_quarter              | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 month_of_year                 | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 monthnumber_of_calendar       | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 monthnumber_of_quarter        | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 monthnumber_of_year           | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 next                          | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 ngram                         |                                   |       101595 | aws_teradata_ext
 ngram                         | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 nvp                           | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 overlaps                      | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 prior                         | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 quarter_of_calendar           | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 quarter_of_year               | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 quarternumber_of_calendar     | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 quarternumber_of_year         | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 round                         |                                   |       101595 | aws_teradata_ext
 round                         | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 shiftleft                     | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 shiftright                    | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 soundex                       | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 strtok                        | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_friday                     | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_monday                     | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_month_begin                | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_month_end                  | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_quarter_begin              | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_quarter_end                | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_saturday                   | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_sunday                     | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_thursday                   | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_toesday                    | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_tuesday                    | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_wednesday                  | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_week_begin                 | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_week_end                   | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_year_begin                 | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 td_year_end                   | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 trunc                         |                                   |       101595 | aws_teradata_ext
 trunc                         | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 week_of_calendar              | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 week_of_month                 | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 week_of_year                  | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 weekday_of_month              | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 weeknumber_of_calendar        | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 weeknumber_of_month           | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 weeknumber_of_quarter         | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 weeknumber_of_year            | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 year_of_calendar              | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
 yearnumber_of_calendar        | {=X/awsuser,awsuser=X/awsuser} |       101595 | aws_teradata_ext
(77 rows)

参考

OIDは主にPostgreSQLのシステムテーブル(システムカタログ)で参照することができます。ただし、OIDは隠し列となっており、psqlの¥dコマンドなどでシステムテーブルの定義を見てもOIDは見つかりません。確認する際はSELECT の参照列に oid を記載します。

オブジェクト識別子を活用する | Let's POSTGRES