postgresql - 将 FUNCTION (Postgres 9.1) 迁移到 Postgres 8.0 (AWS Redshift)
问题描述
我正在寻找一种在 Postgres 中的所有表和所有列中搜索特定值的方法,我发现了这个。
CREATE OR REPLACE FUNCTION search_columns(
needle text,
haystack_tables name[] default '{}',
haystack_schema name[] default '{}'
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $$
begin
FOR schemaname,tablename,columnname IN
SELECT c.table_schema,c.table_name,c.column_name
FROM information_schema.columns c
JOIN information_schema.tables t ON
(t.table_name=c.table_name AND t.table_schema=c.table_schema)
WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
AND (c.table_schema=ANY(haystack_schema) OR haystack_schema='{}')
AND t.table_type='BASE TABLE'
LOOP
EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
schemaname,
tablename,
columnname,
needle
) INTO rowctid;
IF rowctid is not null THEN
RETURN NEXT;
END IF;
END LOOP;
END;
$$ language plpgsql;
但问题是:我想在 AWS Redshift 中执行它,即使用 Postgres 8.0,而此代码仅适用于 Postgres 9.1+。
有人可以帮助我迁移此功能以与 Postgres 8.0 兼容或告知另一种方式来做到这一点?
谢谢!!
解决方案
推荐阅读
- r - Call function on its own output, N times
- ms-access - Trouble with using a form to search multiple dates in Access and return a query
- machine-learning - My DC-GAN on grayscale face images is not training well
- c# - How to use DotNet Core 5.0 authentication with Next.js
- c++ - Visual Studio Refuses to pause after debugging
- c++ - I have an error in my c++ code Adress Sanitizer errer keeps poping up
- python - How to fetch date and time from internet
- azure - Adding CMK(Customer Managed Key) failing for empty Azure data factory
- kentico - 论坛需要为认证用户手动输入用户名和电子邮件?
- r - 如何仅提取具有多个类的对象类之一