<jupyter_start><jupyter_text>PostgreSQL coding examplesBuild some basic tables and create functions with this notebook.a General best practice is to implement a SQL function before building PL/pgSQL script if the transaction involves time stamp date that is required to be dumped for security reasons. It is also preferred to build common table expressions, LATERAL JOINS, window functions and other complex logic with SQL.After the core examples are built, advanced buiding blocks and functions using PL/pgSQL, C and ISO-compliant SQL are covered [here]{'work in progress'}Features of PL/pgSQL include EXCEPTIONs, cursors, debug and notice messages, triggers, control and loop statements.<jupyter_code>%%writefile mt_botanica_schema.sql
--practice import file for PostgreSQL database
CREATE ROLE mt_botanica_app LOGIN;
CREATE DATABASE mt_botanica ENCODINT 'UTF-6' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' TEMPLATE template0
OWNER mt_botanica_app;
--connect
\c mt_botanica
--schema name is the same as the owner name
CREATE SCHEMA mt_botanica_app AUTHORIZATION mt_botanica_app;

SET search path to mt_botanica_app;
SET ROLE mt_botanica_app;

--build the core table first
--species_id is not taxonomic related, assigned by Postres
CREATE TABLE species (
    species_id SERIAL PRIMARY KEY,
    family_name TEXT NOT NULL,
    sub_familay_name TEXT NOT NULL,	
    genus_name TEXT NOT NULL,
    specific_epithet TEXT NULL,
    variety TEXT NULL,
    year_species SMALLINT NULL,

--CHECK (family_name in --wip
);

--distributional range, needs contraints
CREATE TABLE range (
    native TEXT NOT NULL,
    cultivated TEXT NULL,
    naturalized TEXT NULL

/* CREATE TABLE common_names (
    species_id
*/
 

%%writefile pg_tests_conn.sh
#!/bin/bash
connection_number=`PGOPTIONS='--statement_timeout=0' psql -AqXt -c "SELECT
count(*) FROM pg_stat_activity"`
case $connection_number in
[1-50]*)
echo "OK - $connection_number are used"
exit 0
;;
[50-100]*)
echo "WARNING - $connection_number are used"
exit 1
;;
#add more case conditions....
esac

#!bash pg_tests_conn.sh<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.06816015252621545, 'fraction_numerical': 0.007149666348903718, 'mean_word_length': 4.830555555555556, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '347401', 'license': 'Apache-2.0', 'n_tokens_mistral': 669, 'n_tokens_neox': 635, 'n_words': 247, 'path': '00_mt_PostgreSQL_core_builds.ipynb', 'repo_name': 'worker-bee-micah/sql', 'size': '2098'}