PostgreSQL 8.2.3 婺桺桺懼
劯锔	媆锔	䆹40. PL/Python - Python 誺䘋臺蘔	媆誕	嬉誕

40.1. PL/Python 庘昄

PL/Python 喍䔇庘昄䫘锔婩䔇 CREATE FUNCTION 臺濘弄滯㔗懫套

CREATE FUNCTION funcname (argument-list)
  RETURNS return-type
AS $$
  # PL/Python function body
$$ LANGUAGE plpythonu;

庘昄嘷滇婔婻䞔剘䔇 Python 臔橸㔗嘷臖庘昄赆脄䫘䔇施唍嚹锐䔇埗昄儖潊婺 args[] 昄䂇䔇噄䘹㔗变劉埗昄幘嚔赆嚹锐彄臔橸婺幽赆嘷嘩捞锔埻麟㔗䂷悩儖嘪䫘 return 潡 yield 䙓婩誫啂㔗

冋套誫啂婴婻昘昄婺膄崓蔙䔇庘昄

CREATE FUNCTION pymax (a integer, b integer)
  RETURNS integer
AS $$
  if a > b:
    return a
  return b
$$ LANGUAGE plpythonu;

傖庘昄嘷嘵嚟䂍庺䔇 Python 蘸扵潊 Python 庘昄㔗懫套婪麵䔇蘸扵潊

def __plpython_procedure_pymax_23456():
  if a > b:
    return a
  return b

認麯啺螆 23456 滇 PostgreSQL 蕋庽認婻庘昄䔇 OID 㔗

PostgreSQL 庘昄埻麟埇傖锔誺噘匔䔇 args 彖臘诙埡㔗婘 pymax 冋床麯args[0] 寙劆丸婔婻埗昄䔇昄唚㔗args[1] 寙劆丸庯婻㔗鍴溴婘崡誻埇傖嘪䫘婪冋婺暫䴺䔇噾变劉䔇埗昄認䓉桹濘䔇埇臂攓敘喘㔗

套悩劏庘昄嚹锐庖婔婻 NULL 唚埗昄唚儖嚔滆䴺婺 None 㔗婪誄庘昄垔幬儖凹 NULL 膷噖库䫘髍臇䔇䂷悩㔗潏傸埇傖锔誺時媹 STRICT 準誕臯敘劽䊖䔇淉嘩套悩嚹噖 NULL 唚庘昄儖湹橸婉嚔赆脄䫘蔯滇䆋剿誫啂 NULL 㔗嘷䇽幘埇傖婘庘昄嘷婺演昖膷噖埗昄滇劥婺 NULL 

CREATE FUNCTION pymax (a integer, b integer)
  RETURNS integer
AS $$
  if (a is None) or (b is None):
    return None
  if a > b:
    return a
  return b
$$ LANGUAGE plpythonu;

套婪欔䴺襕傯 PL/Python 庘昄婺誫啂 NULL 埻襕誫啂 None 剿埇㔗方螺庘昄滇劥婖湚認湙啔鄘橬昽㔗

崉劽䌂傋埗昄儖嘩婺 Python 滹儇誕臯嚹锐㔗滹儇䔇噄䘹劉滇䂇劽䌂傋䔇噄䘹劉㔗套悩赆嚹锐䔇臯婺昊婻匂攓唚婺 NULL 垄婘滹儇婺䔇唚儖滇 None 㔗冋套

CREATE TABLE employee (
  name text,
  salary integer,
  age integer
);

CREATE FUNCTION overpaid (e employee)
  RETURNS boolean
AS $$
  if e["salary"] > 200000:
    return True
  if (e["age"] < 30) and (e["salary"] > 100000):
    return True
  return False
$$ LANGUAGE plpythonu;

橬崔䓉傯 Python 庘昄誫啂臯潡蔙崉劽䌂傋䔇桹濘㔗婋麵䔇冋床啺垔潏傸橬

CREATE TYPE named_value AS (
  name   text,
  value  integer
);

婔婻崉劽䌂傋䔇䂷悩埇傖嘩婺婋彖幋婔赆誫啂

废彖䌂傋(婔婻噄䂇潡彖臘嘖婉滇婔婻镖劽啹婺镖劽婉埇傖赆䘵嚘)

誫啂䔇废彖凹茇婺䔇釹昄媙釂婯䂷悩䔇崉劽䌂傋庖枕昄䕩劯㔗䘵嚘婺 0 䔇釹儖赆彖陉䂍䂇劽䌂傋䔇丸婔婻庖枕噽垄冺溴䌂毘懫套

CREATE FUNCTION make_pair (name text, value integer)
  RETURNS named_value
AS $$
  return [ name, value ]
  # 潡蔙嘩婺噄䂇誫啂( name, value )
$$ LANGUAGE plpythonu;

襕婺傂嘘庖枕誫啂 NULL 儌婘䕩庫䔇嘉䘞某噖 None 㔗

滹儇(庖噩)

懟婻䂷悩䌂傋庖枕䔇唚鄘麉桄傯滹儇婺嘪䫘庖枕劉嘩婺髞演䘵㔗冋套

CREATE FUNCTION make_pair (name text, value integer)
  RETURNS named_value
AS $$
  return { "name": name, "value": value }
$$ LANGUAGE plpythonu;

傂嘘鵺崡䔇庖噩髞/唚凹鄘儖赆媘䘖㔗婵崌䔇髞儖赆嘷嘩髍臇㔗襕婺傂嘘庖枕誫啂 NULL 儌傖䕩庫䔇庖枕劉嘩婺髞某噖 None 㔗

凹茇(傂嘘柊冕 __getattr__ 桹濘䔇凹茇)

垄䔇噖嘩桹嚟启滹儇婔湙㔗冋套

CREATE FUNCTION make_pair (name text, value integer)
  RETURNS named_value
AS $$
  class named_value:
    def __init__ (self, n, v):
      self.name = n
      self.value = v
  return named_value(name, value)

  # 潡蔙䞔剘婄
  class nv: pass
  nv.name = name
  nv.value = value
  return nv
$$ LANGUAGE plpythonu;

套悩澇橬柊冕誫啂唚Python 儖誫啂䚺䩕䔇 None 㔗PL/Python 儖檪 None 䪂臏潊 NULL 㔗

婔婻 PL/Python 庘昄誻埇傖誫啂湺麟潡䂇劽䌂傋䔇镖劽㔗橬崔䓉桹濘埇傖膆彄認婻䕞䔇啹婺誫啂䔇凹茇婘喙鄘赆蘸扵潊婔婻誺傼単㔗婋麵䔇冋床啺垔潏傸橬婔婻崉劽䌂傋

CREATE TYPE greeting AS (
  how text,
  who text
);

婔婻镖劽䂷悩埇傖傯婋彖幋婔誫啂

废彖䌂傋(噄䂇㔕彖臘㔕镖劽)

CREATE FUNCTION greet (how text)
  RETURNS SETOF greeting
AS $$
  # 誫啂寙劆嘩婺严劽䌂傋彖臘䔇噄䂇
  # 欔橬噽垄䂇劽幘鄘埇傖
  return ( [ how, "World" ], [ how, "PostgreSQL" ], [ how, "PL/Python" ] )
$$ LANGUAGE plpythonu;

誺傼単(傂嘘柊冕 __iter__ 启 next 桹濘䔇凹茇)

CREATE FUNCTION greet (how text)
  RETURNS SETOF greeting
AS $$
  class producer:
    def __init__ (self, how, who):
      self.how = how
      self.who = who
      self.ndx = -1

    def __iter__ (self):
      return self

    def next (self):
      self.ndx += 1
      if self.ndx == len(self.who):
        raise StopIteration
      return ( self.how, self.who[self.ndx] )

  return producer(how, [ "World", "PostgreSQL", "PL/Python" ])
$$ LANGUAGE plpythonu;

䫘潊単(yield)

CREATE FUNCTION greet (how text)
  RETURNS SETOF greeting
AS $$
  for who in [ "World", "PostgreSQL", "PL/Python" ]:
    yield ( how, who )
$$ LANGUAGE plpythonu;

躥只

嘷嬉䫌庯 Python bug #1483133 䔇寘啹婔底 Python 2.4 䔇脄臘䬽橸(嘪䫘 --with-pydebug 锬釹䚡臏)噾䘖嚔螷 PostgreSQL 橉媇単婘嘪䫘誺傼単誫啂镖劽施嘷溄㔗寘哋䔇 Fedora 4 傉䇽寙劆認婻躺荆嘖滇婘䫘库䬽橸䔇 Python 潡噾䂟媞臖䔇 Fedora 4 婪澇橬認婻閞鵻㔗

噘匔庖噩 SD 埇傖䫘庯婘庘昄脄䫘婺閘庻嗘昄扞㔗認婻埻麟滇䓕橬麍攕昄扞㔗噘匔庖噩 SD 滇噸噌昄扞埇傖婘婔婻劯䆇麯䔇欔橬 Python 庘昄幋閘嘪䫘㔗嘖婘嘪䫘施驔襕償媄㔗

懟婻庘昄鄘婘 Python 蓼麪単麯诙冖躻噌䔇埖鍊彽䔇欓臯凹茇啹溴準躻 myfunc 䔇噘匔昄扞冽庘昄埗昄滇 myfunc2 欔䩋婉彄䔇㔗認麯䔇冋崡滇婪麵柊彄䔇 SD 庖噩㔗

劯锔	饡釕	嬉誕
PL/Python - Python 誺䘋臺蘔	婪婔亓	蓥埏単庘昄