Dynamics Community Forum Thread Details

Hello SM-06021322-0,

Yes, it's possible to connect to and call PostgreSQL stored procedures from a Canvas App, but it requires a workaround due to the lack of a direct PostgreSQL connector in Power Apps.

Here's how you can achieve this, along with the limitations on data updates:

1. Connecting to PostgreSQL and Calling Stored Procedures:

Custom API with Azure Functions or Logic Apps:
- The most common and recommended approach is to create a custom API using Azure Functions or Logic Apps.
- This API will act as an intermediary between your Canvas App and your PostgreSQL database.
- Azure Functions:
  - Create an Azure Function that connects to your PostgreSQL database using a PostgreSQL library (like Npgsql in C# or psycopg2 in Python).
  - The Function should expose an HTTP endpoint that accepts parameters from your Canvas App.
  - Inside the Function, execute your PostgreSQL stored procedure using the provided parameters.
  - Return the results of the stored procedure as a JSON response.
- Logic Apps:
  - Create a Logic App with an HTTP trigger.
  - Use the PostgreSQL connector within Logic Apps to connect to your database and execute the stored procedure.
  - Return the results as a JSON response.
- Canvas App Connection:
  - In your Canvas App, use the "Custom connector" to connect to your Azure Function or Logic App.
  - Use the Invoke function to call your custom API and pass the necessary parameters.
  - Parse the JSON response from the API to display the results in your Canvas App.

Advantages:
- Provides a secure and reliable way to connect to PostgreSQL.
- Allows you to execute complex stored procedures.
- Provides flexibility for data transformation and manipulation.

Disadvantages:
- Requires Azure development and management.
- Adds complexity to the solution.
- Requires a custom connector.

2. Data Update Limits (Delegation and Performance):

Delegation:
- Canvas Apps have delegation limitations, meaning that not all data operations can be performed on the server-side.
- For non-delegable operations, Canvas Apps retrieve data to the client-side and perform the operations locally, which can be slow and inefficient for large datasets.

2,000 Row Limit (Default):
- By default, Canvas Apps have a 2,000 row limit for non-delegable operations.
- This means that you can only update a maximum of 2,000 rows at a time if the operation is not delegable.

Increasing the Limit (Performance Considerations):
- You can increase the data row limit to a maximum of 2,000 rows in the advanced settings of the canvas app.
- However, increasing the limit can significantly impact performance, especially on mobile devices.
- Performance is Key:
  - It is highly recommended to keep the row limit as low as possible.

Delegable Operations (Best Practice):
- To update more than 2,000 rows, you need to use delegable operations.
- This means that you need to push the processing to the server side.
- In the context of PostgreSQL, this means that the stored procedure should handle the data updates.
- Your Canvas App should only pass the necessary parameters to the stored procedure.
- The stored procedure should then perform the updates on the PostgreSQL server.

Example:
- Instead of retrieving 10,000 rows to the Canvas App and updating them locally, pass a filter condition to the stored procedure.
- The stored procedure should then update the matching rows on the PostgreSQL server.

Key Considerations:

Security:
- Secure your Azure Function or Logic App and your PostgreSQL database.
- Use appropriate authentication and authorization mechanisms.

Performance:
- Optimize your stored procedures for performance.
- Minimize the amount of data transferred between the Canvas App and the API.

Error Handling:
- Implement robust error handling in your API and Canvas App.

Testing:
- Thoroughly test your solution before deploying it to production.

In summary, while direct PostgreSQL connectivity is not available, you can use a custom API to call stored procedures. To handle large data updates, push the processing to the PostgreSQL server by using stored procedures to perform the updates.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Regards,

Daivat Vartak