SQL

Postgresql 网址转码 urldecode 函数

flyingbamboo 提交于 周四, 09/20/2018 - 12:46

CREATE OR REPLACE FUNCTION urldecode_arr(url TEXT)
  RETURNS TEXT AS
$BODY$
DECLARE ret TEXT;

BEGIN
  BEGIN

    WITH STR AS (
        SELECT
          -- array with all non encoded parts, prepend with '' when the string start is encoded
          CASE WHEN $1 ~ '^%[0-9a-fA-F][0-9a-fA-F]'
            THEN ARRAY ['']
          END
          || regexp_split_to_array($1, '(%[0-9a-fA-F][0-9a-fA-F])+', 'i')                plain,

          -- array with all encoded parts
          array(SELECT (regexp_matches($1, '((?:%[0-9a-fA-F][0-9a-fA-F])+)', 'gi')) [1]) encod

Postgresql 身份证 15位转18位 校验 函数

flyingbamboo 提交于 周四, 09/20/2018 - 12:45

CREATE OR REPLACE FUNCTION sfz15to18check(VARCHAR)
  RETURNS TABLE(idcard VARCHAR, judge INTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
  idcard        VARCHAR;
  is_idcard     INTEGER;
  v_i           INTEGER;
  idcard_length INTEGER;
  v_s           VARCHAR;
  v_sum         INTEGER;
  v_array1      INTEGER [];
  v_array2      VARCHAR [];
BEGIN
  idcard := trim($1);
  idcard_length := char_length(idcard);
  v_array1 := ARRAY [7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2];
  v_array2 := ARRAY ['1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2'];

  IF idcar

ubuntu下postgresql 10 或新版本安装

flyingbamboo 提交于 周四, 09/20/2018 - 12:44

# 如果要安装不在ubuntu软件仓库中的postgresql,需要先添加apt源;
apt源在 https://www.postgresql.org/download/linux/ubuntu/ 可查询

Ubuntu Xenial (16.04)

sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update

# 查询各版本及模块
apt-cache search postgresql

# 安装
apt-get install postgresql-10

# 添加新用户和新数据库
# 创建数据库用户dbuser,并指定其为超级用户
sudo -u postgres createuser --superuser dbuser