postgresql-generic-utils.sql

Generic SQL queries and PLpgSQL functions for PostgreSQL.

Compiled (some written) and tested by Asta1986 on PostgreSQL 13.

Contents:

CSV:

• Import table/column from CSV file.
• Import with header (if the first row contains column names).
• Export table/query result to CSV.

FUNCTIONS

• Erase all schemas (except system ones) and contained objects in the current db.
• Set cell values of text columns in a table to lower case.
• Transform a string with google maps point coordinates to WGS84 WKT.

DUPLICATE ROWS:

• Compare rows according to values in certain columns.

PIVOT TABLES:

• Using crosstab.
• Using aggregate_function(case).

TABLE INFO:

• Get table names.
• Get information of columns.
• Get name and type of PK column.

TEMPORARY TABLE:

• Create a temporary table from the result of query.

TRANSACTIONS:

• Create a transaction block.
• Abort current transaction.

USERS & PERMISSIONS:

• Create a read only user manually.
• Add privileges to read only user automatically upon creation of new schemas.

VIEWS:

• Display user created views.