-- 创建新数据库
CREATE DATABASE mydb;

-- 创建新用户
CREATE USER myuser WITH PASSWORD 'mypassword';

-- 这将允许 myuser 在 mydb 数据库中创建表
GRANT CREATE ON DATABASE mydb TO myuser;

-- 1. 创建用户
CREATE USER rwuser WITH PASSWORD 'mypassword';

-- 2. 授权连接数据库
GRANT CONNECT ON DATABASE mydb TO rwuser;

-- 3. 授权使用 schema
GRANT USAGE ON SCHEMA public TO rwuser;

-- 4. 授权数据操作 (去指定数据库下)
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO rwuser;
--授权 public schema 下所有序列权限 (插入时自增序列需要)
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO rwuser;

-- 5. 设置 future tables 权限 (去指定数据库下)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO rwuser;
--保证以后新建的序列也自动授权：
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO rwuser;

-- 创建该的计数器sequence
CREATE SEQUENCE seq_test_id;
-- 设置 sequence 的开始值
SELECT setval('seq_test_id', 20);
-- 设置id的值，从计数器获取
ALTER TABLE "public".test ALTER COLUMN id SET DEFAULT nextval('seq_test_id');

SELECT setval('seq_test_id', max(id)) FROM m_md;

-- ----------------------------
-- Function structure for generate_create_table_statement
-- ----------------------------
DROP FUNCTION IF EXISTS "public"."generate_create_table_statement"("p_table_name" varchar);
CREATE OR REPLACE FUNCTION "public"."generate_create_table_statement"("p_table_name" varchar)
  RETURNS "pg_catalog"."text" AS $BODY$
DECLARE
    v_table_ddl   text;
    column_record record;
BEGIN
    FOR column_record IN 
        SELECT 
            b.nspname as schema_name,
            b.relname as table_name,
            a.attname as column_name,
            pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
            CASE WHEN 
                (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                 FROM pg_catalog.pg_attrdef d
                 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
                'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                              FROM pg_catalog.pg_attrdef d
                              WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
            ELSE
                ''
            END as column_default_value,
            CASE WHEN a.attnotnull = true THEN 
                'NOT NULL'
            ELSE
                'NULL'
            END as column_not_null,
            a.attnum as attnum,
            e.max_attnum as max_attnum
        FROM 
            pg_catalog.pg_attribute a
            INNER JOIN 
             (SELECT c.oid,
                n.nspname,
                c.relname
              FROM pg_catalog.pg_class c
                   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
              WHERE c.relname ~ ('^('||p_table_name||')$')
                AND pg_catalog.pg_table_is_visible(c.oid)
              ORDER BY 2, 3) b
            ON a.attrelid = b.oid
            INNER JOIN 
             (SELECT 
                  a.attrelid,
                  max(a.attnum) as max_attnum
              FROM pg_catalog.pg_attribute a
              WHERE a.attnum > 0 
                AND NOT a.attisdropped
              GROUP BY a.attrelid) e
            ON a.attrelid=e.attrelid
        WHERE a.attnum > 0 
          AND NOT a.attisdropped
        ORDER BY a.attnum
    LOOP
        IF column_record.attnum = 1 THEN
            v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
        ELSE
            v_table_ddl:=v_table_ddl||',';
        END IF;

        IF column_record.attnum <= column_record.max_attnum THEN
            v_table_ddl:=v_table_ddl||chr(10)||
                     '    '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
        END IF;
    END LOOP;

    v_table_ddl:=v_table_ddl||');';
    RETURN v_table_ddl;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

SELECT generate_create_table_statement('table_name');