In the SQL snippet below, you see custom aggregates that are similar to first_value/last_value but which ignore NULL values, making them more useful in my opinion.
-- "s" stands for state, "v" stands for value, "sf" stands for state (transition) function
-- since these are defined as 'strict', "s" is the previous, while "v" is the next, non-null
-- value in the defined window.
create or replace function sf_first_value_ignore_nulls(s anyelement, v anyelement)
In relational databases, it is possible to request that the database engine executes a function when an event is about to take place within a table. The events that can trigger such a function are INSERT, UPDATE and DELETE.
In the case of triggers called before these operations, database engines give us an additional power: the power to intervene, and possibly prevent the operation from taking place.
This is the core concept of this lesson.
| -- "s" stands for state, "v" stands for value, "sf" stands for state (transition) function | |
| create or replace function sf_previous(s anyelement, v anyelement) | |
| returns anyelement language sql immutable strict as $$ | |
| select v $$; | |
| create or replace function sf_upcoming(s anyelement, v anyelement) | |
| returns anyelement language sql immutable strict as $$ | |
| select s $$; | |
| create or replace aggregate previous(anyelement) ( |
| -- Similar to coalesce, but only gives back a non-null value if all the non-null | |
| -- params are equivalent. | |
| create or replace function coalesce_equal(variadic args anycompatiblearray) | |
| returns anycompatible language sql immutable as $$ | |
| with all_args(arg) as (select unnest(args)), dist_args(count) as ( | |
| select count(distinct arg) filter (where arg is not null) from all_args | |
| ) | |
| select arg from all_args, dist_args | |
| where arg is not null and count = 1 | |
| limit 1 |
You can do this either by using a public service like https://mermaid.ink or while hosting your own mermaid server. I find the latter option to be better in the long run, both in the individual and the collective sense. Below are recipes for both.
Use the following function defintion in your Jupyter notebook:
import base64
from IPython.display import display_svg
from urllib.request import Request, urlopen
| <!DOCTYPE html> | |
| <html lang="en"> | |
| <head> | |
| <meta charset="UTF-8"> | |
| <meta name="viewport" content="width=device-width, initial-scale=1.0"> | |
| <title>Document</title> | |
| </head> | |
| <body> | |
| <div><a href="https://gistpreview.github.io/?2cf125d77ee7881a21a5e91f44cbcd9e/opening.html">(top)</a></div> | |
| <div><a href="https://shorturl.at/sMjQE">(home)</a></div> |
| # | |
| # credit to `jonomoss`: https://www.youtube.com/watch?v=8QpCzaXBUI4 | |
| # | |
| apt update | |
| # This is up to you if you would like to apply all updates | |
| apt upgrade | |
| # These are the extra dependencies I needed | |
| # You can see if you are successful without them | |
| apt install gnupg libcurl4 libncurses5 libxslt1-dev libgpm2 libtinfo5 |
| if [ $# -ne 5 ] && [ $# -ne 4 ]; then | |
| echo "Usage: `basename $0` <github_user> <github_repo> <branch> <dest_dir> [<repo_dir>]" | |
| echo | |
| echo "if <repo_dir> is omitted then the entire repo archive is unpacked into <dest_dir>" | |
| exit 0 | |
| fi | |
| DEST_DIR=./$4 | |
| SOURCE_URL=https://github.com/$1/$2/archive/refs/heads/$3.tar.gz | |
| if [ $# -ne 5 ]; then |
The first president to govern ghana was Osagyefo Dr Kwame Nkrumah. The common language spoken in ghana is English but the traditional or local language in ghana is Twi, there are many dishes in ghana but the best and popular dish in ghana is Fufu, fufu is a dish made from cassava and yam or plantain together, the ghanaian dance is called "Adowa", and the ghana popular costume is "Kente". Ghana is a country ruled with written constitution and every four years election takes place to select a new president. Ghana has many political parties but the most popular parties which has ruled the country for the past 24 years are National Democratic Congress (NDC) and New Patriotic Party
DOJO Links:
checks if a character is non-repeating by looking for the 2nd match in a string and ensuring that this 2nd match exists. Based on
String.prototype.indexOf(check the second usage in the Syntax section on MDN).