AZURE DATA FACTORY STORED PROCEDURE WITH POSTGRESQL SERVER

Question

Dear Microsoft Support,

I have created a stored procedure in a PostgreSQL server and established a linked service for that server in Azure Data Factory (ADF). My goal is to execute this stored procedure within a pipeline in ADF. However, when I attempt to connect to the stored procedure, my linked service does not appear as an option.

Upon seeking guidance from Copilot, I learned that ADF does not currently support stored procedures in PostgreSQL. Could you please provide guidance on how I can execute my PostgreSQL stored procedure within an ADF pipeline?

Answer 1

Hi @Sai Varma Eswararaju

Thank you for reaching out to the community forum with your query.

I understand that you are trying to execute a stored procedure in a PostgreSQL server within an Azure Data Factory (ADF) pipeline, but your linked service does not appear as an option. Unfortunately, ADF does not currently support stored procedures in PostgreSQL.

However, there are a few workarounds that you can try to execute your PostgreSQL stored procedure within an ADF pipeline:

1. Use an Azure Function to execute the stored procedure: You can create an Azure Function that connects to your PostgreSQL server and executes the stored procedure. Then, you can call this Azure Function from your ADF pipeline using the Azure Function activity.
2. Use a custom activity: You can create a custom activity in ADF that connects to your PostgreSQL server and executes the stored procedure. You can use a .NET custom activity or a Python custom activity to achieve this.
3. Use a third-party connector: You can use a third-party connector like the CData PostgreSQL Connector for ADF to connect to your PostgreSQL server and execute the stored procedure.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Hi,
It's correct that ADF does not currently support the direct execution of stored procedures in PostgreSQL. However, we can execute PostgreSQL functions using the Lookup Activity in ADF. Here's how:

Leverage PostgreSQL Functions: Create a PostgreSQL function that encapsulates the required logic. Unlike stored procedures, functions can be invoked directly using a query.

1. Executing the Function: In the Lookup Activity, write a query in the format:
   1. SELECT your_function(arguments);
      This syntax ensures the function is executed, and any returned value is captured by the Lookup Activity. This syntax ensures the function is executed, and any returned value is captured by the Lookup Activity.