Home

# Divide /

1
2
select cast(30 as float) / 120, 30::float / 120, 30 / 120;
--0.25 0.25 0

# VARIADIC Parameter

参数 nums 是一个数组，可以传入任意多个参数；然后计算它们的和值。 例如：
SELECT sum_num(1,2), sum_num(1,2,3);

sum_num sum_num
3 6 
1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE FUNCTION sum_num(VARIADIC nums numeric[])
RETURNS numeric
AS $$
DECLARE 
  ln_total numeric;
BEGIN
  SELECT SUM(nums[i]) INTO ln_total
    FROM generate_subscripts(nums, 1) t(i);
  RETURN ln_total;
END; $$
LANGUAGE plpgsql;

# GENERATE_SUBSCRIPTS

generate_subscripts return array index

1
2
3
4
5
6
7
8
select * 
  from generate_subscripts(array[4, 3, 2], 1) t(i);
  
--i 
----
--1
--2
--3

# GENERATE_SERIES

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT v, 'val:' || v 
  FROM generate_series(1, 5) v;

--1	val:1
--2	val:2
--3	val:3
--4	val:4
--5	val:5

SELECT v, 'val:' || v 
  FROM generate_series(1, 5, 2) v;
  
--1	val:1
--3	val:3
--5	val:5

# Values Lists

Syntax : VALUES ( expression [, ...] ) [, ...]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
VALUES (1, 'one'), (2, 'two'), (3, 'three');

--will return a table of two columns and three rows. 
--It's effectively equivalent to:

SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';

--By default, PostgreSQL assigns the names column1, column2, etc. to the columns of a VALUES table. 
--The column names are not specified by the SQL standard and different database systems do it differently, 
--so it's usually better to override the default names with a table alias list, like this:

SELECT * 
  FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num, letter);
  
 num | letter
-----+--------
   1 | one
   2 | two
   3 | three

# Alias

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create table t1(id int, name varchar(20));
insert into t1 values(1, 'SQL');

create table t2(id int, name varchar(20));
insert into t2 values(1, 'Alias');

select t1.*  --t1 is alias of table t2
  from t1 t
 cross join t2 t1;
--1	Alias

create temporary table t1(id int, name varchar(20));
insert into t1 values (1, 'TEMP');
select * from t1; --select temp table not table t1
--1 'TEMP' 

select -id as id 
  from (values(1), (2), (3)) t(id) 
 order by id; --order by alias id not column id
-- -3
-- -2
-- -1

# Anonymous Block

1
2
3
4
5
6
7
DO $$
DECLARE
name text;
BEGIN
  name := 'PL/pgSQL';
  RAISE NOTICE 'Hello %!', name;
END $$;

以上是一个匿名块，与此相对的是命名块（也就是存储过程和函数）。其中， DO 语句用于执行匿名块；我们定义了一个字符串变量 name，然后给它赋值并输出一个信息； RAISE NOTICE 用于输出通知消息。
$$ 用于替换单引号 ' ，因为 PL/pgSQL 代码主体必须是字符串文本，意味着代码中所有的单引号都必须转义（重复写两次）。对于上面的示例，需要写成以下形式：

1
2
3
4
5
6
7
DO
'DECLARE
  name text;
BEGIN
  name := ''PL/pgSQL'';
  RAISE NOTICE ''Hello %!'', name;
END'

# Month Calendar

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
--current_date is 2023/04/18

with recursive d(ymd) as (
  select date_trunc('month', current_date)::date as ymd --first day of month
  union all
  select ymd + 1
    from d
  where ymd < (date_trunc('month', current_date) + interval '1' month - interval '1' day)::date --last day of month
),
d2 as (
	select ymd, 
	       extract('week' from ymd) wk,  --weekofyear
	       extract (day from ymd) dm,    --dayofmonth
	       extract (dow from ymd) dw     --dayofweek
	 from d
)
select min(case dw when 1 then dm end) as "星期一",
       min(case dw when 2 then dm end) as "星期二",
       min(case dw when 3 then dm end) as "星期三",
       min(case dw when 4 then dm end) as "星期四",
       min(case dw when 5 then dm end) as "星期五",
       min(case dw when 6 then dm end) as "星期六",
       min(case dw when 0 then dm end) as "星期日"
  from d2
 group by wk
 order by 1 nulls first, 2 nulls first, 3 nulls first, 4 nulls first, 5 nulls first, 6 nulls first, 7 nulls first;

# User Permission Design

Users can have roles with certain permissions, or they can directly have permissions.

Edited on