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.