SAK }‘ŠÙ

SAK Streets ƒƒCƒ“ƒXƒgƒŠ[ƒg }‘ŠÙEƒJƒtƒF ˆê—— SAK Streets ŒŸõ
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | ƒtƒŠ[ƒ\ƒtƒg |

PostgreSQL •Ò24 - PL/pgSQLAƒe[ƒuƒ‹\‘¢ˆê——•\Ž¦A•W€A“®“ISQLALIKE

¡ƒe[ƒuƒ‹\‘¢ˆê——•\Ž¦(•W€—á)

  EŠeƒe[ƒuƒ‹‚̃e[ƒuƒ‹\‘¢ˆê——‚ð•\Ž¦‚·‚é‚ɂ́AŽŸ‚̂悤‚É‚·‚éB
    –ß‚è’l‚ɂ̓e[ƒuƒ‹”‚ª•Ô‚éB
    ®A•¡”‚̃f[ƒ^ƒx[ƒX‚ª‚ ‚éê‡‚Ì“®ì‚Í–¢ŒŸØ‚Å‚ ‚éB

-- 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 ŽÀs --
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

¡ƒe[ƒuƒ‹\‘¢ˆê——•\Ž¦(“®“I SQL ‚Å‚Ì—á)

  E•W€—á‚Æ“¯‚¶‚±‚Æ‚ð“®“I SQL ‚ðŽg—p‚µ‚ÄŽÀŒ»‚·‚éB
    ŽÀÛ‚ɂ́u•W€v‚ŏo—ˆ‚邱‚ƂɁu“®“I SQLv‚ðŽg—p‚·‚é•K—v‚Í‚È‚¢B

-- 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 ŽÀs --
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

¡ƒe[ƒuƒ‹\‘¢ˆê——•\Ž¦(LIKE Žg—p—á)

  E•W€—á‚Æ“¯‚¶‚±‚Æ‚ð LIKE ‚ðŽg—p‚µ‚ÄŽÀŒ»‚·‚éB
    ŽÀÛ‚ɂ̓e[ƒuƒ‹\‘¢ˆê——•\Ž¦‚Å‚ÍŒµ–§ˆê’v‚ª•K—v‚È‚Ì‚Å LIKE ‚̏o”Ô‚Í
    ‚È‚¢B\•¶—á‚Æ‚µ‚ÄŒ©‚Ä‚­‚¾‚³‚¢B

-- 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 •ÒAJAVA ServletAJSP •ÒŽ‘—¿
¡MySQL •ÒŽ‘—¿
¡SQL Šî‘b•ÒŽ‘—¿
¡SQL Šî‘bŽÀ’n•ÒŽ‘—¿
¡SQL ƒ`ƒ…[ƒjƒ“ƒO•ÒŽ‘—¿
¡Oracle PL/SQL •ÒŽ‘—¿
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | ƒtƒŠ[ƒ\ƒtƒg |

SAK Streets ƒƒCƒ“ƒXƒgƒŠ[ƒg }‘ŠÙEƒJƒtƒF ˆê—— SAK Streets ŒŸõ



Copyright (C) 1999-2017 Y.SAK