pgsql动态创建table

SQL动态创建表与字符串操作
本文详细介绍了如何使用SQL动态创建表,并演示了字符串连接、长度计算、替换、位置查找、截取、大小写转换及删除特定字符串的操作。通过实例展示了如何在PostgreSQL环境中灵活运用SQL与字符串操作。

先上SQL

CREATE OR REPLACE FUNCTION createtable_urllist_withdate()
  RETURNS varchar AS
$BODY$
declare 
    tableNameWithDate varchar;
    tableCreateSQL varchar;
    currentDate varchar;

BEGIN
    SELECT INTO currentDate to_char(current_date, 'yyyymmdd');

    tableNameWithDate:= 'tb_url_list_'||currentDate;

    if not exists(select 1 from pg_tables where tablename = tableNameWithDate)
    then
    tableCreateSQL := 'create table ' || tableNameWithDate || '(
    id  integer primary key,
    URLlevel integer,
    URL varchar(100),
    title varchar(50)
    )';
    RAISE NOTICE '开始创建表%',tableCreateSQL;
        EXECUTE(tableCreateSQL);
    RAISE NOTICE '成功创建表%',tableCreateSQL;
    end if;

    return tableNameWithDate;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION createurltablewithdate()
  OWNER TO postgres;
   

1.SELECT INTO

生成多个列(但只有一行)的SELECT命令的结果可以赋予一个记录变量, 行类型变量,或者一个标量变量的列表。这是用下面方法实现的:

SELECT INTO target select_expressions FROM ...;

这里的 target 可以是一个记录变量, 行变量,或者一个用逗号分隔的简单变量和记录/行字段的列表。 select_expressions 和命令的剩余部分和普通 SQL 一样。

请注意这个构造和 PostgreSQL 普通的SELECT INTO构造的解释是不一样的, 后者的INTO目标是一个新创建的表。 (如果你想在 PL/pgSQL 函数里从一个SELECT 的结果中创建一个表,那么使用 CREATE TABLE ... AS SELECT 语法。)

也就是说如果是平时使用sql的时候,如果使用select into是插入一个表,但是在pgsql中使用的时候就是插入一条记录

2.字符串操作符

字符串连接符: ||

字符串长度:bit_length(字段),char_length(字段)--中文字符算一个字长,octet(字段)--中文字符按编码算字长,例如UTF-8则一个中文字符一般是3个字长

字符串替换:overlay(字段 placing 替换string from int [for int]) 例如:select overlay(lastname placing 'gg' from 7 for 2)意思是将lastname字段中的值从第7个字符开始,用'gg'替换两个字符

字符串中某个字符的位置: position(substring in string) 例如:select position('g' in lastname)

字符串截取: substring(string from int [for int])例如:select substring(lastname from 2 for 2);substring(string from pattern)例如:select substring(lastname from '^..')

字符串大小写:lower(string),upper(string)

字符串中删除某个字符串 trim(substring from string)默认为空白符 例如:trim('123' from lastname)

通过ascii返回字符,或者通过字符返回ascii 例如:select ascii('a'); select chr('97')

3.刚开始的时候我直接创建table,发现不能给表名动态赋值。后来查了下,通过迂回能够实现,先将"create table……"字符串赋值给一个临时变量,这个时候表名已经生成,并且赋值给了临时变量,然后使用EXECUTE(string)执行。

 

一个比较详细的postgresql中文文档资料链接:http://www.kuqin.com/postgreSQL8.1_doc/

转载于:https://www.cnblogs.com/charlexu/archive/2013/03/11/2954277.html

<think>我们被要求解决PostgreSQL中动态行转多列的问题。动态行转列意味着我们不知道需要转置成多少列,这通常需要根据数据内容动态生成列名。 根据提供的引用,有几种方法: 1. 使用tablefunc模块中的crosstab函数(引用[2])可以实现行转列,但crosstab通常需要预先知道转置后的列名,对于动态列的情况,我们需要使用动态SQL。 2. 使用STRING_AGG函数结合字符串分割(引用[3]和[5])可以先将多行合并成一个字符串,然后再分割成多列,但这种方法需要预先知道最多有多少列,且不够动态。 3. 使用jsonb_object_agg(引用[4])将行数据转成JSON对象,然后通过JSON字段提取成列,这种方法在列名已知的情况下比较直接,但同样需要预先知道列名。 对于动态行转列(即列数不确定,根据数据动态变化),通常需要编写存储过程或使用PL/pgSQL来构造动态SQL。 引用[2]提到使用crosstab实现动态行转列,并附有案例。我们可以参考这个思路。 步骤: 1. 安装tablefunc扩展:CREATE EXTENSION IF NOT EXISTS tablefunc; 2. 使用crosstab函数,但crosstab有两个变种:crosstab(text source_sql)和crosstab(text source_sql, text category_sql)。动态列的问题在于结果集的列名和数量是变化的,因此我们需要动态构建查询。 动态行转列的一般步骤(使用PL/pgSQL): a. 获取所有不同的类别(即列名)。 b. 构建动态SQL,包括选择行标识符和每个类别的聚合值。 c. 执行动态SQL。 由于在普通SQL中无法直接返回动态列数的结果,因此通常使用函数来返回一个动态的记录集。 以下是一个示例,假设我们有一个表sales_data: id integer, product text, amount numeric 我们想将每个产品作为列,显示其销售额。 步骤: 1. 获取所有产品(即列名): SELECT array_agg(DISTINCT product) FROM sales_data; 2. 构建动态SQL,使用crosstab函数,并动态构造列定义部分。 示例函数(动态行转列): ```sql CREATE OR REPLACE FUNCTION dynamic_crosstab() RETURNS TABLE(id int, ...) -- 这里列是动态的,无法预先定义 ``` 但是,由于返回类型必须确定,我们可以使用返回setof record,并在调用时指定列。 另一种方法是使用refcursor或者返回JSON,但用户要求的是动态列的结果集。 更常见的做法是使用动态SQL并执行,然后返回结果集。但是,在函数中返回动态列的结果集,我们可以在调用时指定列名,但这样不够灵活。 参考引用[2]中的案例,我们可以这样实现: 1. 构建类别列表,并生成crosstab查询语句。 2. 使用PL/pgSQL执行动态SQL。 下面是一个完整的示例函数: ```sql CREATE OR REPLACE FUNCTION dynamic_pivot() RETURNS void AS $BODY$ DECLARE column_list TEXT; pivot_query TEXT; BEGIN -- 获取所有不同的类别(列名) SELECT string_agg(DISTINCT quote_ident(product), ' numeric, ') INTO column_list FROM sales_data; -- 添加列名后的类型和逗号处理 IF column_list IS NOT NULL THEN column_list := column_list || ' numeric'; END IF; -- 构建动态查询,使用crosstab pivot_query := format( 'CREATE TEMP TABLE temp_pivot AS SELECT * FROM crosstab( ''SELECT id, product, amount FROM sales_data ORDER BY 1,2'', ''SELECT DISTINCT product FROM sales_data ORDER BY 1'' ) AS ct (id int, %s);', column_list ); EXECUTE pivot_query; END; $BODY$ LANGUAGE plpgsql; ``` 然后调用这个函数,它会创建一个临时表temp_pivot,然后我们可以从这个临时表中查询: SELECT * FROM temp_pivot; 但是这种方法需要两步,并且每次调用都会创建临时表。 另一种方法是使用更高级的动态返回类型,但需要较新版本的PostgreSQL(如使用record类型和返回游标,或者在应用层处理)。 在PostgreSQL 9.6及以上,我们可以使用jsonb或者json来返回动态结构,但用户要求的是传统的行列转换。 考虑到用户可能需要直接执行查询得到动态列的结果,我们可以提供动态生成SQL并执行的方法,但需要用户自己运行生成的SQL。 然而,在应用层,通常由应用程序构建动态SQL并执行。 因此,我们可以这样回答: 在PostgreSQL中,要实现动态行转列(即列数不确定),可以使用PL/pgSQL编写函数来动态构建并执行SQL语句。核心步骤包括: 1. 获取需要转置的列名(即类别)。 2. 使用这些列名构建crosstab函数的查询语句。 3. 执行动态SQL。 由于crosstab函数要求返回的列定义必须与类别匹配,因此我们需要在函数中动态构造列定义部分。 以下是一个通用示例,假设原始表有行标识(如id)、类别列(category)和值列(value): ```sql CREATE OR REPLACE FUNCTION dynamic_crosstab( source_sql TEXT, -- 提供原始数据的SQL,必须返回三列:行标识,类别,值 category_sql TEXT -- 提供类别排序的SQL,返回一列:类别 ) RETURNS TABLE (dynamic_record RECORD) AS $BODY$ DECLARE column_list TEXT; final_sql TEXT; rec RECORD; BEGIN -- 首先执行category_sql获取所有类别,并构建列定义字符串 EXECUTE category_sql INTO column_list; -- 注意:这里需要调整,因为category_sql返回多行 -- 正确做法:遍历类别结果集 column_list := ''; FOR rec IN EXECUTE category_sql LOOP -- 假设类别列名为category(在category_sql中返回的列名) column_list := column_list || quote_ident(rec.category) || ' numeric,'; END LOOP; -- 移除最后一个逗号 column_list := TRIM(TRAILING ',' FROM column_list); -- 构建最终查询 final_sql := format( 'SELECT * FROM crosstab(%L, %L) AS ct (row_id int, %s)', source_sql, category_sql, column_list ); -- 返回动态记录集 RETURN QUERY EXECUTE final_sql; END; $BODY$ LANGUAGE plpgsql; ``` 但是,上述函数在返回类型上仍然需要调用者知道列定义,否则无法使用。因此,在调用时,我们需要使用一个具体类型或者使用匿名块。 实际上,在PostgreSQL中,我们可以使用以下方式调用: ```sql SELECT * FROM dynamic_crosstab( 'SELECT id, category, value FROM my_table', 'SELECT DISTINCT category FROM my_table ORDER BY 1' ) AS ct(row_id int, col1 numeric, col2 numeric, ...); ``` 但这样仍然需要预先知道列名和数量,所以并没有完全动态。 因此,更实用的方法是在应用程序中构建动态SQL,或者使用报表工具。 另外,引用[3]和[4]提供了使用JSON聚合的方法,这样返回的是一个JSON对象,其中键是类别,值是值列。这样就不需要动态列了,因为所有动态列都包含在一个JSON字段中。这种方法更适合在应用层处理。 示例: ```sql SELECT id, jsonb_object_agg(category, value) AS data FROM my_table GROUP BY id; ``` 然后,在应用程序中解析JSON对象,将其转换为所需的列。 总结: 1. 如果需要在SQL查询中直接得到动态列的结果,可以使用PL/pgSQL函数动态构建并执行SQL,但调用时仍然需要指定列(或使用临时表)。 2. 使用JSON聚合(如jsonb_object_agg)将动态列合并为一个JSON字段,然后在应用程序中处理。 根据用户需求,他们希望实现动态行转多列,因此我们提供两种方案,并给出示例。 由于问题中要求动态行转多列,我们重点参考引用[2]中的动态行转列案例。 引用[2]提供了一个案例,我们直接引用: ```sql -- 安装扩展 CREATE EXTENSION tablefunc; -- 创建示例表 CREATE TABLE tb_score( id SERIAL PRIMARY KEY, name VARCHAR(10), subject VARCHAR(10), score NUMERIC ); INSERT INTO tb_score(name, subject, score) VALUES ('张三', '语文', 80), ('张三', '数学', 90), ('张三', '英语', 85), ('李四', '语文', 85), ('李四', '数学', 92), ('李四', '英语', 88); -- 动态行转列函数 CREATE OR REPLACE FUNCTION func_dynamic_pivot() RETURNS void AS $BODY$ DECLARE column_list TEXT; pivot_sql TEXT; BEGIN -- 获取所有科目 SELECT string_agg(DISTINCT subject, ',') INTO column_list FROM tb_score; -- 注意:这里需要为每个科目构建列定义,如语文 NUMERIC, 数学 NUMERIC, 英语 NUMERIC column_list := replace(column_list, ',', ' NUMERIC,') || ' NUMERIC'; -- 构建动态SQL pivot_sql := format( 'CREATE TEMP TABLE temp_pivot AS SELECT * FROM crosstab( ''SELECT name, subject, score FROM tb_score ORDER BY 1'', ''SELECT DISTINCT subject FROM tb_score ORDER BY 1'' ) AS ct (name VARCHAR(10), %s)', column_list ); EXECUTE pivot_sql; END; $BODY$ LANGUAGE plpgsql; -- 调用函数 SELECT func_dynamic_pivot(); -- 查询临时表 SELECT * FROM temp_pivot; ``` 这样,临时表temp_pivot就会有动态的科目列。 但是,这种方法需要创建临时表,并且每次调用函数都会创建新的临时表(可能覆盖之前的)。 为了更通用,我们可以修改函数,使其返回一个记录集,但需要调用时指定列类型。由于动态性,在函数内创建临时表可能是最直接的方法。 因此,我们建议使用临时表的方法,或者使用JSON聚合。 根据用户需求,我们提供两种方案: 方案一:使用PL/pgSQL函数和临时表(适合在数据库内直接查看结果) 方案二:使用JSON聚合(适合在应用层处理) 方案一示例(如上): 1. 创建函数执行动态crosstab,并创建临时表。 2. 查询临时表。 方案二示例(使用jsonb_object_agg): ```sql SELECT name, jsonb_object_agg(subject, score) AS scores FROM tb_score GROUP BY name; ``` 结果: name | scores ------+------------------------------------ 张三 | {"数学": 90, "英语": 85, "语文": 80} 李四 | {"数学": 92, "英语": 88, "语文": 85} 然后,在应用程序中,可以将scores字段的JSON对象展开成所需的列。 如果用户确实需要在SQL中展开成列,且列名已知(即使动态,但后续查询已知),那么可以使用方案一创建的临时表。 但是,如果列名完全未知且需要一次性查询,那么方案一更合适。 另外,引用[5]提供了一种使用string_agg和split_part的方法,但这种方法需要预先知道最多有多少列,并且需要为每一列写一个split_part,不够动态。 因此,我们推荐使用crosstab动态创建临时表或使用JSON聚合。 根据用户问题,他们要求动态行转多列,因此我们重点提供方案一(动态SQL创建临时表)和方案二(JSON聚合)的详细步骤。 由于问题中引用[2]明确提到“PostgreSQL中crosstab实现动态行转列”,我们以该引用为主。 因此,回答如下:</think>### PostgreSQL 动态行转多列实现方法 在 PostgreSQL 中实现**动态行转多列**(列数不确定)的核心解决方案是使用 `crosstab()` 函数结合动态 SQL 和 PL/pgSQL 编程。以下是详细实现步骤: #### 方法 1:使用 crosstab() 和动态 SQL(推荐) ```sql -- 1. 启用扩展 CREATE EXTENSION IF NOT EXISTS tablefunc; -- 2. 创建动态转换函数 CREATE OR REPLACE FUNCTION dynamic_pivot() RETURNS TABLE (json_data jsonb) AS $$ DECLARE col_list TEXT; sql_query TEXT; BEGIN -- 动态获取列名(示例:根据 subject 字段) SELECT string_agg(DISTINCT format('"%s"', subject), ', ') INTO col_list FROM your_table; -- 构建动态查询 sql_query := format( 'SELECT jsonb_object_agg(row_name, row_data) FROM (' 'SELECT row_id AS row_name, ' 'jsonb_build_object(%s) AS row_data ' 'FROM crosstab(' '''SELECT row_id, subject, value FROM your_table ORDER BY 1,2'', ' '''SELECT DISTINCT subject FROM your_table ORDER BY 1''' ') AS ct (row_id TEXT, %s)) sub', col_list, col_list ); -- 执行并返回 JSONB 结果 RETURN QUERY EXECUTE sql_query; END; $$ LANGUAGE plpgsql; -- 3. 调用函数 SELECT * FROM dynamic_pivot(); ``` **输出示例**: ```json { "row1": {"math": 90, "physics": 85}, "row2": {"math": 78, "chemistry": 92} } ``` #### 方法 2:使用 JSONB 聚合(简化版) ```sql SELECT row_id, jsonb_object_agg(subject, value) AS dynamic_columns FROM your_table GROUP BY row_id; ``` #### 关键组件说明: 1. **`crosstab()` 函数**: - 需先启用 `tablefunc` 扩展[^2] - 语法:`crosstab(source_sql, category_sql)` - 自动将行值转换为列名 2. **动态列处理**: - 使用 `string_agg()` 动态生成列名列表 - 通过 `EXECUTE` 执行动态构建的 SQL - 结果返回 JSONB 格式保证灵活性 3. **JSONB 优势**: - 无需预知列数量/名称 - 可直接查询嵌套数据:`json_data->'row1'->>'math'` - 支持二进制存储(高效)[^4] #### 实际应用示例 假设有学生成绩表: ```sql CREATE TABLE scores ( student_id INT, subject TEXT, score INT ); INSERT INTO scores VALUES (1, 'math', 90), (1, 'physics', 85), (2, 'math', 78), (2, 'chemistry', 92); ``` 执行动态转换后得到: ```json { "1": {"math": 90, "physics": 85}, "2": {"math": 78, "chemistry": 92} } ``` #### 注意事项 1. 动态列方法需 PL/pgSQL 支持 2. 大数据集建议在 JSONB 字段上创建 GIN 索引 3. 列名中特殊字符需用 `quote_ident()` 处理 4. 确保 `row_id` + `subject` 组合唯一
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值