pg使用python编写存储过程_PL/Python PostgreSQL 存储过程语言(花架子)

总有一天，我要把这样的文章，删掉，统统删掉，I Hate PG!html

安装呢是在编译的过程当中选择python

./configure --with-python

[root@laptop]# ./configure --help |grep python

--with-python build Python modules (PL/Python)

[root@]#

具体能够使用以下命令论证

./configure --help |grep python

(Linux 环境中多使用　help, 真我的生都是升华的，

很少说了，技术的梗，离开技术但愿你也能活)

而后使用psql 吧，相似安装模块的语法，安装语言sql

postgres=# CREATE PROCEDURAL LANGUAGE plpythonu;

CREATE LANGUAGE

postgres=# \?

postgres=# \dL

List of languages

Name | Owner | Trusted | Description

-----------+----------+---------+------------------------------

plpgsql | postgres | t | PL/pgSQL procedural language

plpythonu | postgres | f |

(2 rows)

postgres=# \dL+

postgres=# CREATE OR REPLACE FUNCTION hello ()

postgres-# RETURNS TEXT

postgres-# AS $$

postgres$# import os

postgres$# return '你好，我当前目录为:'+ os.getcwd()

postgres$# $$ LANGUAGE plpythonu;

CREATE FUNCTION

postgres=# SELECT hello();

hello

------------------------------------

你好，我当前目录为:/dev/shm/hello

(1 row)

postgres=#

开篇一：　在greenplum(@pivotal) 中使用PL/Python

在下面的语句中加入了 try{} catch {} 是加入了异常处理post

CREATE TABLE test(id int, name varchar(20));

INSERT INTO test(id, name) VALUES(1, 'hire'),(2, 'me');

CREATE OR REPLACE FUNCTION query_test(a text) RETURNS text AS $$

try:

query = "SELECT name FROM test"

r = plpy.execute(query)

return r[0]

except Exception, ex:

plpy.notice(str(ex))

return str(ex)

$$

LANGUAGE plpythonu ;

SELECT query_test('nothing');

SELECT * FROM test;

定义查询计划 使用prepare

CREATE OR REPLACE FUNCTION query_test(a text) RETURNS text AS $$

try:

query = "SELECT id FROM test where name = $1 "

plan = plpy.prepare(query, ["text"])

r = plpy.execute(plan , [a])

return r[0]

except Exception, ex:

plpy.notice(str(ex))

return str(ex)

$$

LANGUAGE plpythonu ;

SELECT query_test('me');

参考资料:ui