Converting Postgresql Function to SQL Server function

Question

Our company is in planning to move a Postgres database to SQL Server. This includes all tables, functions and stored procedures etc.

Here is the Postgresql syntax:

CREATE or REPLACE FUNCTION ex.on_update_integrity_reset()
    RETURNS trigger
    LANGUAGE plpgsql
AS 
$$
BEGIN 
    new."integrity_error_id" := 0 ;
    new."requires_processing" := True ;
    new."datetime_amended" := now();
    return new ;
END;
$$;

I have tried the following conversion BUT no luck I am afraid. I am hoping that the solution is quite straight forward. Any assistance will be gratefully received.

My T-SQL syntax, which isn't working:

CREATE FUNCTION ex.on_update_integrity_reset()
RETURNS TABLE
WITH SCHEMABINDING
AS
BEGIN atomic
    new."integrity_error_id" := 0 ;
    new."requires_processing" := True ;
    new."datetime_amended" := GETDATE();
    return new ;
END;

You've just dumped PostgreSQL into a SQL Server function's definition here and expected it to "just work". plgsql and T-SQL are completely different. — Thom A
– Thom A ♦, Commented Aug 24, 2022 at 14:51
Triggers are written completely differently in SQL Server. You don't write functions that return tables. You write a trigger. — user330315
– user330315, Commented Aug 24, 2022 at 14:58
Here is an answer I did about a specific use of a trigger...... called "trgKeepUpdateDateInSync_ByeByeBye" If you run the sample (which is working completely from the answer). you should get a glimpse of how a SET BASED trigger works. if you internet search for Ms sql server triggers, make sure whatever you read is considering "SET BASED"...or your (RBAR row by agonizing row) trigger will let you down. stackoverflow.com/questions/17116334/… — granadaCoder
– granadaCoder, Commented Aug 24, 2022 at 16:00

Charlieface · Accepted Answer · 2022-08-24 19:21:09Z

There are a huge amount of differences between Postgres' pgSQL and SQL Server's T-SQL, triggers not the least of them. You must learn the differences from the documentation, rather than dumping in code and expecting it to just "work".

The key things to note about triggers in SQL Server:

Triggers are directly defined on the table, rather than being separate functions which you can call.
Triggers are run per-statement not per-row, and the pseudo-tables inserted and deleted may contain multiple or zero rows.
SET NOCOUNT ON is ideal, due to problems with certain client drivers.
Do not return resultsets from triggers, instead make any updates, inserts or deletes you wish, taking into account the pseudo-tables.

Here is an example for your use case:

CREATE TRIGGER dbo.on_update_integrity_reset ON dbo.YourTable
AFTER INSERT -- what about updates???
AS
SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM inserted)
    RETURN;  -- early bail-out

UPDATE t
SET
    integrity_error_id = 0,
    requires_processing = 1,  -- boolean type is not supported
    datetime_amended = GETDATE()
FROM dbo.YourTable t
JOIN inserted i ON i.SomePrimaryKey = t.SomePrimaryKey;

Be that as it may, you probably don't actually want a trigger. Instead, you probably need DEFAULT constraints. Although there are other methods for an auto-updating datetime column.

ALTER TABLE dbo.YourTable
  ADD DEFAULT 0 FOR integrity_error_id;

ALTER TABLE dbo.YourTable
  ADD DEFAULT 1 FOR requires_processing;

ALTER TABLE dbo.YourTable
  ADD DEFAULT GETDATE() FOR datetime_amended;

Collectives™ on Stack Overflow

Converting Postgresql Function to SQL Server function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related