PostgreSQL
PostgreSQLは、クラウドまたはオンプレミスでホストされるオープンソースのオブジェクトリレーショナルデータベース管理システムです。
サポートされているエディション
PostgreSQLのすべてのリリースがサポートされています。
WorkatoでPostgreSQLに接続する方法
PostgreSQLコネクターは、PostgreSQLで認証するためにベーシック認証を使用します。 
| フィールド | 説明 |
|---|---|
| コネクション名 | このPostgreSQLコネクションに、接続先のPostgreSQLインスタンスを識別する一意の名前を付けます。 |
| オンプレミスセキュアエージェント | データベースが直接コネクションを許可しないネットワークで実行されている場合は、オンプレミスエージェントを選択します。 接続を試みる前に、有効なオンプレミスエージェントがあることを確認してください。 詳細については、オンプレミス接続ガイドを参照してください。 |
| ユーザー名 | PostgreSQLに接続するためのユーザー名。 |
| パスワード | PostgreSQLに接続するためのパスワード。 |
| ホスト | ホストされているサーバーのURL。 |
| ポート | サーバーが実行されているポート番号。通常は5432です。 |
| データベース名 | 接続するPostgreSQLデータベースの名前。 |
| スキーマ | 接続するPostgreSQLデータベース内のスキーマ名。 デフォルトはpublicです。 |
接続に必要な権限
最低限、データベースユーザーアカウントには、コネクションで指定されたデータベースに対するSELECT権限を付与する必要があります。
新しいデータベースユーザーworkatoを使用してPostgreSQLインスタンスに接続する場合は、次のクエリ例を使用できます。
まず、Workatoでの連携ユースケース専用の新しいユーザーを作成します。
CREATE USER workato WITH PASSWORD 'password';次のステップでは、スキーマ内のcustomerテーブルへのアクセス権を付与します。 この例では、SELECTおよびINSERT権限のみを付与します。
GRANT SELECT,INSERT ON customer TO workato;最後に、このユーザーに必要な権限があることを確認します。 すべての権限付与を確認するクエリを実行します。
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'workato';これにより、WorkatoでPostgreSQLコネクションを作成するための次の最小権限が返されます。
+---------+------------+----------------+
| grantee | table_name | privilege_type |
+---------+------------+----------------+
| workato | customer | SELECT |
+---------+------------+----------------+
| workato | customer | INSERT |
+---------+------------+----------------+
2 rows in set (0.26 sec)PostgreSQLコネクターの使用
テーブル、ビュー、ストアドプロシージャ
PostgreSQLコネクターは、すべてのテーブル、ビュー、ストアドプロシージャで機能します。 これらは各トリガー/アクションのピックリストで使用できます。または、正確な名前を指定できます。
ピックリストからテーブル/ビューを選択
テキストフィールドに正確なテーブル/ビュー名を指定
PostgreSQLでは、引用符で囲まれていない識別子は大文字と小文字を区別しません。 そのため、
SELECT ID FROM USERSは次と同等です
SELECT ID FROM usersただし、引用符で囲まれた識別子は大文字と小文字が区別されます。 したがって、
SELECT ID FROM "USERS"は次と同等ではありません
SELECT ID FROM "users"単一行と行のバッチ
PostgreSQLコネクターは、単一行またはバッチでデータベースを読み書きできます。 バッチトリガー/アクションを使用する場合は、操作するバッチサイズを指定する必要があります。 バッチサイズには1～100の任意の数値を指定でき、最大バッチサイズは100です。
バッチトリガー入力
入力フィールドの違いに加えて、これら2種類の操作の出力にも違いがあります。 行を1つずつ処理するトリガーには、その単一行のデータをマッピングできる出力データツリーがあります。
単一行出力
ただし、行をバッチで処理するトリガーは、それらを行の配列として出力します。 行データピルは、出力がそのバッチ内の各行のデータを含むリストであることを示します。
バッチトリガー出力
そのため、バッチトリガー/アクションの出力は異なる方法で処理する必要があります。 このレシピでは、usersテーブルの新規行に対してバッチトリガーを使用します。 トリガーの出力はSalesforce一括作成アクションで使用され、行データピルをソースリストにマッピングする必要があります。
バッチトリガー出力の使用
WHERE条件
この入力フィールドは、アクションを実行する行をフィルタリングして識別するために使用されます。 次の方法で複数のトリガーおよびアクションで使用されます:
- トリガーで取得する行をフィルタリング
- Select rowsアクションで行をフィルタリング
- Delete rowsアクションで削除する行をフィルタリング
TIP
以下の例は、ユーザー入力でWHERE条件を直接使用する方法を示しています。 セキュリティを強化するには、SQLインジェクションを防ぐためにパラメーター付きのWHERE条件を使用します。 詳細はこちら
この句は、各リクエストでWHEREステートメントとして使用されます。 これは基本的なSQL構文に従う必要があります。 PostgreSQLステートメントを記述するためのルールの完全な一覧については、このPostgreSQLドキュメントを参照してください。
演算子
| Operator | 説明 | 例 |
|---|---|---|
| = | 等しい | WHERE ID = 445 |
| != <> | 等しくない | WHERE ID <> 445 |
| > >= | より大きい 以上 | WHERE PRICE > 10000 |
| < <= | より小さい 以下 | WHERE PRICE > 10000 |
| IN(...) | 値のリスト | WHERE ID IN(445, 600, 783) |
| LIKE | ワイルドカード文字（%および_）を使用したパターン照合 | WHERE EMAIL LIKE '%@workato.com' |
| BETWEEN | 範囲を指定して値を取得 | WHERE ID BETWEEN 445 AND 783 |
| IS NULL IS NOT NULL | NULL値のチェック NULL以外の値のチェック | WHERE NAME IS NOT NULL |
単純なステートメント
文字列値は一重引用符（''）で囲む必要があり、使用する列はテーブルに存在している必要があります。
単一列の値に基づいて行をフィルタリングする単純なWHERE条件は次のようになります。
role = 'admin'Select rowsアクションで使用すると、このWHERE条件はrole列に値'admin'があるすべての行を返します。 入力では、データピルを一重引用符で囲むことを忘れないでください。
WHERE条件でのデータピルの使用
スペースを含む列名は二重引用符（""）で囲む必要があります。 たとえば、currency codeを識別子として使用するには、引用符で囲む必要があります。 引用符で囲まれた識別子はすべて大文字と小文字が区別されることに注意してください。
"created date" = '2020-05-06'レシピでは、各値/識別子に適切な引用符を使用してください。
囲まれた識別子を含むWHERE条件
複雑なステートメント
WHERE条件にはサブクエリを含めることもできます。 次のクエリはusersテーブルで使用できます。
id in (select user_id from tickets where priority = 2)Delete rowsアクションで使用すると、ticketsテーブル内の関連行の少なくとも1つでpriority列の値が2であるusersテーブルのすべての行が削除されます。
サブクエリを含むWHERE条件でのデータピルの使用
パラメーターの使用
パラメーターは、SQLインジェクションに対する追加のセキュリティレイヤーを加えるために、WHERE条件と組み合わせて使用します。 WHERE条件でパラメーターを使用するには、まず入力でバインド変数を宣言する必要があります。 バインドパラメーターは、変数名の前に:を付けた:bind_variable形式で宣言する必要があります。 これが完了したら、すぐ下のセクションで、指定した正確な名前を使用してパラメーターを宣言します。
TIP
バインド変数は列名ではなく、列値の代わりとしてのみ使用してください。
バインド変数を含むWHERE条件
任意の数のバインド変数を指定でき、それぞれに一意の名前を付ける必要があります。 一重引用符（'）、二重引用符（"）、角括弧（[]）内の:を無視することで、バインド変数を列名および静的値と区別します。
一意キー
すべてのトリガーと一部のアクションでは、これは必須入力です。 この選択された列の値は、選択したテーブル内の行を一意に識別するために使用されます。
そのため、選択した列の値は一意である必要があります。 通常、この列はテーブルの主キーです（例: ID）。
トリガーで使用する場合、この列は増分である必要があります。 この制約が必要なのは、トリガーがこの列の値を使用して新しい行を検索するためです。 各ポーリングで、トリガーは前回の最大値より大きい一意キー値を持つ行をクエリします。
この動作を説明するために、簡単な例を使用しましょう。 テーブルから行を処理するNew rowトリガーがあるとします。 このトリガーに設定されている一意キーはIDです。 最後に処理された行のID値は100です。 次のポーリングでは、トリガーは新しい行を検索する条件としてID >= 101を使用します。
一意キーとして使用するために選択された列にインデックスが設定されている場合、トリガーのパフォーマンスを向上させることができます。
ソート列
これはNew/updated rowトリガーでは必須です。 この選択された列の値は、更新された行を識別するために使用されます。
行が更新されると、一意キーの値は同じままです。 ただし、最終更新時刻を反映するようにタイムスタンプが更新されている必要があります。 このロジックに従って、Workatoはこの列の値を、選択されたUnique key列の値とともに追跡します。 Sort column値の変更が検出されると、更新行イベントが記録され、トリガーによって処理されます。
PostgreSQLでは、timestampおよびtimestamp with time zone列タイプのみ使用できます。
パラメーター制限
PostgreSQLは、単一のSQLステートメントで最大65,535個のバインドパラメーターをサポートします。 この制限を超えると、クエリが失敗する場合があります。
これを回避するには、バッチサイズを小さくする、クエリをより小さい部分に分割する、配列バインドを使用する、またはCOPYを使用してデータを一括読み込みします。
Workatoでは、安定性を確保するために、このパラメーター制限内で動作するようレシピとSQL操作を構成することをお勧めします。
Last updated: