1

It is know that SQL Server offers some extensibility mechanisms like user-defined functions, stored procedures, triggers...

I have C# code that runs in SQL Server 2008 (it is a stored procedure that is deployed in the DBMS using Visual Studio 2008, and then a exec dbo.SP_Analysis 'MyTable','Colum1,Column2',300,0 command is used to get the results in SQL Server 2008) defined as:

using System;
using System.Collections.Generic;
using System.Text;
using System.Security;
using System.Runtime.InteropServices;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{              
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void SP_Analysis(
     SqlString table, [SqlFacet(MaxSize = -1)] SqlString columns,
     int Nocustomers,
     Boolean result)
  {
     String query = "";

     using (SqlConnection conn = new SqlConnection("context connection=true"))
     {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        query = "SELECT " + (String)columns + " FROM " + (String)table + ";";
        cmd.CommandText = query;
        SqlDataReader reader = cmd.ExecuteReader();
        MyObject _myObject = new Object(Nocustomers);
        while (reader.Read())
           _myObject.read(reader);
        reader.Close();        
        conn.Close();
      } //END SqlConnection conn 
  }        
}

class MyObject
{
   int NoCustomers; 
   //some definitions

  public MyObject(int NoCustomers)
  {
     this.NoCustomers = NoCustomers;
     //some stuff
  }

   public void read(SqlDataReader reader)
   {
     Object[] record = new Object[NoCustomers];
     reader.GetValues(record);
     //more stuff
   }
}

It is clear that I am using .NET c# available language and then deploy it to create a stored procedure in SQL Server 2008, via Visual Studio 2008.

Now my question is what is the analog to all this in Postgres?

  1. What language is available to extend the functionality of Postgres?
  2. What is the analog of Visual Studio to have the possibility to deploy a UDF in Postgres?
  3. What is the analog of exec dbo.UDF in Postgres?

I was reading that Postgres uses C language to define UDFs maybe C++ but the others I have no idea...

2 Answers 2

5
  1. PL/Python, PL/Perl, PL/Java, PL/TCL, PL/V8 (JavaScript) and C procedures. For most jobs, though, you just use PL/PgSQL, an extended version of SQL for procedures.

  2. There isn't one, really. PgAdmin-III sort-of. You just execute SQL commands to create functions, or in the case of C procedures, you use a Makefile with PGXS.

  3. Dunno, what does that do in SQL Server? Docs link? If it's just "call this procedure" then SELECT my_function();.

Sign up to request clarification or add additional context in comments.

2 Comments

yeah, the exec executes the stored procedure in SQL, so it is a way of using the power of C# inside SQL Server, but could you explain a little more the 2 answer?
@cMinor Documentation links added.
1

PostgreSQL supports. Number of stored procedure languages including A PL/SQL flavor, one based on Perl, one on Python and one on Tcl.

PL/pgSQL is a block structured language with SQL embedded directly in it and is very similar to Oracle's PL/SQL.

1 Comment

Is it possible to program a storedprocedure or a udf in C, or even C++ languages?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.