Create a postgresql schema

[Alain De Vos]

The order in how to create a postgresql schema can be not obvious.
So here sql script as example,
So you first create :
1. A user
2. A database
3. A schema
4. Grant rights from the user to the schema.
5. Last create a table

CREATE USER john7 WITH PASSWORD 'john7';
CREATE DATABASE x7;
\c x7;
CREATE SCHEMA schema7;
GRANT USAGE, CREATE ON SCHEMA schema7 TO john7;
SET search_path TO schema7;
SET ROLE john7;
CREATE TABLE schema7.products7 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0
);
\dt schema7.*

 

[Alain De Vos]

updated,

\pset linestyle unicode
\pset border 2
CREATE USER myuser WITH PASSWORD 'myuser';
CREATE DATABASE mydb;
\c mydb;
CREATE EXTENSION IF NOT EXISTS plpython3u;
CREATE SCHEMA myschema;
ALTER SCHEMA myschema OWNER TO myuser;
GRANT USAGE, CREATE ON SCHEMA myschema TO myuser;
SET search_path TO myschema;

SET ROLE myuser;
CREATE TABLE myschema.mytable (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0
);
\dt myschema.*
INSERT INTO myschema.mytable (name, price, stock)
VALUES 
    ('Wireless Mouse', 25.50, 100),
    ('Mechanical Keyboard', 89.99, 45),
    ('USB-C Hub', 45.00, 12);

RESET ROLE;
VACUUM myschema.mytable;
VACUUM FULL myschema.mytable;

set ROLE myuser;    
SELECT name, price, stock FROM myschema.mytable WHERE stock < 20 ORDER BY price DESC;
CREATE OR REPLACE VIEW myschema.inventory_value_v AS
    SELECT * FROM (SELECT id, name, stock, price, (price * stock) AS total_value  FROM myschema.mytable ) sub  
        WHERE total_value > 500;
SELECT * FROM myschema.inventory_value_v;

RESET ROLE;
CREATE OR REPLACE FUNCTION myschema.get_total_stock_value()
  RETURNS TABLE(item_name text, total_value numeric)
AS $$
    query = "SELECT name, price, stock FROM myschema.mytable"
    result_set = plpy.execute(query)
    output = []
    for row in result_set:
        # Perform Python logic on the database rows
        val = row['price'] * row['stock']
        output.append((row['name'], val))
    return output
    $$ LANGUAGE plpython3u;
ALTER FUNCTION myschema.get_total_stock_value() OWNER TO myuser;
GRANT EXECUTE ON FUNCTION myschema.get_total_stock_value() TO myuser;    

set ROLE myuser;    
SELECT * FROM myschema.get_total_stock_value();
\copy myschema.mytable TO './mytable.csv' DELIMITER ',' CSV HEADER;
DROP VIEW IF EXISTS myschema.inventory_value_v;
DROP FUNCTION myschema.get_total_stock_value();

RESET ROLE;
DROP TABLE myschema.mytable ;
DROP SCHEMA myschema;
\c postgres
DROP DATABASE mydb;
DROP USER myuser;