<转>postgresql

jane27

一、如何判断一个表是否已经存在

     Sybase中可以使用如下语句来判断
   
select count(*) from systable where table_type = 'BASE' and table_name = 'tablename';


     也可以再加上creator=1来限定在用户创建的表而非系统的表。

     在从Sybase移植到PostgreSQL过程中，不得不考虑的就是这个问题。最初不知道用什么办法，所以简单的用一句

   
select * from tablename;


     然后加上Exception处理来判断表是否存在。

     后来去google了一下，原来有更多更简单的办法。

     一种：

   
select count(*) from pg_class where relname = 'tablename';


     另一种：

   
select count(*) from information_schema.tables where table_schema='public' and table_type='BASE TABLE' and table_name='tablename';


     最后一种，适用于检查那些临时的，虽然存在但却不可见的表的存在与否：

   
select count(*) from pg_catalog.pg_class where relkind = 'r' and relname = 'tablename' and pg_catalog.pg_table_is_visible(oid);


二、如何返回result和result set

     例如有表test_table的定义如下：

   
create table test_table(col_idx integer,
                 col_a varchar,
                 col_b text);

     首先，返回result，我更愿意说是返回record。一条记录。有两种办法。

     一种就是利用out参数返回，好处是不需要使用record类型，返回的数据结构清楚，便于使用。缺点是如果返回的列数目比较大，参数列表变长，不美观，不易于编写，有可能超过参数个数上限。以及，目前，我没办法用这种方式返回结果集。


     create or replace function test(in idx integer,out col_a varchar, out col_b text) AS $$
     begin
       select col_a, col_b into col_a,col_b from test_table where col_idx=idx;
     end;
     $$ LANGUAGE 'plpgsql';


     使用的时候，是这样的：

   
select * from test(1);


     值得注意的是，在Sybase的存储过程中使用out参数，在调用存储过程的时候，是：(假如a和b分别是目标变量，用于存储out参数返回的值)

   
call test(1,a,b);


     而同样的在PostgreSQL中，却是

   
select * from test(1) into a,b;




     另一种方法就是使用record类型作为存储过程的返回值类型。例如：


     create or replace function test(in idx integer) returns record AS $$
     declare
       rec record;
     begin
      
       select col_a, col_b into record from test_table where col_idx=idx;
       return rec;
     end;
     $$ LANGUAGE 'plpgsql';


     那么使用这个存储过程的时候，大致如下：
   
select * from test(1) a(a varchar, b text);


     其中a(a varchar, b text)就是为了给返回的record一个明确的结构定义。

     返回结果集使用的方法和上述的第二种方法类似。


     create or replace function test(in idx integer) returns setof record AS $$
     declare
       rec record;
     begin
      
       for rec in select col_a, col_b from test_table where col_idx=idx loop
         return next rec;
       end loop;
     end;
     $$ LANGUAGE 'plpgsql';


     使用时候也是一样的。
   
select * from test(1) a(a varchar, b text);


     同样，如果结果集中只有一条记录呢？那么就同样实现了返回单条记录的功能。

三、如何实现Sybase的执行动态SQL查询并返回结果集

     Sybase中，使用如下的一行即可：
   
execute immediate with result set on 'SQL Command';


     而对应的PostgreSQL中要实现这个功能，可以这样：

       for rec in execute 'SQL Command' loop
         return next rec;
       end loop;


     过程返回值类型是setof record。
     不过让我不确定的是，似乎以前的PostgreSQL版本的excute并不返回数据。而我在8.1上测试，没有问题。