SAK 図書館
SAK 図書館
PostgreSQL 編24 - PL/pgSQL、テーブル構造一覧表示、標準、動的SQL、LIKE
■テーブル構造一覧表示(標準例)
・各テーブルのテーブル構造一覧を表示するには、次のようにする。
戻り値にはテーブル数が返る。
尚、複数のデータベースがある場合の動作は未検証である。
-- PL/pgSQL 作成 --
drop function tbllist();
create function tbllist() returns int as
'
declare
rec record;
rec2 record;
rcd int := 0;
begin
for rec in
select * from pg_tables where not tablename like ''pg%''
order by tableowner, tablename loop
raise debug ''tablename= %'', rec.tablename;
for rec2 in
select attname, atttypid, attlen from pg_attribute
where attnum > 0 and attrelid =
(select relfilenode from pg_class where relname = rec.tablename)
order by attnum loop
raise debug ''attname= % atttypid= % attlen= &'', rec2.attname, rec2.atttypid, rec2.attlen;
end loop;
rcd := rcd + 1;
end loop;
return rcd;
end;
'
language 'plpgsql'
;
-- PL/pgSQL 実行 --
select tbllist();
-- PL/pgSQL 結果 --
DEBUG: tablename= test2m
DEBUG: attname= key atttypid= 1042 attlen= &
DEBUG: attname= code1 atttypid= 1042 attlen= &
DEBUG: tablename= testm
DEBUG: attname= key atttypid= 1042 attlen= &
DEBUG: attname= data1 atttypid= 20 attlen= &
DEBUG: attname= data2 atttypid= 20 attlen= &
DEBUG: attname= data3 atttypid= 20 attlen= &
tbllist
---------
2
■テーブル構造一覧表示(動的 SQL での例)
・標準例と同じことを動的 SQL を使用して実現する。
実際には「標準」で出来ることに「動的 SQL」を使用する必要はない。
-- PL/pgSQL 作成 --
drop function tbllist();
create function tbllist() returns int as
'
declare
rec record;
rec2 record;
rcd int := 0;
sql varchar;
begin
for rec in
select * from pg_tables where not tablename like ''pg%''
order by tableowner, tablename loop
raise debug ''tablename= %'', rec.tablename;
sql := ''select attname, atttypid, attlen from pg_attribute where attnum > 0 and attrelid = (select relfilenode from pg_class where relname = '' || chr(39) || rec.tablename || chr(39) || '') order by attnum'';
for rec2 in execute sql loop
raise debug ''attname= % atttypid= % attlen= &'', rec2.attname, rec2.atttypid, rec2.attlen;
end loop;
rcd := rcd + 1;
end loop;
return rcd;
end;
'
language 'plpgsql'
;
-- PL/pgSQL 実行 --
select tbllist();
-- PL/pgSQL 結果 --
DEBUG: tablename= test2m
DEBUG: attname= key atttypid= 1042 attlen= &
DEBUG: attname= code1 atttypid= 1042 attlen= &
DEBUG: tablename= testm
DEBUG: attname= key atttypid= 1042 attlen= &
DEBUG: attname= data1 atttypid= 20 attlen= &
DEBUG: attname= data2 atttypid= 20 attlen= &
DEBUG: attname= data3 atttypid= 20 attlen= &
tbllist
---------
2
■テーブル構造一覧表示(LIKE 使用例)
・標準例と同じことを LIKE を使用して実現する。
実際にはテーブル構造一覧表示では厳密一致が必要なので LIKE の出番は
ない。構文例として見てください。
-- PL/pgSQL 作成 --
drop function tbllist();
create function tbllist() returns int as
'
declare
rec record;
rec2 record;
rcd int := 0;
begin
for rec in
select * from pg_tables where not tablename like ''pg%''
order by tableowner, tablename loop
raise debug ''tablename= %'', rec.tablename;
for rec2 in
select attname, atttypid, attlen from pg_attribute
where attnum > 0 and attrelid =
(select relfilenode from pg_class where relname like rec.tablename || ''%'')
order by attnum loop
raise debug ''attname= % atttypid= % attlen= &'', rec2.attname, rec2.atttypid, rec2.attlen;
end loop;
rcd := rcd + 1;
end loop;
return rcd;
end;
'
language 'plpgsql'
;
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
■SQL 基礎編資料
■SQL 基礎実地編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
Copyright (C) 1999-2018 Y.SAK