PostgreSQL FAQ(三）参数传递及表创建时间

1、对copy操作的参数传递

mydb=# copy t to '/pgccc/tmp/t.dat' with CSV HEADER;
COPY 2
mydb=# \! cat /pgccc/tmp/t.dat
id,col2
1,Shanghai
3,Beijing
mydb=# truncate t;
TRUNCATE TABLE

CREATE OR REPLACE FUNCTION etldata(filePath text) RETURNS "pg_catalog"."text" AS $BODY$
DECLARE res TEXT;
BEGIN
res := 'COPY t FROM ' ||  quote_literal(filePath) ||  ' WITH DELIMITER '','' CSV HEADER';
raise notice '%', res;
execute res;
res = 'abc';
return res;
END
$BODY$ LANGUAGE plpgsql;

mydb=# select etldata('/pgccc/tmp/t.dat');
NOTICE:  COPY t FROM '/pgccc/tmp/t.dat' WITH DELIMITER ',' CSV HEADER
 etldata
---------
 abc
(1 row)

mydb=# select * from t;
 id |   col2
----+----------
  1 | Shanghai
  3 | Beijing
(2 rows)

上边：quote_literal(filePath)是关键，它帮你把完整的字符串内容给传递过去。

2、\set中如何传递变量

postgres=# select * from t;
 id | col2
----+------
  1 | zhou
(1 row)

-- 定义变量
postgres=# \set v_col 'zhou'
postgres=# select * from t where col2 = :v_col;
ERROR:  column "zhou" does not exist
LINE 1: select * from t where col2 = zhou;
                                     ^

会发现 :v_col在这里出问题了。因为它是字符串值，正确的应该是将它括起来。不留意，很容易出错。

postgres=# select * from t where col2 = :'v_col';
 id | col2
----+------
  1 | zhou
(1 row)

使用prepare语句及传递变量：

postgres=# prepare mysel as select * from t where col2 = $1;
PREPARE
postgres=# execute mysel(:'v_col');
 id | col2
----+------
  1 | zhou
(1 row)

3、使用session var

postgres=# set session my.var = 'zhou';
SET
postgres=# select * from t where col2 = current_setting('my.var');
 id | col2
----+------
  1 | zhou
(1 row)
postgres=# set session my.id = 1;
SET

postgres=# select * from t where id = current_setting('my.id')::int;
 id | col2
----+------
  1 | zhou
(1 row)

4、表的创建时间

分别为 Access time,Modify time, Change time a time 
(Access Time):表示最后一次访问 文件 或目录 
m time (Modify Time):表示最后一次修改 文件 或目录 的时间 
c time （Change Time）:表示最后一次改变 文件 或目录 的时间 

mydb2=# insert into t3 values(2);
INSERT 0 1
mydb2=# \! stat base/16663/33069
  File: base/16663/33069
  Size: 8192          Blocks: 16         IO Block: 4096   regular file
Device: 802h/2050d    Inode: 2241445     Links: 1
Access: (0600/-rw-------)  Uid: (   26/postgres)   Gid: (   26/postgres)
Access: 2022-09-09 09:03:54.271549851 +0000
Modify: 2022-09-09 09:04:18.845783455 +0000
Change: 2022-09-09 09:04:18.845783455 +0000
 Birth: 2022-09-09 09:03:54.271549851 +0000
mydb2=# insert into t3 values(3);
INSERT 0 1
mydb2=# \! stat base/16663/33069
  File: base/16663/33069
  Size: 8192          Blocks: 16         IO Block: 4096   regular file
Device: 802h/2050d    Inode: 2241445     Links: 1
Access: (0600/-rw-------)  Uid: (   26/postgres)   Gid: (   26/postgres)
Access: 2022-09-09 09:03:54.271549851 +0000
Modify: 2022-09-09 09:04:18.845783455 +0000
Change: 2022-09-09 09:04:18.845783455 +0000
 Birth: 2022-09-09 09:03:54.271549851 +0000
mydb2=# checkpoint;
CHECKPOINT
mydb2=# \! stat base/16663/33069
  File: base/16663/33069
  Size: 8192          Blocks: 16         IO Block: 4096   regular file
Device: 802h/2050d    Inode: 2241445     Links: 1
Access: (0600/-rw-------)  Uid: (   26/postgres)   Gid: (   26/postgres)
Access: 2022-09-09 09:03:54.271549851 +0000
Modify: 2022-09-09 09:11:47.472048088 +0000
Change: 2022-09-09 09:11:47.472048088 +0000
 Birth: 2022-09-09 09:03:54.271549851 +0000

上边的Birth值，看起来可以近似表示该表的创建时间。我们也可以使用函数：pg_stat_file来得到相应的信息：

postgres=# select * from pg_stat_file(pg_relation_filepath('ttt'));
 size |         access         |      modification      |         change         | creation | isdir
------+------------------------+------------------------+------------------------+----------+-------
 8192 | 2023-05-08 22:32:50+08 | 2023-05-08 22:37:41+08 | 2023-05-08 22:37:41+08 |          | f
(1 row)

注意到creation这一栏是空的。实际上你会发现，stat命令对应的Birth值，这时也是空的。这个要取决于文件系统是否支持。

postgres=# select pg_relation_filepath('ttt');
 pg_relation_filepath
----------------------
 base/14486/16392
(1 row)
postgres=# \! stat 14/data/base/14486/16392
  File: ‘14/data/base/14486/16392’
  Size: 8192            Blocks: 16         IO Block: 4096   regular file
Device: 803h/2051d      Inode: 33639070    Links: 1
Access: (0600/-rw-------)  Uid: (   26/postgres)   Gid: (   26/postgres)
Access: 2023-05-08 22:32:50.395414107 +0800
Modify: 2023-05-08 22:37:41.540845570 +0800
Change: 2023-05-08 22:37:41.540845570 +0800
 Birth: -

关于stat命令的Birth项的值，有如下解释：

stat命令的Birth字段表示文件的创建时间，该属性是ext4的新功能（当然也适用于xfs文件系统），也称为crtime或btime，之前也在xfs文件系统中测试过，发现xfs不支持creation time. 但是从xfs v5开始，xfs已经支持creation time。

如果只是要求近似表示，那么取上边的access time似乎也是可以的。当然，如果要完全自己控制，使用PG的事件触发器，在建表的时候将当前时间和表名记录下来，在删除表的时候，可以将相应记录清除，也应该能够实现。

结束语：

昨天网上传来陈皓离去的消息，实让人惋惜。IT界近几年猝死或因心梗出问题离去的，几乎年年都会碰到。确实是一种高危职业。所以，趁着年轻或者说未老之前，能动的话，还是多动一动，作息时间尽量规律一些，给大脑和内脏充足的调息时间。

始终相信人的思想要与自己的身体最好能和睦相处，不要过度消费（消耗）。

金钱和其它，都可以没了重新再来过；但是，生命于我们每个人只有一次而已。所以，在做好工作、顾及好家庭的同时，还是稍稍珍重留意下自己的身体状况吧。