sql - Postgres 动态转换特定列

Question

我们有一个 SQL 服务器，但出于几个原因决定将所有内容都移植到一个 postgres 服务器上。我在重新创建一个 SQL Server 存储过程时遇到问题。数据每天上传到服务器。原始数据中的所有小数都没有小数点。该过程通过从列的定义中查找精度来动态确定小数点的放置位置。

ALTER procedure [dbo].[adddecimal] @tableName varchar(30)
AS

DECLARE @sql VARCHAR(MAX)
SET @sql = ''

SELECT @sql = @sql + 'UPDATE ' + @tableName + ' SET ' + c.name + ' = ' + c.name + '/ power(10,'+CONVERT(varchar(10), c.scale) + ');'

FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.types y ON c.system_type_id = y.system_type_id
WHERE t.name = @tableName AND y.name ='decimal';

exec(@sql);

这个的输出遵循这个结构

  UPDATE table1 SET col1 = col1/power(10,3); UPDATE table1 SET col2 = col2/power(10,5)'.....

我怎样才能在 postgres 中做同样的事情？？？ Postgres 不想将所有命令连接到一个可执行文件中。我得到这个错误

ERROR:  more than one row returned by a subquery used as an expression

这是我目前所拥有的

CREATE FUNCTION clean_emp2() RETURNS void AS 
  $body$
DECLARE
   prec integer := (select numeric_scale from information_schema.columns) ;
   column_nm varchar(50) := (select column_name from information_schema.columns where data_type like 'numeric');
   table_nm varchar(50) := 'test_table'; // will eventually be pulled from function call

BEGIN 
    UPDATE table_nm set column_nm = column_nm / power(10,prec)
END $body$ LANGUAGE plpgsql;

我知道我已经很接近解决方案了，但我不知道如何让过程返回一个由多个 UPDATE 语句组成的字符串。

Answer 1

使用聚合函数string_agg 将多个结果行连接成一个字符串。

那么查询只会返回一行，可以存储在一个变量中。