How to access PostgreSql from MS Sql server store procs.

Don't you have a bad idea or comment become weird and at the same time awesome?
Well today after a long meeting about why would be a good idea to used our postgres database directly using reporting services the reporting team kind of refused to go into the change, so, I came with a silly comment "why not just create several CRL's in ms sql server using the NPGSQL drivers and let the reporting team used those instead." the room got silence and now that's became my new POC project. So here we go a silly comment now became the solution. Next time I just keep my silly comments to myself.
Cheers,

Send Sql server agent job notifications Via Sql Script

Today I have to add some notifications about the status of specific sql server agent jobs, specially if the job fail and been a lazy programmer I create some metadata tables with specific messages and jobs, but I still want to share the core code used to send this message. Because this is set on a ssis sql task we only care to see a message if the job fails so, top 50 should be enough history to know something is wrong :-) .

Cheers.

declare @sqlcmd varchar(2000) = 'set transaction isolation level read uncommitted;

set nocount on; declare @today varchar(8) = convert (varchar(8),getdate(),112)

SELECT  top 50  j.name JobName ,

        h.step_name StepName ,

        CONVERT(CHAR(10), CAST(STR(h.run_date, 8, 0) AS DATETIME), 111) RunDate ,

        STUFF(STUFF(RIGHT(''000000'' + CAST (h.run_time AS VARCHAR(6)), 6), 5, 0,

                    '':''), 3, 0, '':'') RunTime ,

        h.run_duration StepDuration ,

        CASE h.run_status

          WHEN 0 THEN ''failed''

          WHEN 1 THEN ''Succeded''

          WHEN 2 THEN ''Retry''

          WHEN 3 THEN ''Cancelled''

          WHEN 4 THEN ''In Progress''

        END AS ExecutionStatus ,

        h.message MessageGenerated

FROM    sysjobhistory h

        INNER JOIN sysjobs j ON j.job_id = h.job_id

where j.name = ''Job_name Here''

and h.run_status in(1,0,3) and h.run_date >= @today

ORDER BY h.instance_id desc',

@today varchar(50) = 'NameOfAttachFileHere';

set @today = @today+'_'+ replace (replace(replace(convert(varchar(25),

getdate(),

120),

'-',

''),

':',

''),

' ',

'') +'.csv';

EXEC msdb.dbo.sp_send_dbmail

    @recipients = 'EmailRecipientsHere',

               @body= 'EmailBodyHere',

               @execute_query_database = 'msdb',

    @query = @sqlcmd,

    @subject = 'Subject Alert Here',

               @attach_query_result_as_file = 1,

               @query_result_separator = ',

',

               @query_result_no_padding = 1,

               @query_attachment_filename = @today;

Building dynamic queries to extract regular data or Json datatype from postgreSQL

As always a lazy programmer, today my task is to get the update from a set of tables on postgres while excluding an specific pattern plus the scripts is run twice per day so I need to capture changes in a 12 hour interval.
MMMMM>..... How i can do this and get it done quickly :-)
.........agony, agony, agony.. I don't want to spend all day doing this....So, here is the solution to build the scripts quick and dirty:
select 
  'Do $$ declare today timestamp = current_timestamp - interval''12 hours''; begin Create Temp table temp_'||table_name||' on commit drop as SELECT '||string_agg(column_name,',')||' FROM '||table_schema||'.'||table_name||' where modified_date >= today; end $$; SELECT '||string_agg(column_name,',')||' FROM temp_'||table_name||';'
from 
 information_schema.columns 
where 
table_schema = 'tableSchemaHere'
and table_name !~* 'StringPartternHere'
and is_updatable = 'YES'

group by table_schema,table_name;

Ah... More agony now I need to extract data from the audit tables that uses the json datatype...
mmm.... what to do... just change the above script just a little to get data from json
select Table_Name,
'Do $$ declare today timestamp = current_timestamp - interval''12 hours'';begin  Create Temp table temp_'||table_name||' on commit drop as SELECT action,'||cast (string_agg('new_object_data->'||replace (initcap(replace(''''||column_name||'''','_',' ')),' ','')||
' AS '||replace(initcap(replace(column_name,'_',' ')),' ',''),',')as varchar(2000))||
' FROM audit.audit_history Where (action = '||'''Update'') AND object_type = '''||replace(initcap(replace(Table_Name,'_',' ')),' ','')||''' 
AND (Created_date <= today) UNION ALL SELECT action,'||cast (string_agg('original_object_data->'||replace (initcap(replace(''''||column_name||'''','_',' ')),' ','')||
' AS '||replace(initcap(replace(column_name,'_',' ')),' ',''),',')as varchar(2000))||
' FROM audit.audit_history Where (action = '||'''Delete'') AND object_type = '''||replace(initcap(replace(Table_Name,'_',' ')),' ','')||''' AND (Created_date <= today);END $$; Select * from temp_'||Replace(Table_Name,'_','')
from 
 information_schema.columns
where 
   table_schema = 'schema_here'
   
group by table_name
order by 1

quick and dirty just the way I like... Cheers.