Introduction:
In the world of cloud computing, Azure Functions have become a popular choice for developing serverless applications. These lightweight, event-driven functions offer scalability and cost-efficiency, making them an ideal solution for various use cases. Recently we got one such requirement where we need to read PostgreSQL database view and need to update data in Dynamics 365. We used Azure function to connect with PostgreSQL, a powerful open-source relational database management system. In this blog post, we will see sample code to connect with PostgreSQL from Azure Function.

Details
Our requirement was to Fetch data from PostgreSQL daily and update Dynamics 365 CE data, so we used Azure Timer Trigger Function to connect data. Below are the high level steps to setup connection

1: Set Up Azure Timer Trigger Function App.
2. We need to Add reference to Npgsql (https://www.nuget.org/packages/Npgsql/)

 string connectionstring = string.Empty;
 string viewquery = string.Empty;

 string host = Environment.GetEnvironmentVariable("Host");
 string port = Environment.GetEnvironmentVariable("Port");
 string database = Environment.GetEnvironmentVariable("Database");
 string username = Environment.GetEnvironmentVariable("User");
 string password = Environment.GetEnvironmentVariable("Password");
 string timeout = Environment.GetEnvironmentVariable("Timeout");

            //prepare connection string
            connectionstring = "Host=" + host + ";Port=" + port + ";Database=" + database + ";Username=" + username + ";Password=" + password + ";Timeout=" + timeout;

            viewquery = "select * from <ViewName>";
 using (NpgsqlConnection connection = new NpgsqlConnection(connectionstring))
            {
                try
                {
                    logger.LogInformation("Opening connection to Postgres...");
                    connection.Open();
                    logger.LogInformation("Connected to Host:" + host);
                    logger.LogInformation("Feteching data from <ViewName>");

                    using (var command = new NpgsqlCommand(viewquery, connection))
                    {

                        using (var reader = command.ExecuteReader())
                        {
						//PostgreSQLView is class where we have properties that we want to fetch from view
                         List<PostgreSQLView> list = new List<PostgreSQLView>();

                            while (reader.Read())
                            {
                                PostgreSQLView tbl=new PostgreSQLView();
                                tbl.id= reader.GetInt32(0).ToString();
                                tbl.name = reader.GetString(1).ToString();
                                list.Add(tbl);
                              
                            }
                        }
                    }

                    connection.Close();
                  }
                catch (Exception ex)
                {
                    logger.LogInformation(ex.Message);

                }



            }

Summary:
Once we have data from view we can connect to Dynamics 365 and update data based on the identifiers. You can check our earlier blogs for Azure Function

Developing Scheduler for Dynamics 365 CE using Azure Function Part 1
Developing Scheduler for Dynamics 365 CE using Azure Function Part 2
Using Configuration with Azure Function