From: <ertan.kucukoglu@1nar.com.tr>
To: <pgsql-sql@postgresql.org>
Subject: Exporting modifications to a table as XML/JSON
Date: Tue, 12 May 2020 00:17:27 +0300
Message-ID: <001401d627d9$9353fdb0$b9fbf910$@1nar.com.tr>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AdYn2WoCmUjddePhSqmNc8ExpOvPkA==
Content-Language: tr
Precedence: bulk

Hello,

I am using PostgreSQL 12.2.

Below is an SQL Server trigger I am provided and asked to convert to
PostgreSQL. I do not have access to SQL Server database nor I am provided
additional details. This is more or less dead end, but I am taking my
chances, just in case.

I briefly know PostgreSQL and using it. Unfortunately, my knowledge is not
in that detail to create a trigger. I did not have the need to do that so
far. My main use case is simple CRUD operations.

I have tried to read several different resources, I also found that script
example https://github.com/2ndQuadrant/audit-trigger to log executed
modifying SQL statements in a separate scheme. However, it did not help me
much as I am asked in verbal to log old values and new values as XML/JSON of
my choice (example script is XML).

In the end, I even failed to understand if this is possible to achieve with
PostgreSQL at all.

CREATE TRIGGER dbo.Users_Trigger ON dbo.Users
AFTER INSERT, UPDATE, DELETE
AS
  if context_info()= 0x4321 return
  SET NOCOUNT ON

-------------- Audit Controls -----------------
  DECLARE @RecID int,@RefCode varchar(10),@FldsUpdated varchar(max), @SqlStr
nvarchar(max),@OldValues xml,@NewValues xml

  SELECT @FldsUpdated = COALESCE(@FldsUpdated+',','')+COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS Field
  WHERE TABLE_NAME = 'Users' and
  (sys.fn_IsBitSetInBitmask(COLUMNS_UPDATED(),
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME,
'ColumnID')) <> 0 or not 
  Exists(Select * From inserted) )
  and not COLUMN_NAME in ('Photo','PassE')
 
  Select * Into #TempTbl From 
  (Select State=0,* From Deleted
   Union All
   Select State=1,* From Inserted
  ) X

  Declare Audit_Cursor CURSOR LOCAL FAST_FORWARD FOR
  SELECT distinct RecId,Code 
  FROM #TempTbl
  
  Open Audit_Cursor
  Fetch Next FROM Audit_Cursor INTO @RecID,@RefCode
  While @@FETCH_STATUS = 0
  Begin
    if Exists(Select * from #TempTbl Where RecID=@RecID)
    begin
      ---Old Values
      Set @SqlStr=N'Select @XmlValOut=(Select '+@FldsUpdated+' From #TempTbl
Root Where RecID=@RecIDIn and State=0 For Xml Auto)';
      Exec sp_ExecuteSql @SqlStr, N'@RecIDIn int, @XmlValOut xml
OUTPUT',@RecIDIn=@RecID,@XmlValOut=@OldValues OUTPUT
      ---New Values
      Set @SqlStr=N'Select @XmlValOut=(Select '+@FldsUpdated+' From #TempTbl
Root Where RecID=@RecIDIn and State=1 For Xml Auto)';
      Exec sp_ExecuteSql @SqlStr, N'@RecIDIn int,@XmlValOut xml
OUTPUT',@RecIDIn=@RecID,@XmlValOut=@NewValues OUTPUT
 
      Exec usp_SaveAudit @RecID,@RefCode,0,'','','Users',@OldValues,
@NewValues
    end
 
    Fetch Next FROM Audit_Cursor INTO @RecID,@RefCode
  end --while--
  Close Audit_Cursor
  DeAllocate Audit_Cursor
  Drop Table #TempTbl
------- Audit Controls ---------------
  SET NOCOUNT OFF

I do not know what usp_SaveAudit() procedure does, tough it is likely saving
XML into a table/disk file.

Any help is appreciated.

Thanks & regards,
Ertan