pl_python

最新推荐文章于 2020-12-10 23:07:35 发布

weixin_33712987

最新推荐文章于 2020-12-10 23:07:35 发布

阅读量267

点赞数

文章标签： python 数据库

原文链接：https://my.oschina.net/innovation/blog/786673

版权

2019独角兽企业重金招聘Python工程师标准>>>

函数缓存策略

IMMUTABLE
STABLE
VOLATILE

SECURITY CONTEXT

SECURITY DEFINER

控制结构

if foo =='blah':
 do_something()
for x in somearray:
statements
[x['somefield'] for x in rv]
[1.1*x/(2 + x) for x in range(5)]
if some boolean expression:
stuff happens
while b < n:
 stuff happens

返回值构造

return somevariable
return somearray_variable

Python 通用结构

import somepackage
#这是一个注释
a, b = b, a+b
alist =['Jim', 'Guru', 'x3456']
name, title, phone = alist
alist = [] #declares empty array
adict = {} #declare empty dictionary
adict = {'Jim': 'Guru', 'Jimmy' : 'Intern'}

常量和函数

True
False
coerce(somestring, ther)
dict(..)
globals()
float(num_string)
hasattr(object, name)
hash(obj)
int(num_string)
len(somearray_ordict)
long(num_string)
map(function, sequence[, sequence, ...])
pow(x, y [, z])
range([start,] end [, step])
xrange(start [, end [, step]]) (#use for big lists)
round(x,[numdigits])
slice([start,] stop[, step])
split(pattern,string, [maxsplit])
str(object)
zip(seq1[, seq2,...])
somestring.split

运算符

+ - *
/ // *
** # power operator
& ^ | == #boolean operators
&= ^= |= #compound bool
<< >> #shift operators
<<= >>= #compound shift operators

异常处理

try:
stuff happens
 return something
except (IOError, OSError):
 return "an error has happened"
try:
stuff happens
 return something
except IOError:
 return "an IOError"
except RuntimeError:
 return "runtime error"
except:
 return "have no clue what happened"

常见异常状态

Exception
 StandardError
 ArithmeticError
 FloatingPointError
 OverflowError
 ZeroDivisionError
 EnvironmentError
 IOError
 OSError
 EOFError
 ImportError
 RuntimeError
 SystemError

内置对象Objects

plpy
 execute(sql) #returns a python dictionary object
 prepare(sql) # returns a prepared plan object
TD["new"] # trigger new data
TD["old"] # trigger old data
TD["when"] # BEFORE, AFTER, UNKNOWN
SD

常用包和包函数

os -- chdir(path), chmod(path), listdir(path)
 mkdir(path,[mode]), rmdir(path), unlink(path),
 write(fp, str), path.exists()
math -- pi
sys -- argv, modules,path,stdin,stdout,
 stderr, version, exit(n)
time -- time(), clock(), strftime(format, timetuple), sleep(secs)

代价参数

COST cost metric
ROWS estimated number of rows

Official PostgreSQL 8.3 PL/Python Documentation Official Python documentation

只包含了我们认为最有用的结构部分的一个子集，只有单个cheatsheet页面

New in this release.

PLPYTHON 函数示例

CREATE OR REPLACE FUNCTION readfile (param_filepath text)
 RETURNS text
AS $$
 import os
 if not os.path.exists(param_filepath):
 return "file not found"
 return open(param_filepath).read()
$$ LANGUAGE plpythonu;

SELECT readfile('/test.htm');

--Doing postgresql queries --
-- PostgreSQL 查询示范
CREATE OR REPLACE FUNCTION getmydata (param_numitems integer)
 RETURNS SETOF mydata
AS $$
 #lets us grab the first param_numitems records
 rv = plpy.execute("SELECT pt_id, pt_observation FROM mydata",param_numitems);
 return rv
$$ LANGUAGE plpythonu;
SELECT *
 FROM getmydata(20) As d;

--Example Using custom classes and local classes --
-- 该示范同时使用用户定义类，以及本地类
CREATE OR REPLACE FUNCTION dofunkyplot (param_anum integer)
 RETURNS text
AS $$
 import aplotter
 import sys
 class WritableObject:
def __init__(self):
self.content = ''
def write(self, string):
self.content = self.content + string
 saveout = sys.stdout
 outbuffer = WritableObject()
 sys.stdout = outbuffer

#range (param_anum) w ill return array
 #consisting of 0 to param_num - 1 and formula
 # gets applied to each element
 # [1.1*x/(2 + x) for x in range(2)] -> [0 1.1/3]
 aplotter.plot([1.1*x/(2 + x) for x in range(param_anum)])
 sys.stdout = saveout
 return outbuffer.content
$$ LANGUAGE plpythonu;

SELECT dofunkyplot(n)
FROM generate_series(5,20, 10) As n;

转载于:https://my.oschina.net/innovation/blog/786673