PostgreSQL User Defined Functions (2022-10-14)

SQLServerCentral Newsletter for October 14, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [PostgreSQL User Defined Functions]( - [Finding the Version of a Flyway-managed Database Using SQL]( - [Steps to Quickly Configure an AWS RDS SQL Server instance]( - [From the SQL Server Central Blogs - DevOps From Redgate]( - [From the SQL Server Central Blogs - How to Calculate Compound Rate in Power BI]( Question of the Day - [Disabling the CEIP]( The Voice of the DBA  Daily Coping Tip Look for the good in people around you today I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Evolving Yourself I ran across [an interview with Big Boi](, a music musician and producer. It's an interesting look at someone that has had a lot of success and continues to work, mostly because he enjoys it. I'm sure the compensation matters somewhat, but that doesn't appear to be his driving force. Whether you like his music, it's an interesting look at someone who lives by his creativity. As I read through, a lot of the themes and quotes could be attributed to software developers. We constantly need to learn more and evolve how we write code if we want to be more successful. We have ideas, and often the code we write today isn't the way we'd write code to accomplish the same task last year. At least, I hope that we would learn, grow, and write new code. Certainly many of us are compensated for what we know, and often we can write the same code over and over to handle similar situations. This might be more true for people that work in Operations and manage systems more so than developers, but perhaps not. Even if we write similar code, we often do it more efficiently or incorporate small enhancements that help us. Perhaps better error handling, perhaps anticipating places where our code might fail or falter. That might be something we hadn't considered in the last. Not everyone wants to be a star or famous, whether musician or software developers. Lots of us are happy to do a good job, leaning more heavily on past experience than evolving to tackle new challenges and produce a better end result. There is nothing wrong with having a career that accomplishes what is asked of us and nothing more. However the world presents lots of opportunities if you look. Often those come from exploration, experimentation, and making an effort to grow. Having hobby software projects might be akin to writing music and creating new "beats." Learning from what others have done, studying their techniques and patterns can help is improve our own learning. The more I try to be more creative, the more I understand that copying others is an important way to learn. From there I can start to adjust and alter the final product to be my own. Study your craft, improve what you know and learn more. Practice and improve, and you might be surprised where your career can go. Plus, it's more interesting when you're engaged. Of course, as noted at the end, learn to take breaks, relax, and enjoy life outside of your work. Finding a balance there is important to ensure you work to live, not the other way around. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [PostgreSQL User Defined Functions]( Shivayan Mukherjee from SQLServerCentral Learn how to create user defined functions in PostgreSQL. [External Article]( [Finding the Version of a Flyway-managed Database Using SQL]( Additional Articles from Redgate Maintaining a version of a database opens a lot of possibilities, especially if an automated process can easily grab the current version, at runtime, using just SQL. You might, for example, have a routine that is only appropriate after a particular version. It is also very handy to be able to associate entries in an event log or bug report with the database version. No more desultory quests, when dealing with support issues, or when bug fixing, to find which database version was running when the bug happened. [External Article]( [Steps to Quickly Configure an AWS RDS SQL Server instance]( Additional Articles from MSSQLTips.com Learn how to configure and deploy a new Amazon AWS RDS SQL Server instance with this step-by-step article. [Blog Post]( From the SQL Server Central Blogs - [DevOps From Redgate]( Grant Fritchey from The Scary DBA In a few weeks at the PASS Data Community Summit, I’ll be joining several other Redgaters to put on an all-day precon where we take you on a database... [Blog Post]( From the SQL Server Central Blogs - [How to Calculate Compound Rate in Power BI]( Koen Verbeeck from Koen Verbeeck Recently I had an interesting use case where I had to a compound calculation in Power BI. You can compound an interest rate for example, where you get a...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Disabling the CEIP Under what situations can I disable the CEIP service? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Checking Digital Signatures I have some character data in a table that is signed by a certificate. The signature is also stored in a table. How can I check that the data hasn't changed? Answer: Run VERIFYSIGNEDBYCERT() with the cert, the data, and the signature values Explanation: There is a way to do this. The VERIFYUSIGNEDBYCERT() function will check data against a signature, given the cert. Ref: VERIFYUSIGNEDBYCERT() - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [SQL Server Windows NT - 64 bit" in Task Manager: CPU high]( -  Hello ,  The SQL Server Windows NT - 64 bit" in Task Manager: CPU high 56,961 MB , the overall server CPU is now 95 % kindly advise the solution. SQL Server 2016 - Development and T-SQL [Create SFX Archive - 7zip in SSIS?]( - Hi everyone, I have created some XML files via SSIS package and compressed them as well using 7-zip. There is an option in 7-zip to Create SFX Archive which converts the .7z file to .exe How can I achieve this from SSIS? [Grant user permissions to a view but the base tables live in another database]( - I have two databases on the same server. I have a service account I want to have access to SELECT off some view in the "Staging" database. The view calls on tables in our Finance database. If I run the SELECT as myself or other "normal" users it works fine because those accounts have access […] [The ORDER BY clause is invalid in views]( - Hi, I'm trying to run the below SQL code but I'm getting this error: An error occurred while checking the query syntax. Errors: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. I'm still learning SQL so any […] Administration - SQL Server 2014 [Restore Stumper - Metadata error]( - I am attempting to restore a database from a server in one domain to another. The Source server is 2014 (SP3-CU4-GDR) (KB5014164) - 12.0.6439.10 (X64) Dev edition and the destination server is the same, the only difference being that the source server has the DB as part of an AG and the destination has no […] SQL Server 2012 - T-SQL [find missing records]( - I want to find all customers that have a certain prlist, but within that same customer grouping that don't contain records for prlist like (cb% or ab%) In the example provided Customer 123 matches and would be excluded from my output list. Customer 347 has the pli-(prlist), but doesn't have the others like (cb% or […] SQL Server 2019 - Administration [Crashed OS, now SQL will not accept inbound connections]( - Hi, We have a VM running SQL. This VM crashed and since coming back up we cannot connect to the SQL instance running on it from outside of the VM itself. SSMS works fine when connecting to the local instance. Also, the SQL Server service will now only start if the domain account used is […] [Change tracking - Date a row changed]( - We have change tracking enabled on one of our databases and I am looking for a query where I could get the timestamp/date on when a row changed. Any help is greatly appreciated. Thanks a lot. [Data replication tools and review]( - Hello expertise, Could you please suggest best tool for database replication? data replication on transactional basis and using for filtering option like DELETE transaction not replicate to target database due to data should maintain long term in target database instance. Thanks.   SQL Server 2019 - Development [Problem extracting Data from JSON string]( - Hi there I have a JSON sample and struggling to extract data from it. This is my query Declare @JSON varchar(max) SET @json = N' { "ChannelReadings": [ { "ReadingsDto": [ { "Si": 54.03, "Raw": 0, "Conversion": 0, "TimeStamp": "2022-04-22T14:20:57" }, { "Si": 53.97, "Raw": 0, "Conversion": 0, "TimeStamp": "2022-04-22T14:22:57" }, { "Si": 54.01, "Raw": […] [I must be loosing my mind. Odd data issue]( - I have a guest table on two servers. Call them prod1 and prod2. The Prod1 database is backed up and restored to the prod2 server everyday. The backup starts around 11 pm and the restore the prod2 finishes around 10:30 am --this is to check what records exist on prod1 that dont exist on prod2 […] [How to insert data from same set of tables selecting different id from the same]( - I am trying to insert same set of records with student id 1234 (All 4 records with student id 1234 needs to be inserted into same table but instead of student id 1234. i want the id to be selected 9876) Below is the sample data Student ID 1234 below points to specific case from […] [Increment max value by over a complete dataset]( - In a weird situation where I have limited say in the ultimate solution. Switching from a table with an identity column to a table that does not have an identity column, odd I know. I can get the MAX(ID) with out an issue. I am having issues generating the next set of IDs for the […] [How to run a query multiple times with different values in date]( - I'm very new to the sql and am wondering what is the easiest way to run the same query multiple times with different dates (ideally is the last day of each month). Can I create a loop so that I don't need to manually change the value in Date? Values in date are 31Jan2022, 28Feb2022, […] Amazon AWS and other cloud vendors [Version Controlling Lambdas]( - I know there are lambda versions. But I want to know if this can be done using GitHub, can we integrate it with CodePipeline and create something like a centralised repo for all our Python lambdas and then update those functions with every push.   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -