Postgresql
plpgsql函式的排序輸出
我正在 Postgres 9.6 中創建一個函式,將兩個模式與相同的表進行比較。我想找到在一個模式中為空但在另一個模式中填充的表。我正在避免
reltuples
在資訊模式中找到的屬性,因為不能保證它是正確的。到目前為止,我有這個功能:
CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(schema1 text, schema2 text) RETURNS TABLE(tablename text, schema1_ct integer, schema2_ct integer, match boolean) AS $$ DECLARE schema1_tables CURSOR FOR select pg_tables.tablename from pg_tables where schemaname ~ schema1 order by tablename; schema1_ct int; schema2_ct int; BEGIN FOR table_record IN schema1_tables LOOP EXECUTE 'SELECT count(*) FROM ' || schema1 || '.' || table_record.tablename INTO schema1_ct EXECUTE 'SELECT count(*) FROM ' || schema2 || '.' || table_record.tablename INTO schema2_ct RETURN QUERY EXECUTE 'SELECT ''' || table_record.tablename || '''::text,' || schema1_ct || ',' || schema2_ct || ',' (schema1_ct >= 0) = (schema2_ct >= 0) END LOOP END; $$ LANGUAGE pgpgsql
但是我真的只對輸出的列為 的情況感興趣,即一張
match
表false
為空,一張表不為空。如何對輸出進行排序,以便match = false
首先顯示該行?
直接從循環返回行時,排序順序由循環確定。PL/pgSQL 建構結果集,您仍然可以引發異常以防止函式在最後一刻返回任何內容。(手冊中有程式碼範例。)但是沒有辦法在最終返回之前對結果集進行重新排序。您必須為此添加
ORDER BY
到函式呼叫中:SELECT * FROM public.compare_schemas_by_table_emptiness('foo', 'bar') ORDER BY match;
FALSE
排序之前TRUE
,所以不匹配首先出現。但我真的只對輸出
match
列是false
.所以不要做超過要求的工作。此功能可以滿足您的需求,高效且安全地防止 SQL 注入(與您的原始功能不同):
CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(_schema1 text, _schema2 text) RETURNS TABLE(tablename text, schema1_ct bigint, schema2_ct bigint) LANGUAGE plpgsql AS $func$ BEGIN FOR tablename IN SELECT quote_ident(t.tablename) -- must be table-qualified, due to dupe in OUT parameters! FROM pg_catalog.pg_tables t WHERE t.schemaname = _schema1 ORDER BY 1 LOOP EXECUTE format( 'SELECT (SELECT count(*) FROM %1$I.%3$s) , (SELECT count(*) FROM %2$I.%3$s) WHERE EXISTS (SELECT FROM %1$I.%3$s) <> EXISTS (SELECT FROM %2$I.%3$s)' , _schema1, _schema2, tablename) INTO schema1_ct, schema2_ct; CONTINUE WHEN schema1_ct IS NULL; -- happens when both or none have values RETURN NEXT; END LOOP; END $func$;
稱呼:
SELECT * FROM public.compare_schemas_by_table_emptiness('foo', 'bar');
使用大表進行計數可能會很昂貴,因此此功能僅在需要時才計數。
標識符可能需要也可能不需要雙引號,並且必須像使用者輸入一樣對待以確保安全。看: