如何在动态 postgresql 中过滤表 [英] How to filter table in dynamic postgresql

问题描述

我想添加一个检查，以确定特定表是否包含名称"列然后只执行我的查询(在我的情况下，它每次在任何表中找不到名称"列时都会抛出错误).我很困惑在哪里使用EXISTS"我的查询中的语句.

I want to add a check that if a particular table contains "names" column then only execute my query(in my case it's throwing an error each time it didn't find "names" column in any of the tables). I am confused where to use that "EXISTS" statement in my query.

create or replace function name_list(schemaname text, tablename text) 
 
 RETURNS SETOF text

language 'plpgsql'

as $body$

declare

  _query text;

begin


  _query := FORMAT('SELECT "names" FROM %I.%I;', schemaname, tablename);

  RAISE NOTICE '"%"' , _query;


  RETURN QUERY EXECUTE _query;

end;

$body$;

copy(select 

  table_name, 

  name_list(table_schema, table_name)

  from information_schema.tables)
 to 'C:\test\name_list.csv' DELIMITER ',' CSV HEADER;

推荐答案

变体 1:在复制命令中使用 information_schema.columns:

variant 1: use information_schema.columns at the copy command:

copy(select t.table_name, name_list(t.table_schema, t.table_name)
  from information_schema.tables AS t
  JOIN information_schema.columns AS c
    ON (c.table_schema, c.table_name, c.column_name) = 
       (t.table_schema, t.table_name, 'names')
)
 to 'C:\test\name_list.csv' DELIMITER ',' CSV HEADER;

变体2:更改函数以跳过坏"表

variant 2: change the function to skip "bad" tables

create or replace function name_list(schemaname text, tablename text) 
  RETURNS SETOF text
language 'plpgsql'
as $body$
declare
  _query text;
begin
  IF EXISTS(
      SELECT * FROM information_schema.columns AS c
      WHERE (c.table_schema, c.table_name, c.column_name) = 
            (schemaname, tablename, 'names')
  ) THEN
    _query := FORMAT('SELECT "names" FROM %I.%I;', schemaname, tablename);
    RAISE NOTICE '"%"' , _query;
    RETURN QUERY EXECUTE _query;
  ELSE
    RETURN;
  END IF;
end;
$body$;

这篇关于如何在动态 postgresql 中过滤表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！