# -  - #
# - PostgreSQL Commands - #


psql -U is_derayo -d Accounting -h isdevelopment.us;

# - Describe table - #
\d "ChartOfAccounts";
\d+ "ChartOfAccounts";

\! clear
\l




SELECT  
    f.attnum AS number,  
    f.attname AS name,  
    f.attnum,  
    f.attnotnull AS notnull,  
    pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,  
    CASE  
        WHEN p.contype = 'p' THEN 't'  
        ELSE 'f'  
    END AS primarykey,  
    CASE  
        WHEN p.contype = 'u' THEN 't'  
        ELSE 'f'
    END AS uniquekey,
    CASE
        WHEN p.contype = 'f' THEN g.relname
    END AS foreignkey,
    CASE
        WHEN p.contype = 'f' THEN p.confkey
    END AS foreignkey_fieldnum,
    CASE
        WHEN p.contype = 'f' THEN g.relname
    END AS foreignkey,
    CASE
        WHEN p.contype = 'f' THEN p.conkey
    END AS foreignkey_connnum,
    CASE
        WHEN f.atthasdef = 't' THEN d.adsrc
    END AS default
FROM pg_attribute f  
    JOIN pg_class c ON c.oid = f.attrelid  
    JOIN pg_type t ON t.oid = f.atttypid  
    LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum  
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
    LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)  
    LEFT JOIN pg_class AS g ON p.confrelid = g.oid  
WHERE c.relkind = 'r'::char  
    AND n.nspname = 'Accounting'  -- Replace with Schema name  
    AND c.relname = 'ChartOfAccounts'  -- Replace with table name  
    AND f.attnum > 0 ORDER BY number;



SELECT * 
FROM information_schema.columns 
WHERE table_schema = 'Accounting' 
AND table_name   = 'ChartOfAccounts';






# -  - #
# - PostgreSQL - Overview - #
https://www.tutorialspoint.com/postgresql/postgresql_quick_guide.htm


# -  - #
# - Functions and Operators - #
https://www.postgresql.org/docs/9.1/static/functions.html

# - Mathematical Functions and Operators - #
https://www.postgresql.org/docs/9.1/static/functions-math.html

# - Strings, functions & operators - #
https://www.postgresql.org/docs/9.1/static/functions-string.html

# - Creating temporary tables - #
https://dba.stackexchange.com/questions/86724/how-to-create-a-temporary-table-using-values-in-postgresql/86726
http://www.sqlines.com/postgresql/statements/create_temporary_table

# - Comparison Operators - #
https://www.postgresql.org/docs/9.1/static/functions-comparison.html

# - Select - #
https://www.postgresql.org/docs/9.5/static/sql-select.html

# - pg_dump - #
https://www.postgresql.org/docs/9.0/static/app-pgdump.html
http://www.postgresonline.com/special_feature.php?sf_name=postgresql90_pg_dumprestore_cheatsheet&outputformat=html

# - CREATE TEMPORARY TABLE - #
http://www.sqlines.com/postgresql/statements/create_temporary_table


# -  - #
# - To investigate, very important - #
    UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));
    




sSQL_ = "CREATE TEMPORARY TABLE " & sTemporary & " " & _
      "SELECT DES_DECRYPT(User,'" & sAPass & "') AS OldUser," & _
             "DES_DECRYPT(Name,'" & sAPass & "') AS OldName, " & _
             "DES_DECRYPT(Type,'" & sAPass & "') AS OldType, " & _
             "SPACE(40) AS User, SPACE(40) AS Name, SPACE(10) AS Type FROM " & sT_ & ";" & _
      "UPDATE " & sTemporary & " SET User = OldUser, Name = OldName, Type = OldType;" & _
      "SELECT A.User,B.Name FROM " & GVar.sT_AuthorizedCompanies & " A " & _
          "LEFT JOIN " & sTemporary & "  B " & _
          "ON A.User=B.User " & _
          sW_ & "ORDER BY Name;"


# -  - #
# - PostgreSQL - #
DROP VIEW IF EXISTS v_ChartOfAccounts;
CREATE VIEW v_ChartOfAccounts AS 
    SELECT Cia, 
           SUBSTRING(Account from 1 for 1) || '.' || 
           SUBSTRING(Account from 2 for 2) || '.' || 
           SUBSTRING(Account from 4 for 3) || '.' || 
           SUBSTRING(Account from 7 for 3) AS Account, 
           Description, 
           Level, 
           Able, 
           BalanceProfitLoss AS BPL 
    FROM ChartOfAccounts 
    ORDER BY Cia, Account;

DROP VIEW IF EXISTS v_ChartOfAccounts;
CREATE VIEW v_ChartOfAccounts AS 
    SELECT Cia, 
           SUBSTR(Account, 1, 1) || '.' || 
           SUBSTR(Account, 2, 2) || '.' || 
           SUBSTR(Account, 4, 3) || '.' || 
           SUBSTR(Account, 7, 3) AS Account, 
           Description, 
           Level, 
           Able, 
           BalanceProfitLoss AS BPL 
    FROM ChartOfAccounts 
    ORDER BY Cia, Account;


SELECT Account, 
       Description, 
       Level, 
       Able, 
       BPL 
FROM "Accounting".v_ChartOfAccounts
WHERE Cia = 'CS';


WITH  temp (k,v) 
AS (VALUES (0,-9999), (1, 100)) 
SELECT * FROM temp;


WITH  vals (k,v) 
AS (VALUES (0,-9999), (1, 100), (0,500), (4, 1579)) 
SELECT * INTO TEMPORARY TABLE temp 
FROM vals;
SELECT * FROM temp;


DROP TABLE IF EXISTS lookup;
CREATE TEMPORARY TABLE lookup 
AS 
SELECT * 
FROM ( VALUES (0::int,-99999::numeric, 'Hola'::varchar), 
              (1::int, 100::numeric, 'Adios'::varchar) ) 
AS t (key, value, Salutation);
SELECT * FROM lookup;


DROP TABLE IF EXISTS lookup;
CREATE TEMPORARY TABLE lookup 
AS 
WITH t (key, value, Salutation) 
AS ( VALUES (0::int,-99999::numeric, 'Hola'::varchar), 
            (1::int,100::numeric, 'Adios'::varchar) );
SELECT * FROM t;
SELECT * FROM lookup;


DROP TABLE IF EXISTS lookup;
CREATE TEMPORARY TABLE lookup 
AS 
WITH t (key, value, Salutation) as (
  VALUES (0::int,-99999::numeric, 'Hola'::varchar), 
         (1::int,100::numeric, 'Adios'::varchar) ) 
SELECT * FROM t;
SELECT * FROM lookup;


CREATE TEMP TABLE lookup
          (key, val) 
AS VALUES (0, -99999), 
          (1, 100);
SELECT * FROM lookup;


CREATE TEMP TABLE lookup 
          (key, val) 
AS VALUES (0::bigint, -99999::int), 
          (1, 100);
SELECT * FROM lookup;



WITH u AS (
SELECT * FROM ( VALUES (1, 'one'), 
                       (2, 'two'), 
                       (3, 'three')) 
                AS account (id,name) )
SELECT id, name, length(name) from u;


WITH  temp (k,v) 
AS (VALUES (0,-9999), (1, 100)) 
SELECT * FROM temp;



WITH u AS (
    SELECT * FROM (VALUES (1, 'one'), 
                          (2, 'two'), 
                          (3, 'three')) AS account (id,name)
)
SELECT id, 
       name, 
       length(name) 
FROM u;

    
WITH u AS (
    SELECT * FROM (VALUES (1, 'one'), 
                          (2, 'two'), 
                          (3, '      ')) AS account (id,name)
)
SELECT id, 
       name, 
       length(name) 
FROM u;




DROP TABLE IF EXISTS Cities;

CREATE TEMPORARY TABLE Cities
  (name VARCHAR(80));
 
BEGIN TRANSACTION;
   INSERT INTO Cities VALUES ('Warsaw');
   INSERT INTO Cities VALUES ('Prague');
   INSERT INTO Cities VALUES ('Milan');
   INSERT INTO Cities VALUES ('Guatemala'),
                             ('Honduras'), 
                             ('El Salvador'), 
                             ('Nicaragua'), 
                             ('Costa Rica'),
                             ('Panama');
COMMIT;

SELECT COUNT(*) FROM Cities;
SELECT * FROM Cities;







# -  - #
# - TRANSACTIONS - #

Functions are part of the transaction they're called from. 
Their effects are rolled back if the transaction rolls back. 
Their work commits if the transaction commits. 
Any BEGIN ... EXCEPT blocks within the function operate like 
(and under the hood use) savepoints like the SAVEPOINT and ROLLBACK TO SAVEPOINT SQL statements.

The function either succeeds in its entirety or fails in its entirety, 
barring BEGIN ... EXCEPT error handling. 
If an error is raised within the function and not handled, the transaction calling the function is aborted. 
Aborted transactions cannot commit, and if they try to commit the COMMIT is treated as ROLLBACK, same as for any other transaction in error. 


Observe:

regress=# BEGIN;
BEGIN
regress=# SELECT 1/0;
ERROR:  division by zero
regress=# COMMIT;
ROLLBACK

See how the transaction, which is in the error state due to the zero division, rolls back on COMMIT?

If you call a function without an explicit surounding transaction the rules are exactly the same as for any other Pg statement:

BEGIN;
SELECT refresh_materialized_view(name);
COMMIT;

(where COMMIT will fail if the SELECT raised an error).

PostgreSQL does not (yet) support autonomous transactions in functions, where the procedure/function could commit/rollback independently of the calling transaction. 
This can be simulated using a new session via dblink.





As my knowledge of PostgreSQL is less deeper than Craig Ringer´s.
I will try to give a shorter answer: Yes.
If you execute a function that has an error in it, none of the steps will impact in the database.
Also, if you execute a query in PgAdmin the same happen.

For example, if you execute in a query:

update your_table yt set column1 = 10 where yt.id=20;
select anything_that_do_not_exists;

The update in the row, id = 20 of your_table will not be saved in the database.




In the function level, it is not transnational. 
In other words, each statement in the function belongs to a single transaction, which is the default db auto commit value. 
Auto commit is true by default. But anyway, you have to call the function using:

select schemaName.functionName()

The above statement 'select schemaName.functionName()' is a single transaction, let's name the transaction T1, and so all the statements in the function belong to the transaction T1. In this way, the function is in a single transaction. 





It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the similarly-named SQL commands for transaction control. 
PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. 
Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in. 
However, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction.

https://www.postgresql.org/docs/current/static/plpgsql-structure.html










CREATE TABLE test_1(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);


CREATE TABLE Company(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);



CREATE TABLE Department(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);


SELECT * FROM city;

SELECT * FROM city 
WHERE countrycode = 'AFG';

SELECT * FROM city 
WHERE countrycode = 'AFG' 
ORDER BY name;

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'city';




\c IntegritySystems;
\d "Accounting".ChartOfAccounts;
\dt "Accounting".*
SELECT table_catalog, table_schema, table_name FROM information_schema.tables 
WHERE table_schema = 'Accounting';

SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'Accounting';

SELECT * FROM pg_tables WHERE schemaname='Accounting';

SHOW search_path;
SET search_path= Accounting;
SET search_path TO Accounting,public;
\d





\d city;

SELECT * FROM public.city limit 10;

SELECT * FROM public.city 
ORDER BY name limit 10;

SELECT * FROM public.city 
WHERE countrycode = 'AFG' 
ORDER BY name;

SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'city' AND COLUMN_NAME = 'col_name';

SELECT * FROM info_schema.columns 
WHERE table_schema = 'public' 
AND table_name = 'city';


SELECT  
    f.attnum AS number,  
    f.attname AS name,  
    f.attnum,  
    f.attnotnull AS notnull,  
    pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,  
    CASE  
        WHEN p.contype = 'p' THEN 't'  
        ELSE 'f'  
    END AS primarykey,  
    CASE  
        WHEN p.contype = 'u' THEN 't'  
        ELSE 'f'
    END AS uniquekey,
    CASE
        WHEN p.contype = 'f' THEN g.relname
    END AS foreignkey,
    CASE
        WHEN p.contype = 'f' THEN p.confkey
    END AS foreignkey_fieldnum,
    CASE
        WHEN p.contype = 'f' THEN g.relname
    END AS foreignkey,
    CASE
        WHEN p.contype = 'f' THEN p.conkey
    END AS foreignkey_connnum,
    CASE
        WHEN f.atthasdef = 't' THEN d.adsrc
    END AS default
FROM pg_attribute f  
    JOIN pg_class c ON c.oid = f.attrelid  
    JOIN pg_type t ON t.oid = f.atttypid  
    LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum  
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
    LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)  
    LEFT JOIN pg_class AS g ON p.confrelid = g.oid  
WHERE c.relkind = 'r'::char  
    AND n.nspname = 'public'  -- Replace with Schema name  
    AND c.relname = 'city'  -- Replace with table name  
    AND f.attnum > 0 ORDER BY number
;






SELECT 1 FROM pg_database WHERE datname = 'Hola';
SELECT 1 AS "Col" FROM pg_database WHERE datname = 'Hola';


psql -U postgres -d postgres -h isdevelopment.us -tc "SELECT 1 AS Col FROM pg_database WHERE datname = 'Hola';";
psql -U postgres -d postgres -h isdevelopment.us -c 'CREATE DATABASE "Hola"';


psql -U postgres -d postgres -h isdevelopment.us -tc "SELECT 1 AS Col FROM pg_database WHERE datname = 'Hola';" | grep -q 1 || psql -U postgres -d postgres -h isdevelopment.us -c 'CREATE DATABASE "Hola";';



# - Put in the script - #

psql -U postgres -d postgres -h isdevelopment.us -tc "SELECT 1 FROM pg_database WHERE datname = 'IntegritySystems';" | grep -q 1 || psql -U postgres -d postgres -h isdevelopment.us -c 'CREATE DATABASE "IntegritySystems" WITH OWNER = postgres ENCODING = "UTF8" TABLESPACE = pg_default LC_COLLATE = "en_US.UTF-8" LC_CTYPE = "en_US.UTF-8" CONNECTION LIMIT = -1;'

CREATE DATABASE "IntegritySystems"
  WITH OWNER = postgres 
       ENCODING = "UTF8"
       TABLESPACE = pg_default
       LC_COLLATE = "en_US.UTF-8"
       LC_CTYPE = "en_US.UTF-8"
       CONNECTION LIMIT = -1;


psql -U postgres -d IntegritySystems -h isdevelopment.us -q -f /home/Data-1/Images/Programs/Scripts-SQL/Databases.sql

/*  */
/* Comment on database IntegritySystems */
COMMENT ON DATABASE "IntegritySystems" IS 'Default administrative connection database';




psql -U postgres -d postgres -h isdevelopment.us -c "CREATE ROLE is_derayo WITH LOGIN PASSWORD 'Administrator2'";

psql -U postgres -d postgres -h isdevelopment.us -c "ALTER USER is_derayo WITH PASSWORD 'Administrator2';";
psql -U postgres -d postgres -h isdevelopment.us -c "DROP ROLE is_derayo;";

psql -U postgres -d postgres -h isdevelopment.us -c "ALTER USER is_derayo WITH SUPERUSER;";
psql -U postgres -d postgres -h isdevelopment.us -c "ALTER USER is_derayo WITH NOSUPERUSER;";

psql -U postgres -d postgres -h isdevelopment.us -c "ALTER USER is_derayo WITH SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS;";






psql -U postgres -d postgres -h isdevelopment.us -c "GRANT ALL PRIVILEGES ON DATABASE postgres TO is_derayo;";
psql -U postgres -d postgres -h isdevelopment.us -c "GRANT ALL PRIVILEGES ON DATABASE IntegritySystems TO is_derayo;";





















psql -U is_derayo -d IntegritySystems -tc 'SELECT * FROM "Accounting".ChartOfAccounts'


-c = To execute on terminal.
psql -U is_derayo -d IntegritySystems -h isdevelopment.us -c 'SELECT Account, Description, Level, Able, BPL FROM "Accounting".v_ChartOfAccounts'

-tc = Query without columns' titles.
psql -U is_derayo -d IntegritySystems -h isdevelopment.us -tc 'SELECT Account, Description, Level, Able, BPL FROM "Accounting".v_ChartOfAccounts'



# ~/.pgpass file
# ~/.pgpass
# /home/is_derayo/.pgpass
# vim ~/.pgpass
# gedit ~/.pgpass

# host:port:database:user:password

#localhost:5432:postgres:is_derayo:Administrator2
#localhost:5432:postgres:postgres:Admin22
#localhost:5432:IntegritySystems:is_derayo:Administrator2
#localhost:5432:IntegritySystems:postgres:Admin22
#localhost:5432:world:is_derayo:Administrator2
#localhost:5432:world:postgres:Admin22


isdevelopment.us:5432:*:postgres:Admin22
isdevelopment.us:5432:*:is_derayo:Administrator2
localhost:5432:*:postgres:Admin22
localhost:5432:*:is_derayo:Administrator2

#psql -U is_derayo -h isdevelopment.us -d world -c "SELECT code, name, continent FROM country";
#psql -U is_derayo -h isdevelopment.us -d world -c 'SELECT code, name, continent FROM "public".country ORDER BY Name';