PostgreSQL的许多小技巧

stbyruby

1.使用空间索引进行快速间隔数据类型的搜索.
　　间隔搜索有时候很慢，大部分原因是索引优化器不使用索引，并且在开始列和结束列比较独立。一个解决方案是使用空间索引，它可以把两个独立的值当做一个值来使用。
　　postgres=# EXPLAIN ANALYZE SELECT * FROM testip WHERE 19999999 BETWEEN startip AND endip;
　　QUERY PLAN ---------------------------------------------------------------- Seq Scan on testip (cost=0.00..19902.00 rows=200814 width=12) (actual time=3.457..434.218 rows=1 loops=1) Filter: ((19999999 >= startip) AND (19999999 <= endip)) Total runtime: 434.299 ms (3 rows) Time: 435,865 ms
　　结论：根据以上的执行计划，可以知道上边的查询使用的是序列扫描，花费的时间是：435,865 ms
　　postgres=# CREATE INDEX ggg ON testip USING gist ((box(point(startip,startip),point(endip,endip))) box_ops);
　　使用如下的查询：
　　EXPLAIN ANALYZE SELECT * FROM testip WHERE box(point(startip,startip),point(endip,endip)) @> box(point (19999999,19999999), point(19999999,19999999));
　　结论：执行计划使用的是Bitmap Index Scan on ggg,花费的时间是：2,805 ms。可见相比以前的查询，使用空间索引的查询效率大大的提高了。
　　2.16进制到10进制的转换
　　我们已经有了系统函数将10进制转换成16进制：to_hex(11)　result: b 下边的函数实现将16进制的数转换成10进制。非常的简单：
 
[html] view plaincopy 

• create or replace function to_dec(text)  
  
• returns integer as $$  
  
• declare r int;  
  
• begin  
  
• 　　execute E'select x''||$1|| E''::integer' into r;  
  
• 　　return r;  
  
• end  
  
• $$ language plpgsql;  
  

　　--测试
 
 
[html] view plaincopy 

• select to_dec('ff');  
  

　　--结果
[html] view plaincopy 

• 255  
  

　　3.ALTER TABLE ALTER COLUMN USING 语法
 
　　在PostgreSQl里边，我们不能将varchar类型直接转换到bool，但是我们可以使用Using语法加判断后进行转换。
[html] view plaincopy 

• CREATE TABLE foo(a varchar);  
  
• INSERT INTO foo VALUES ('ano');  
  

　　--更改数据类型，会报错误信息
[html] view plaincopy 

• ALTER TABLE foo ALTER COLUMN a TYPE boolean;  
  
• ERROR:　column "a" cannot be cast to type "pg_catalog.bool"  
  

　　--使用Using语法更改数据类型
[html] view plaincopy 

• ALTER TABLE foo  
  
• ALTER COLUMN a TYPE boolean  
  
• 　　　USING CASE a  
  
• 　　　WHEN 'ano' THEN true  
  
• 　　　ELSE false END;  
  

　　--更改成功
[html] view plaincopy 

• SELECT * FROM foo;  
  

　　4.Quote_ident 的使用
 
　　使用双引号是一种防止SQL注入的方法，quote_ident 可以检查参数，如果参数中包含任何非法的字符，它会在参数两边加上""
　　非常简单和有效，但是问题是schema.name，因为中间有点分割。问题如下：
 
[html] view plaincopy 

• select quote_ident('public.foo');  
  

　　他不能在schema和name两边加上双引号。
 
　　我们可以通过使用函数来按点分割上边的对象名称，在每个单独的对象上使用quote_ident来完成我们的目的:
　　--对数组进行表转换，针对每一列来使用quote_ident
[html] view plaincopy 

• CREATE OR REPLACE FUNCTION quote_array(text[])  
  
• RETURNS text AS $$  
  
• SELECT array_to_string(array(SELECT quote_ident($1)  
  
• 　　　　　　　　　　　　　　　　FROM generate_series(1, array_upper($1,1)) g(i)),  
  
• 　　　　　　　　　　　 '.')  
  
• $$ LANGUAGE SQL IMMUTABLE;  
  

　　--创建函数按点进行拆分字符串
[html] view plaincopy 

• CREATE OR REPLACE FUNCTION quote_schema_ident(text)  
  
• RETURNS text AS $$  
  
• SELECT quote_array(string_to_array($1,'.'))  
  
• $$ LANGUAGE SQL IMMUTABLE;  
  

　　--测试
[html] view plaincopy 

• select quote_schema_ident('public.foo tab');  
  

　　5.我们已经习惯使用PostgreSQL的exception来捕捉错误，但是错误信息一直不知道如何取得，SQLERRM变量可以给我们详细的信息
 
　　以下是一个具体的示例：
[html] view plaincopy 

• CREATE OR REPLACE FUNCTION fn_log_error(p_function varchar, p_location int, p_error varchar)  
  
• RETURNS void AS $$  
  
• DECLARE  
  
• 　v_sql varchar;  
  
• 　v_return varchar;  
  
• 　v_error varchar;  
  
• BEGIN  
  

　　　--连接数据库
[html] view plaincopy 

• PERFORM dblink_connect('connection_name', 'dbname=...');  
  

　　　--拼凑插入的字符串
[html] view plaincopy 

• v_sql:= 'INSERT INTO error_log (function_name, location, error_message, error_time) '  
  
• 　　　　 || 'VALUES (''' || p_function_name || ''', '  
  
• 　　　　 || p_location || ', ''' || p_error || ''', clock_timestamp())';  
  

　　　--远程执行
[html] view plaincopy 

• SELECT INTO v_return *  
  
• 　 FROM dblink_exec('connection_name', v_sql, false);  
  

　　　--获取远程的错误信息
[html] view plaincopy 

• SELECT INTO v_error *  
  
• 　 FROM dblink_error_message('connection_name');  
  

　　　--如果出现错误则抛出异常
[html] view plaincopy 

• 　IF position('ERROR' in v_error) > 0 OR position('WARNING' in v_error) > 0 THEN  
  
• 　　RAISE EXCEPTION '%', v_error;  
  
• 　END IF;  
  
• 　PERFORM dblink_disconnect('connection_name');  
  
• EXCEPTION  
  
• 　WHEN others THEN  
  

　　　　--使用SQLERRM 来显示错误信息
[html] view plaincopy 

• 　　PERFORM dblink_disconnect('connection_name');  
  
• 　　RAISE EXCEPTION '(%)', SQLERRM;  
  
• END;  
  
• $$ LANGUAGE plpgsql SECURITY DEFINER;  
  

SQLERRM是一个非常有用的变量，可以详细记录错误的具体信息，帮助我们分析执行中发现的错误。
 
6.循环优化技巧
 
plpgsql对于非SQL操作效率不是特别高。Plpgsql 不喜欢字符或者字符数组的累计操作，当我们也不能用Perl，因此我们只能用SQL
　　--使用循环，结果会比较慢的函数
[html] view plaincopy 

• CREATE OR REPLACE FUNCTION SlowList(int) -- slow function, usable for N <= 100  
  
• RETURNS varchar AS $$  
  
• DECLARE s varchar = '';  
  
• BEGIN  
  
• 　FOR i IN 1..$1 LOOP  
  
• 　　s:= '<item>' || i || '</item>';　-- slow is s:= s || ..  
  
• 　END LOOP;  
  
• 　RETURN s;  
  
• END; $$ LANGUAGE plpgsql IMMUTABLE;  
  

　　--使用SQL，结果会比较快的函数
[html] view plaincopy 

• CREATE OR REPLACE FUNCTON FastList(int) -- fast function  
  
• RETURNS varchar AS $$  
  
• BEGIN  
  
• 　RETURN array_to_string(ARRAY(SELECT '<item>' || i || '</item>'  
  
• 　　　　　　　　　　　　　　　　　FROM generate_series(1, $1) g(i)),  
  
• 　　　　　　　　　　　　 '');  
  
• END; $$ LANGUAGE plpgsql IMMUTABLE;  
  

　　--结果:在循环100以下的时候差别并不是很大，当循环更多的时候，差距就非常明显，都来试试吧！
 
　　7.查询一组之中的头n条记录
　　我们一般的做法是使用子查询如下：
 
[html] view plaincopy 

• SELECT * FROM people WHERE id IN (  
  
• 　　 SELECT id FROM people s  
  
• 　　　 WHERE people.category = s.category  
  
• 　　　 ORDER BY age LIMIT 2)  
  
• ORDER BY category, age;  
  

　　使用连接我们也可以达到同样的效果如下：
 
 
[html] view plaincopy 

• SELECT s1.*  
  
• 　 FROM people s1  
  
• 　　　　LEFT JOIN  
  
• 　　　　people s2  
  
• 　　　　ON s1.category = s2.category AND s1.age < s2.age  
  
• 　GROUP BY s1.id, s1.category  
  
• 　HAVING COUNT(s2.id) <= 1  
  
• 　ORDER BY s1.category, COUNT(s2.id);  
  

 
　　说明:这个SQL语句的含义是找到同一类比自己的age大的记录，最后判断比自己大的记录的个数，如果是0，那么应该排名第一，
　　如果是1，那么排名第二(HAVING COUNT(s2.id) <= 1)