Postgres Features: Window Function, CTE, Views, Function, JSON

Introduction to Postgres features

— -Window Function — -

1, What is Window Function?
Window Function performs a calculation across a set of rows that are related to the current row.
- aggregate information
- non-aggregate information

2, Usage
- aggregate

AGGREGATE_FUNCTION() OVER{
PARTITION BY ___
ORDER BY ___
<WINDOW FRAME CLAUSE>}

WINDOW FRAME CLAUSE:

- ROWS BETWEEN frame_start AND frame_end
## frame:
- UNBOUNDED PRECEDING/FOLLOWING
- XXPRECEDING/FOLLOWING
- CURRENT ROW

• non-aggregate

NON_AGGREGATE_FUNCTION() OVER {
PARTITION BY ___
ORDER BY ___
<WINDOW FRAME CLAUSE>}

NON AGGREGATE FUNCTIONS:

- row_number (): Returns the number of the current row within its partition, counting from 1
- first_value(col): Returns value evaluated at the row that is the first row of the window frame
- last_value(col): Returns value evaluated at the row that is the last row of the window frame
- lag (col[, offset]): Returns value evaluated at the row that is offset rows before the current row within the partition
- lead (col[, offset]): Returns value evaluated at the row that is offset rows after the current row within the partition

— -Common Table Expression — -

1, What is CTE
CTE defines a query that can be reused, it acts as a temporary table defined within the scope of the statement.

2, Usage

WITH temp_table_name AS ( SELECT … FROM …)
SELECT * FROM temp_table_name

3, Pros and Cons
Pro:
- add readability
Cons:
- might be an optimization barrier

— -Views — -

1, What is view
A view is a named query that provides a way to present data in the base tables. It is not physically materialized, it is just working with the base table.

2, Usage

CREATE OR REPLACE VIEW view_name AS
SELECT … FROM … WHERE… [WITH CHECK OPTION];

WITH CHECK OPTION — INSERT and UPDATE commands on the view will be checked to ensure that new
rows satisfy the view-defining condition.

— -CTE and Views — -
CTE is temporary, works only in a statement.
View is not temporary, works in the schema.

— -Functions — -

1, What is Function
Function allows to put a series of SQL statements together and treat them as a unit.

2, Usage
- Create function

CREATE OR REPLACE FUNCTION function_name (arg1 datatype DEFAULT val)
RETURNS type | TABLE (column_name column_type [, …]) AS
$$
BODY OF FUNCTION
$$
LANGUAGE SQL;

-Call function

SELECT * FROM function_name(arg1);

• Delete function

DROP FUNCTION function_name;

— -JSON — -

1, What is json?
JSON, JavaScript Object Notation, is an open-standard file format that uses human-readable text to transmit data objects consisting of key-value pairs and array data types

2, Usage
Select with pointer symbols

# if you want a json type
SELECT column_name -> name -> name FROM table;
# if you want a text type
SELECT column_name -> name ->> name FROM table;

3, Convert to JSON
to_json(record) converts a value to json