Using Advanced SQL Features

SELECT * FROM (VALUES (1,'a'),(2,'b')) AS t(id, code);
INSERT INTO t(id,code) VALUES (1,'a'),(2,'b');

SELECT * FROM big_fact TABLESAMPLE BERNOULLI (1);
SELECT * FROM big_fact TABLESAMPLE SYSTEM (0.5) REPEATABLE (42);

-- SQL: creates new table
SELECT id, name INTO new_customers FROM customers WHERE active;
-- PL/pgSQL: assigns row(s) into variables
SELECT count(*) INTO n FROM orders;

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY total_cents) AS median,
       percentile_disc(ARRAY[0.5,0.9,0.99]) WITHIN GROUP (ORDER BY total_cents) AS pNN,
       mode() WITHIN GROUP (ORDER BY status)
  FROM orders;

SELECT rank(10000) WITHIN GROUP (ORDER BY total_cents) FROM orders;
SELECT dense_rank(10000) WITHIN GROUP (ORDER BY total_cents) FROM orders;

CREATE FUNCTION sum_sfunc(state numeric, val numeric)
RETURNS numeric LANGUAGE sql AS $ SELECT coalesce(state,0) + coalesce(val,0) $;

CREATE AGGREGATE my_sum (numeric) (
   SFUNC = sum_sfunc, STYPE = numeric, INITCOND = '0'
);
SELECT my_sum(total_cents) FROM orders;

SELECT * FROM reservations
 WHERE during && tstzrange('2025-03-01','2025-03-08');

CREATE TYPE addr AS (street text, city text, zip text);
CREATE TABLE people (id int, home addr);
INSERT INTO people VALUES (1, ROW('1 Main','SF','94107'));
SELECT (home).city FROM people;

CREATE FUNCTION f() RETURNS text AS $
SELECT 'no need to escape '' or "';
$ LANGUAGE sql;

-- Nested: use distinct tags
DO $outer$
   BEGIN PERFORM $body$select 1$body$; END;
$outer$;

WITH ins AS (
   INSERT INTO archive SELECT * FROM orders WHERE placed_at < '2024-01-01'
   RETURNING id
)
DELETE FROM orders WHERE id IN (SELECT id FROM ins);

SELECT c.id, recent.*
  FROM customers c
       LEFT JOIN LATERAL (
          SELECT * FROM orders o
           WHERE o.customer_id = c.id
           ORDER BY placed_at DESC LIMIT 3
       ) recent ON true;

SELECT * FROM ROWS FROM(
   generate_series(1, 3),
   unnest(ARRAY['a','b','c'])
) AS t(n, c);