将函数动态应用于postgres表中的所有列 [英] Apply function to all columns in a Postgres table dynamically
本文介绍了将函数动态应用于postgres表中的所有列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
使用postgres 13.1，我希望将前向填充函数应用于表的所有列。前向填充功能在我前面的问题中进行了解释：
但是，在这种情况下，将指定列和表。我想将该代码应用于任意表，即。指定一个表，并对每一列应用正向填充。以下表为例：
CREATE TABLE example(row_num int, id int, str text, val integer);
INSERT INTO example VALUES
(1, 1, '1a', NULL)
, (2, 1, NULL, 1)
, (3, 2, '2a', 2)
, (4, 2, NULL, NULL)
, (5, 3, NULL, NULL)
, (6, 3, '3a', 31)
, (7, 3, NULL, NULL)
, (8, 3, NULL, 32)
, (9, 3, '3b', NULL)
, (10,3, NULL, NULL)
;
我从该函数的以下工作基础开始。我称其为传入一些变量名。注第一个是表名称，而不是列名。该函数获取表名并创建包含所有列名的数组，然后输出这些名称。
create or replace function col_collect(tbl text, id text, row_num text)
returns text[]
language plpgsql as
$func$
declare
tmp text[];
col text;
begin
select array (
select column_name
from information_schema."columns" c
where table_name = tbl
) into tmp;
foreach col in array tmp
loop
raise notice 'col: %', col;
end loop;
return tmp;
end
$func$;
我想将从前面的问题中获得的"；Forward Fill"；函数应用到表的每一列。UPDATE似乎是正确的方法。这是前面的函数，我使用execute将raise notice替换为更新，这样我就可以传入表名：
create or replace function col_collect(tbl text, id text, row_num text)
returns void
language plpgsql as
$func$
declare
tmp text[];
col text;
begin
select array (
select column_name
from information_schema."columns" c
where table_name = tbl
) into tmp;
foreach col in array tmp
loop
execute 'update '||tbl||'
set '||col||' = gapfill('||col||') OVER w AS '||col||'
where '||tbl||'.row_num = '||col||'.row_num
window w as (PARTITION BY '||id||' ORDER BY '||row_num||')
returning *;';
end loop;
end
$func$;
-- call the function
select col_collect('example','id','row_num')
前面的错误由于语法错误而输出。我尝试了很多不同的版本，但都失败了。关于SO的有用答案是here和here。我尝试应用的聚合函数(作为窗口函数)是：
CREATE OR REPLACE FUNCTION gap_fill_internal(s anyelement, v anyelement)
RETURNS anyelement
LANGUAGE plpgsql AS
$func$
BEGIN
RETURN COALESCE(v, s); -- that's all!
END
$func$;
CREATE AGGREGATE gap_fill(anyelement) (
SFUNC = gap_fill_internal,
STYPE = anyelement
);
我的问题是：
- 这是一个好方法吗？如果是，我做错了什么；或者
- 有没有更好的方法？
推荐答案
您所问的不是一项微不足道的任务。您应该熟悉PL/pgSQL。我不建议初学者使用这种动态SQL查询，功能太强大了。
这就是说，让我们开始吧。系好安全带！
CREATE OR REPLACE FUNCTION f_gap_fill_update(_tbl regclass, _id text, _row_num text, OUT nullable_columns int, OUT updated_rows int)
LANGUAGE plpgsql AS
$func$
DECLARE
_pk text := quote_ident(_row_num);
_sql text;
BEGIN
SELECT INTO _sql, nullable_columns
concat_ws(E'
'
, 'UPDATE ' || _tbl || ' t'
, 'SET (' || string_agg( quote_ident(a.attname), ', ') || ')'
, ' = (' || string_agg('u.' || quote_ident(a.attname), ', ') || ')'
, 'FROM ('
, ' SELECT ' || _pk
, ' , ' || string_agg(format('gap_fill(%1$I) OVER w AS %1$I', a.attname), ', ')
, ' FROM ' || _tbl
, format(' WINDOW w AS (PARTITION BY %I ORDER BY %s)', _id, _pk)
, ' ) u'
, format('WHERE t.%1$s = u.%1$s', _pk)
, 'AND (' || string_agg('t.' || quote_ident(a.attname), ', ') || ') IS DISTINCT FROM'
, ' (' || string_agg('u.' || quote_ident(a.attname), ', ') || ')'
)
, count(*) -- AS _col_ct
FROM (
SELECT a.attname
FROM pg_attribute a
WHERE a.attrelid = _tbl
AND a.attnum > 0
AND NOT a.attisdropped
AND NOT a.attnotnull
ORDER BY a.attnum
) a;
IF nullable_columns = 0 THEN
RAISE EXCEPTION 'No nullable columns found in table >>%<<', _tbl;
ELSIF _sql IS NULL THEN
RAISE EXCEPTION 'SQL string is NULL. Should not occur!';
END IF;
-- RAISE NOTICE '%', _sql; -- debug
EXECUTE _sql; -- execute
GET DIAGNOSTICS updated_rows = ROW_COUNT;
END
$func$;
示例调用：
SELECT * FROM f_gap_fill_update('example', 'id', 'row_num');
小提琴here
该功能是最先进的。 生成和执行以下形式的查询：
UPDATE tbl t
SET (str, val, col1)
= (u.str, u.val, u.col1)
FROM (
SELECT row_num
, gap_fill(str) OVER w AS str, gap_fill(val) OVER w AS val
, gap_fill(col1) OVER w AS col1
FROM tbl
WINDOW w AS (PARTITION BY id ORDER BY row_num)
) u
WHERE t.row_num = u.row_num
AND (t.str, t.val, t.col1) IS DISTINCT FROM
(u.str, u.val, u.col1)
使用pg_catalog.pg_attribute而不是信息架构。请参阅：
请注意最后的WHERE子句，以防止(可能是昂贵的)空更新。只会写入实际更改的行。请参阅：
此外，甚至只考虑可为空的列(未定义NOT NULL)，以避免不必要的工作。
在UPDATE中使用ROW语法以保持代码简单。请参阅：
该函数返回两个整数值：nullable_columns和updated_rows，报告名称的含义。
该函数可正确防御SQL注入。请参阅：
- Table name as a PostgreSQL function parameter
- SQL injection in Postgres functions vs prepared queries
上述函数会更新，但不返回行。以下是如何返回各种类型的行的基本演示：
CREATE OR REPLACE FUNCTION f_gap_fill_select(_tbl_type anyelement, _id text, _row_num text)
RETURNS SETOF anyelement
LANGUAGE plpgsql AS
$func$
DECLARE
_tbl regclass := pg_typeof(_tbl_type)::text::regclass;
_sql text;
BEGIN
SELECT INTO _sql
'SELECT ' || string_agg(CASE WHEN a.attnotnull
THEN format('%I', a.attname)
ELSE format('gap_fill(%1$I) OVER w AS %1$I', a.attname) END
, ', ' ORDER BY a.attnum)
|| E'
FROM ' || _tbl
|| format(E'
WINDOW w AS (PARTITION BY %I ORDER BY %I)', _id, _row_num)
FROM pg_attribute a
WHERE a.attrelid = _tbl
AND a.attnum > 0
AND NOT a.attisdropped;
IF _sql IS NULL THEN
RAISE EXCEPTION 'SQL string is NULL. Should not occur!';
END IF;
RETURN QUERY EXECUTE _sql;
-- RAISE NOTICE '%', _sql; -- debug
END
$func$;
Call(注意特殊语法！)：
SELECT * FROM f_gap_fill_select(NULL::example, 'id', 'row_num');
小提琴here
关于返回多态行类型：
这篇关于将函数动态应用于postgres表中的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
