Stored-Proc to insert into PostgreSQL

It turned out Not to be so straight forward, executing a SQL Server stored-procedure from Entity Framework to insert data into Postgres (yes, I know, I said that in the meeting).

(A stored-procedure with an ordinary INSERT executed perfectly from within SSMS, using a linked server with an ODBC driver.)

After a fruitless day tweeking RPC and Distributed Transactions, my pragmatic (dreary) solution was to manually create a Postgres table, then a Postgres function to do the insert, then a stored-procedure to pass parameters to that function.

Here’s the code …

-- in postgres

-- DROP TABLE public.errorlogs;

CREATE TABLE public."ErrorLogs"
(
     "id" serial primary key,
     "edesc" char(500),
     "etype" int,
     "appid" int
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.ErrorLogs
    OWNER to postgres;

Then to create the insert function …

--in postgres
CREATE OR REPLACE FUNCTION pg_insert_new_error(edesc varchar(500), etype int, appid int) RETURNS VOID AS
$$
BEGIN
    INSERT INTO "ErrorLogs" (edesc, etype, appid) VALUES (edesc, etype, appid);
END
$$
  LANGUAGE 'plpgsql';

And finally the stored-proc …

-- in sql server
alter procedure [PostgreSQL].[SPU_InsertNewErrorLog]
	@description varchar(500),
	@messagetype int,
	@appid int
as

BEGIN

	DECLARE @cmd VARCHAR(500) = 'SELECT a.* FROM OPENQUERY(pgserver,''select pg_insert_new_error(''''' + @description + ''''', ' 
								+ CONVERT(VARCHAR(5), @messagetype) + ', ' + CONVERT(VARCHAR(5), @appid) + ')'') a'

	EXEC (@cmd)

END

GO

(Notice, I had to encapsulate the whole query and its parameters to work with OPENQUERY).