Code Snippets PLpgSQL

Short texts PLpgSQL 🧑‍💻 Code snippets PLpgSQL ✍️ Reading lists PLpgSQL 👀

PostgreSQL function checking whether a parameter is in a valid set of values

— — Function that needs to explicitly check whether the ‘color’ value is valid — CREATE OR REPLACE FUNCTION get_gluon(color text) RETURNS TEXT AS $$ #print_strict_params on DECLARE duck TEXT; BEGIN IF color not in (‘red’, ‘green’, ‘blue’) THEN RAISE EXCEPTION ‘unknown color %’, color; END IF; — …. return duck; END; $$ LANGUAGE plpgsql […]

Decrypt T-SQL modules.sql

— More information: https://sqlsunday.com/2013/03/24/decrypting-sql-objects/ SET NOCOUNT ON DECLARE @owner sysname=’dbo’, @name sysname=’sp_someprocedure’; ———————————————————– — Declarations: DECLARE @offset int=1; DECLARE @datalength int; DECLARE @encrypted_object nvarchar(max); DECLARE @decrypted_object nvarchar(max)=N”; DECLARE @fake_object nvarchar(max); DECLARE @fake_encrypted_object nvarchar(max); DECLARE @lf nvarchar(max)=NCHAR(13)+NCHAR(10); DECLARE @type varchar(128); DECLARE @object_id int=OBJECT_ID(‘[‘[email protected]+’].[‘[email protected]+’]’); DECLARE @a int, @b int, @c int; — Check that the object exists […]

Imports an XML blob into a set of relational tables.

— Read this first: https://sqlsunday.com/2016/06/16/copying-data-with-foreign-keys-and-identity-columns/ IF (OBJECT_ID(‘dbo.LoadTableBlobs’) IS NULL) EXEC(‘CREATE PROCEDURE dbo.LoadTableBlobs AS –‘) GO /* Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution. http://creativecommons.org/licenses/by/4.0/ Source: http://sqlsunday.com/downloads/ DISCLAIMER: This script may not be suitable to run in a production environment. I cannot assume any responsibility regarding the accuracy of the output information, performance […]

Find primary key candidates.sql

— Source: https://sqlsunday.com/2017/02/21/finding-primary-key-candidates/ IF (OBJECT_ID(‘dbo.FindPrimaryKey’) IS NULL) EXEC(‘CREATE PROCEDURE dbo.FindPrimaryKey AS –‘); GO /* This stored procedure is used to identify primary key candidates. Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution. http://creativecommons.org/licenses/by/4.0/ Source: http://sqlsunday.com/downloads/ Version: 2017-10-13 DISCLAIMER: This script does not make any modifications to the server, except for installing a stored […]

method to generate amount of distance between 2 position on km

CREATE OR REPLACE FUNCTION distance(lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT) RETURNS FLOAT AS $$ DECLARE x float = 111.12 * (lat2 – lat1); y float = 111.12 * (lon2 – lon1) * cos(lat1 /92.215); BEGIN RETURN sqrt(x * x + y * y); END $$ LANGUAGE plpgsql; // km