PostgreSQL

2022-01-26 15:15:07    2    0    0
system_stats是Postgres的扩展,它提供了访问系统级统计信息的功能,这些统计信息可用于监控。它支持Linux、macOS和Windows。
git https://github.com/EnterpriseDB/system_stats.git

Linux and macOS:
    tar -zxvf system_stats-1.0.tar.gz
    cd system_stats-1.0
    PATH="/usr/local/pgsql/bin:$PATH" make USE_PGXS=1
    sudo PATH="/usr/local/pgsql/bin:$PATH" make install USE_PGXS=1
功能:
    提供以下函数来获取所有平台的系统级统计信息。
    pg_sys_os_info 此界面允许用户获取操作系统统计信息。
        名字
        版本
        主机名
        域名
        句柄计数
        进程计数
        线程计数
        建筑
        上次启动时间
        正常运行时间(以秒为单位)
        
    pg_sys_cpu_info 此接口允许用户获取 CPU 信息。
        供应商
        描述
        型号名称
        处理器类型
        逻辑处理器
        物理处理器
        内核数
        建筑
        时钟速度(以赫兹为单位)
        处理器类型
        中央处理器系列
        字节顺序
        L1d 高速缓存大小
        L1i 高速缓存大小
        L2 高速缓存大小
        L3 高速缓存大小
        
    pg_sys_cpu_usage_info 此接口允许用户获取 CPU 使用率信息。值是 CPU 在所有操作上花费的时间的百分比。
        处理用户模式正常进程所花费的时间百分比
        处理用户模式 niced 过程所花费的时间百分比
        在内核模式进程中花费的时间百分比
        在空闲模式下花费
2022-01-24 15:52:49    115    0    0
本次自定义安装在/home/pg12/pgsql下
*linux最好dash该bash,可见http://ivan.gold/blog/post/ivan/Debian%E7%B3%BBdash%E6%94%B9bash
1、pg源码下载
   https://www.postgresql.org/ftp/source/
   下载postgresql-12.7.tar.gz
   [root@centos home]# wget -c https://ftp.postgresql.org/pub/source/v12.7/postgresql-12.7.tar.gz
2、解压
   [root@centos home]# tar -zxvf postgresql-12.7.tar.gz
   [root@centos home]# cd postgresql-12.7
3、指定安装目录
   [root@centos postgresql-12.7]# ./configure --prefix=/home/pg12/pgsql
   [root@centos postgresql-12.7]# make
   [root@centos postgresql-12.7]# make install
   #在执行configure或make过程中,可能会有依赖错误,需要自己改正
   [root@centos postgresql-12.7]# cd /home/pg12/pgsql
   [root@centos pgsql]# ll
    rwxr-xr-x 2 postgres12 postgres12 4096 10月 27 10:04   bin
    drwxr-xr-x 6 postgres12 postgres12 4096 10月 27 10:04 include
    drwxr-xr-x 4 postgres12 postgres12 4096 10月 27 11:27 lib
    drwxr-xr-x 7 postgres12 postgres12 4096 10月 27 11:27 share
   #以上是主程序安装完毕,由于PostgreSQL是插件可扩展的,主程序安装完后,其实有好多实用功能是没有的,源码中自带了,但是没有安
2022-01-18 10:07:02    1    0    0
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright  for distribution terms
       \h          for help with SQL commands
       \?          for help with psql commands
       \g          or terminate with semicolon to execute query
       \q          to quit
postgres=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gdesc                 describe result of query, without executing it
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [FILE]             as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds
2021-11-04 17:20:41    44    1    0

1、数据库建表

        CREATE TABLE test_tab_jdbc_cursor (
            c_id varchar,
            c_name varchar,
            c_type varchar
        );
        INSERT INTO test_tab_jdbc_cursor VALUES ('c1', '1-name1', '1');
        INSERT INTO test_tab_jdbc_cursor VALUES ('c2', '1-name2', '1');
        INSERT INTO test_tab_jdbc_cursor VALUES ('c3', '1-name3', '1');
        INSERT INTO test_tab_jdbc_cursor VALUES ('c4', '2-name1', '2');
        INSERT INTO test_tab_jdbc_cursor VALUES ('c5', '2-name2', '2');
        INSERT INTO test_tab_jdbc_cursor VALUES ('c6', '2-name3', '2');

2、数据库建函数

CREATE OR REPLACE FUNCTION test_fun_jdbc_cursor(IN p_type varchar, INOUT re_code varchar, INOUT re_message varchar, INOUT re_cur refcursor)
RETURNS record
AS $BODY$
DECLARE v_r_code VARCHAR(30) DEFAULT 'PG-00000';
                 v_r_msg VARCHAR(2000) DEFAULT 'Success';
BEGIN
        open re_cur for SELECT c_id,c_name,c_type from test_tab_jdbc_cursor where c_type = p_type;
            re_code:=v_r_code;
            re_message:=

2021-10-27 15:17:52    115    0    0

书接上文:pg>编译安装-自定义安装目录 (ivan.gold)

1、下载

    https://pgxn.org/dist/orafce/

    这里使用最新版orafce-3.16.2.zip

2、安装

    root:

        1)解压

        unzip orafce-3.16.2.zip

        2)移动到pg安装目录的extension目录下

        mv -f orafce-3.16.2 /home/pg12/pgsql/share/extension

        3)这里使用postgres12用户编译,因此将pg安装目录属组给postgres12用户

        chown -R postgres12:postgres12 /home/pg12/pgsql

    su - postgres12

        cd /home/pg12/pgsql/share/extension/orafce-3.16.2

        make

        make install

        psql postgres postgres12

            postgres-# \dn

                   List of schemas

                  Name  |   Owner   

                --------+------------

                 public | postgres12

           

            postgres=# create extension orafce;

           

            postgres=# \dn

                      List of schemas

                     Name     |   Owner   

                --------------+------------

                 dbms_alert   | postgres12

                 dbms_assert  | postgres12

                 dbms_output  | postg

2021-10-25 11:20:57    42    0    0

    ---------------------------------------------------------------------------

    --

    -- syscat.sql-

    --    sample queries to the system catalogs

    --

    --

    -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group

    -- Portions Copyright (c) 1994, Regents of the University of California

    --

    -- src/tutorial/syscat.source

    --

    ---------------------------------------------------------------------------

 

    --

    -- Sets the schema search path to pg_catalog first, so that we do not

    -- need to qualify every system object

    --

    SET search_path TO pg_catalog;

 

    -- The LIKE pattern language requires underscores to be escaped, so make

    -- sure the backslashes are not misinterpreted.

    SET standard_conforming_strings TO on;

 

    --

    -- lists the names of all database owners and the name of their database(s)

    --

    SELECT rolname, datname

      FROM pg_roles, pg_database

      WHERE pg_roles.oid = datdba

      ORDER BY rolname, datname;

 

    --

    -- l

2021-10-25 10:57:24    54    0    0

1、自定义postgresql安装目录

    默认安装在/usr/local/pgsql下,configure 时加上 --prefix参数

2、查看\d开头命令执行的sql

    1)源码src/bin/psql/describe.c下有执行sql的描述

    2)  psql登陆时加-E参数

3、部分可从数据库系统表中获取的信息

    src/tutorial/syscat.source

2021-09-26 13:47:42    56    0    0
1、按月产生连续数据
    highgo=# SELECT to_char( T, 'yyyy-mm' ) AS TIME FROM generate_series(to_timestamp('2021-01', 'yyyy-mm'), to_timestamp('2021-10', 'yyyy-mm'), '1 months') AS T;
          time   
        ---------
         2020-01
         2020-02
         2020-03
         2020-04
     
2、按天产生连续数据
    highgo=# SELECT to_char( T, 'yyyy-mm-dd' ) AS TIME FROM generate_series(to_timestamp('2021-01-01', 'yyyy-mm-dd'), to_timestamp('2021-01-03', 'yyyy-mm-dd'), '1 days') AS T;
            time    
        ------------
         2020-01-01
         2020-01-02
         2020-01-03
      
3、按小时产生连续数据
    highgo=# SELECT to_char( t, 'yyyy-mm-dd hh24:mi:ss' ) AS TIME FROM generate_series ( to_timestamp( '2021-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ), to_timestamp( '2021-01-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ), '1 hours' ) AS t;
                time         
        ---------------------
         2020-01-01 00:00:00
         2020-01-01 01:00:00
         .    .    .
         2020-01-01 06:00:00
     
2021-09-26 13:09:22    55    0    0

CREATE OR REPLACE FUNCTION db2_timestampdiff(i_type int, tp1 timestamp with time zone, tp2 timestamp with time zone)

RETURNS numeric

LANGUAGE plpgsql

AS $function$

    DECLARE

        l_result number;

        t_result number;

    begin

        l_result:=null;

        --秒

        if ( i_type = 2 ) then

            select floor(extract(epoch from (tp1-tp2))) into t_result;

            l_result := t_result;

        end if;

        --分钟

        if (i_type = 4) then

            select floor(extract(epoch from (tp1-tp2))/60) into t_result;

            l_result := t_result;

        end if;

        --小时

        if (i_type = 8) then

            select floor(extract(epoch from (tp1-tp2))/60/60) into t_result;

            l_result := t_result;

        end if;

        --天

        if (i_type = 16) then

            select floor(extract(day from (tp1-tp2))) into t_result;

            l_result := t_result;

        END IF;

        --周

        if (i_type = 32) then

            select floor(extract(WEEKDAY from (tp1-tp2))) into t_resu

2021-09-26 11:15:16    38    0    0

创建函数返回游标:

CREATE or REPLACE FUNCTION fun_cursor( in v_p1 int,in v_p2 int, refcursor, refcursor)
RETURNS SETOF refcursor AS $$
    BEGIN
        OPEN $3 FOR SELECT * FROM users where u_type = v_p1;
        RETURN NEXT $3;
        OPEN $4 FOR SELECT * FROM users where u_type = v_p2;
        RETURN NEXT $4;
    END;
$$ LANGUAGE plpgsql;

 客户端调用:
    BEGIN;

        SELECT * FROM fun_cursor(1,2,'a', 'b');

        FETCH ALL FROM a;

        FETCH ALL FROM b;

   COMMIT;

1/3