一、行转列
SELECT
string_agg(id::varchar, '-') AS "INT数据行转String列",
string_agg(mobile, '-') AS "VARCHAR数据行转String列",
array_agg(id) AS "INT数据转数组",
pg_typeof(array_agg(id)) AS "INT数据转数组后的类型",
array_agg(mobile) AS "VARCHAR数据转数组",
pg_typeof(array_agg(mobile)) AS "INT数据转数组后的类型",
json_agg(id) AS "INT数据转JSON数组",
json_agg(mobile) AS "VARCHAR数据转JSON数组"
FROM "user";
二、数组转换
SELECT
"group" AS "数组数据",
array_to_json("group") AS "数组转JSON数组数据"
array_to_string("group",'_') AS "数组转字符串数据",
string_to_array(array_to_string("group",'_'), '_') AS "字符串转数组数据",
pg_typeof("group") AS "数组数据类型",
pg_typeof(array_to_json("group")) AS "JSON数据类型"
FROM "user"
三、JSON数组 转 数组
SELECT
array_agg(vaule) AS "JSON数组转数组",
array_to_json(array_append(array_agg(vaule), json_build_object('name', 'Gadfly02', 'age', 44))) AS "JSON数组后添加数据",
array_to_json(array_prepend(json_build_object('name', 'Gadfly02', 'age', 44), array_agg(vaule))) AS "JSON数组前添加数据",
array_to_json(array_cat(ARRAY[json_build_object('name', 'Gadfly02', 'age', 44), json_build_object('name', 'Gadfly02', 'age', 45)], array_agg(vaule))) AS "JSON数组前添加数据"
FROM json_array_elements(json_build_array(json_build_object('name', 'Gadfly00', 'age', 43), json_build_object('name', 'Gadflyd01', 'age', 43))) as vaule;
四、获取insert插入的新纪录的id值
使用PostgreSQL的RETURNING语句来实现插入时快速获取insert id
在INSERT INTO或者UPDATE的时候在最后面加上RETURNING colname，PostgreSQL会在插入或者更新数据之后会返回你指定的字段。
INSERT INTO test(name) values('name') RETURNING id;
五、PostgreSQL 动态SQL语句
- 动态SQL语句中正确使用SELECT INTO语句
sqlstring:= 'select max("' || idname || '") from "' || tablename || '";';
EXECUTE sqlstring into currentId;
- 在用户自定义函数中用 RETURN QUERY 返回一个表
CREATE or Replace FUNCTION func_get_task_view(
sdkv integer)
RETURNS setof task_view AS $$
Declare
queryString varchar(260);
Begin
queryString := 'select * from task_view where sdvk = $1 order by id desc';
return query execute queryString using sdkv;
return;
End;
$$ LANGUAGE plpgsql;
