You can configure an SQL transformation in a PostgreSQL mapping to process SQL queries, stored procedures, and stored functions in PostgreSQL.
When you add an SQL transformation to the mapping, on the SQL tab, you define the database connection and the type of SQL that the transformation processes.
You can use an SQL transformation to process the following types of SQL statements:
Stored procedure or stored function
You can configure an SQL transformation to call a stored procedure or function in PostgreSQL. The stored procedure or function must exist in the PostgreSQL database before you create the SQL transformation.
When the SQL transformation processes a stored procedure or function, it passes input parameters to the stored procedure or function. The stored procedure returns the values to the output fields of the transformation. A stored function is similar to a stored procedure, except that a function returns a single value.
SQL query
You can configure an SQL transformation to process an entered query that you define in the SQL editor. Do not use more than one SQL query in an SQL transformation.
The SQL transformation processes the query and returns the rows. The SQL transformation also returns any errors that occur from the underlying database or if there is an error in the user syntax.
For more information about SQL queries, stored procedures, and stored functions, see Transformations in the Data Integration documentation.