[PostgreSQL] Function2

이번에는 입력받는 매개변수 없이 현재 데이터 베이스의 모든 스키마 내의 베이스 테이블을 가져오는 함수를 생성해봅니다.

create or replace function get_all_tables()
returns table(schema_name text, table_name text) as
$$
begin
     return query
select table_schema, table_name
from information_schema.tables
where table_type='BASE TABLE';
end;
$$
language plpgsql;

ERROR: column reference "table_name" is ambiguous

이전 Function1에서의 포스팅에서와 같이 from절 아래 테이블을 별칭을 사용하여 구체화해줘야한다.

create or replace function get_all_tables()
returns table(schema_name text, table_name text) as
$$
begin
    return query
select t.table_schema, t.table_name
from information_schema.tables t
where t.table_type='BASE TABLE';
end;
$$
language plpgsql;

ERROR: structure of query does not match function result type

이 에러 역시 Function1 포스팅에서 발견한 에러이다.
타입 캐스팅을 통해 데이터 타입을 맞춰준다.

create or replace function get_all_tables()
returns table(schema_name text, table_name text) as 
$$
begin
    return query 
	select t.table_schema::text, t.table_name::text 
	from information_schema.tables t
	where t.table_type::text='BASE TABLE';
end;
$$
language plpgsql;

아래와 같이 정상적으로 함수가 실행됨을 확인할 수 있다.

postgres=# select * from get_all_tables() ;
    schema_name     |       table_name
--------------------+-------------------------
 pg_catalog         | pg_statistic
 pg_catalog         | pg_type
 public             | pgadmin_ddl
 public             | test_role
 pg_catalog         | pg_foreign_table
 pg_catalog         | pg_authid
 pg_catalog         | pg_statistic_ext_data
 pg_catalog         | pg_user_mapping
 pg_catalog         | pg_subscription
 pg_catalog         | pg_attribute
 pg_catalog         | pg_proc
 pg_catalog         | pg_class
 pg_catalog         | pg_attrdef
 pg_catalog         | pg_constraint
 pg_catalog         | pg_inherits
 pg_catalog         | pg_index
 pg_catalog         | pg_operator
 pg_catalog         | pg_opfamily
 pg_catalog         | pg_opclass
 pg_catalog         | pg_am
 pg_catalog         | pg_amop
 pg_catalog         | pg_amproc
 pg_catalog         | pg_language
 pg_catalog         | pg_largeobject_metadata
 pg_catalog         | pg_aggregate
 pg_catalog         | pg_statistic_ext
 pg_catalog         | pg_rewrite
 pg_catalog         | pg_trigger
 pg_catalog         | pg_event_trigger
 pg_catalog         | pg_description
 pg_catalog         | pg_cast
 pg_catalog         | pg_enum
 pg_catalog         | pg_namespace
 pg_catalog         | pg_conversion
 pg_catalog         | pg_depend
 pg_catalog         | pg_database
 pg_catalog         | pg_db_role_setting
 pg_catalog         | pg_tablespace
 pg_catalog         | pg_auth_members
 pg_catalog         | pg_shdepend
 pg_catalog         | pg_shdescription
 pg_catalog         | pg_ts_config
 pg_catalog         | pg_ts_config_map
 pg_catalog         | pg_ts_dict
 pg_catalog         | pg_ts_parser