PostgreSQL　ストアドファンクションサンプル

PostgreSQL ストアドファンクションサンプル

「raise info」で標準出力しています。デバッグ等に使います。Oracleでいうところのdbms_output.put_line()のように利用します。
PostgreSQLのファンクションはselectの結果セットをそのまま上位へ簡単に返却する事が出来て便利です。以下例では、returns table()で返却項目のレイアウトを定義した後、selectの前にreturn query文をつけるだけです。ビジネスロジックが複雑すぎてVIEWでは対応しきれないようなシーンでも簡単に対応できます。

create or replace function destination ()
returns table (
    item_no    integer,
    item_name  text,
    item_value text
)
as $$
begin
raise info '接続先を確認するサンプルファンクション';
return query
select 1 as item_no,'current_database' as item_name,cast(current_database() as text) as item_value
union select 2 as item_no,'version' as item_name,substring(version() from 'PostgreSQL [0-9|.]*') as item_value
union select 3 as item_no,'inet_server_addr' as item_name,cast(inet_server_addr() as text) as item_value
union select 4 as item_no,'inet_server_port' as item_name,cast(inet_server_port() as text) as item_value
union select 5 as item_no,'current_user' as item_name,cast(current_user as text) as item_value
union select 6 as item_no,'current_schema' as item_name,cast(current_schema() as text) as item_value
union select 7 as item_no,'inet_client_addr' as item_name,cast(inet_client_addr() as text) as item_value
order by item_no
;
END;
$$
LANGUAGE plpgsql;

実行結果

myposdb=# select * from destination();
myposdb=# create or replace function destination ()
myposdb-# returns table (
myposdb(#     item_no    integer,
myposdb(#     item_name  text,
myposdb(#     item_value text
myposdb(# )
myposdb-# as $$
myposdb$# begin
myposdb$# raise info '接続先を確認するサンプルファンクション';
myposdb$# return query
myposdb$# select 1 as item_no,'current_database' as item_name,cast(current_database() as text) as item_value
myposdb$# union select 2 as item_no,'version' as item_name,substring(version() from 'PostgreSQL [0-9|.]*') as item_value
myposdb$# union select 3 as item_no,'inet_server_addr' as item_name,cast(inet_server_addr() as text) as item_value
myposdb$# union select 4 as item_no,'inet_server_port' as item_name,cast(inet_server_port() as text) as item_value
myposdb$# union select 5 as item_no,'current_user' as item_name,cast(current_user as text) as item_value
myposdb$# union select 6 as item_no,'current_schema' as item_name,cast(current_schema() as text) as item_value
myposdb$# union select 7 as item_no,'inet_client_addr' as item_name,cast(inet_client_addr() as text) as item_value
myposdb$# order by item_no
myposdb$# ;
myposdb$# END;
myposdb$# $$
myposdb-# LANGUAGE plpgsql;
CREATE FUNCTION   <-- 無事エラーなく作成完了

myposdb=# select * from destination();　<-- 早速実行

INFO:  接続先を確認するサンプルファンクション  <-- 標準出力されています
 item_no |    item_name     |   item_value
---------+------------------+-----------------
       1 | current_database | myposdb
       2 | version          | PostgreSQL 13.6
       3 | inet_server_addr | 192.168.3.22/32
       4 | inet_server_port | 5432
       5 | current_user     | sooni
       6 | current_schema   | sooni
       7 | inet_client_addr | 192.168.3.10/32
(7 rows)


myposdb=#