Help with Powershell functions

04 July,2022 by Tom Collins

Question:  I need some help with a Powershell function to generate some text output based on some imput parameters . Can you supply an example of a Powershell function or possibly including calling a function from within a function

 

Answer: This is a general question , but here is an example of utilising a function using the Powershell syntax. The concept is a general programming construct . In the example below there are  2 functions - GetNewDatabaseDetails   & Get-RandomPassword  .       

Don't worry about the purpose of the function - it's part of a larger script to generate code , the main point is that the function is referenced and called from different parts of the code. 

At the bottom of the script you'll see the line  "GetNewDatabaseDetails $mydb"   - It's calling the   GetNewDatabaseDetails function , which generates a bunch of text for creating a PostgreSQL database.   In the middle of the GetNewDatabaseDetails there is a reference to the Get-RandomPassword  function. 

Generally - if you're finding yourself reusing Powershell code & making slight modifications - then there is possibility it's a candidate to be converted into a function

 

##Input parameter
param (
    [string]$mydb = ""
    
)


#Purpose generate a new CREATE DATABASE 
Function GetNewDatabaseDetails([string]$dbname)
{

$dbname = $dbname;
$userfull = $dbname + "_full"
$usefullpw = Get-RandomPassword 8 
$userread= $dbname + "_read"
$usereadpw = Get-RandomPassword 8 
$fullcreatedb = @"

###############START  CREATE DB DETAILS: $dbname#####################

CREATE DATABASE $dbname;
\c $dbname
CREATE SCHEMA $dbname;

CREATE USER $userread WITH ENCRYPTED PASSWORD '$usefullpw';
GRANT  CONNECT ON DATABASE $dbname  TO $userread;
GRANT  USAGE   ON SCHEMA $dbname  TO $userread;
GRANT SELECT ON ALL TABLES IN SCHEMA $dbname TO $userread;
ALTER USER $userread SET search_path TO $dbname;


CREATE USER $userfull WITH ENCRYPTED PASSWORD '$usereadpw';
GRANT  CONNECT ON DATABASE  $dbname TO $userfull;
GRANT  USAGE,CREATE   ON SCHEMA $dbname  TO $userfull;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA $dbname TO $userfull;
ALTER USER $userfull SET search_path TO $dbname;

## username/password  
$dbname
$userread / $usereadpw
$userfull / $usefullpw


###############FINISH  CREATE DB DETAILS: $dbname#####################
"@

          
return $fullcreatedb 
    
}
  

function Get-RandomPassword {
    param (
        [Parameter(Mandatory)]
        [int] $length
    )
    #$charSet = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789{]+-[*=@:)}$^%;(_!&#?>/|.'.ToCharArray()
    $charSet = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'.ToCharArray()
    $rng = New-Object System.Security.Cryptography.RNGCryptoServiceProvider
    $bytes = New-Object byte[]($length)
 
    $rng.GetBytes($bytes)
 
    $result = New-Object char[]($length)
 
    for ($i = 0 ; $i -lt $length ; $i++) {
        $result[$i] = $charSet[$bytes[$i]%$charSet.Length]
    }
 
    return (-join $result)
}




##example

GetNewDatabaseDetails $mydb



Full credit for the Get-RandomPassword  function goes to arminreiter.com  

 

Read more on Powershell functions

How to use Powershell string functions

Powershell script – Function to return server disk capacity

Powershell script function for disk free


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Help with Powershell functions


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer