On 04/02/11 17:19, Hitoshi Harada wrote:
> 2011/1/28 Jan Urbański <wulc...@wulczer.org>:
>> On 23/12/10 15:15, Jan Urbański wrote:
>>> Here's a patch implementing custom parsers for data types mentioned in
>>> http://archives.postgresql.org/pgsql-hackers/2010-12/msg01991.php. It's
>>> an incremental patch on top of the plpython-refactor patch sent eariler.
>>
>> Updated to master.
>
> I reviewed this for some time today.
Thank you.
> The patch applies with hunks, compiles and tests are passed, though it
> looks like not having additional test along with it.
I added a simple test. I had to add an expected file for the case when
hstore is compiled without PL/Python integration.
> - in hstore_plpython.c,
> PLyParsers parsers = {
> .in = hstore_to_dict,
> .out = dict_to_hstore
> };
> I'm not sure if this coding style is used anywhere in the core.
> Isn't this the C99 style?
Ooops, you're right. Fixed.
> - You need define custom variable class to use this feature.
> plpython.hstore = 'public.hstore'. I wonder why it's called
> plpython[u].hstore = 'public.hstore' (with 'u') because the language
> is called "plpythonu".
I think plpython.hstore was what showed up in discussion... I'd be fine
with calling the variable plpythonu.hstore, if that's the consensus.
> - typo in plpython.h,
> Types for parsres functions that ...
Fixed.
> - I tried the sample you mention upthread,
> regression=# select pick_one('a=>3, b=>4', 'b');
> ERROR: TypeError: string indices must be integers
> CONTEXT: PL/Python function "pick_one"
>
> My python is 2.4.3 again.
Hm, this means that the hstore has not been transformed into a Python
dict, but into a string, which is what happens if you *don't* have
plpython hstore integration enabled. I think that was because of an
issue with my changes to hstore's Makefile, that made it compile without
Python support, even if the sources were configured with --with-python.
There's also a gotcha: if you set plpython.hstore to 'public.hstore',
you will have to DROP (or CREATE OR REPLACE again) all functions that
accept or return hstores, because their I/O routines are already cached.
Not sure how big of a problem that is (or how to fix it in an elegant
manner). Making the parameter PGC_POSTMASTER is an easy solution... but
not very nice.
> That's it for now. It is an exciting feature and plpython will be the
> first language to think of when you're building "object database" if
> this feature is in. The design here will affect following pl/perl and
> other so it is important enough to discuss.
Yes, I ended up writing this patch as a PoC of how you can integrate
procedural languages with arbitrary addon modules, so it would be good
to have a discussion about the general mechanisms. I'm aware that this
discussion, and subsequently this patch, might be punted to 9.2
(although that would be a shame).
Cheers,
Jan
diff --git a/contrib/hstore/Makefile b/contrib/hstore/Makefile
index 1d533fd..fd85052 100644
*** a/contrib/hstore/Makefile
--- b/contrib/hstore/Makefile
***************
*** 1,8 ****
# contrib/hstore/Makefile
MODULE_big = hstore
OBJS = hstore_io.o hstore_op.o hstore_gist.o hstore_gin.o hstore_compat.o \
! crc32.o
DATA_built = hstore.sql
DATA = uninstall_hstore.sql
--- 1,9 ----
# contrib/hstore/Makefile
MODULE_big = hstore
+
OBJS = hstore_io.o hstore_op.o hstore_gist.o hstore_gin.o hstore_compat.o \
! hstore_plpython.o crc32.o
DATA_built = hstore.sql
DATA = uninstall_hstore.sql
*************** top_builddir = ../..
*** 18,20 ****
--- 19,28 ----
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif
+
+ ifeq ($(with_python), yes)
+ override CFLAGS += -I$(srcdir) -I$(top_builddir)/src/pl/plpython \
+ $(python_includespec) -DHSTORE_PLPYTHON_SUPPORT
+ SHLIB_LINK = $(python_libspec) $(python_additional_libs) \
+ $(filter -lintl,$(LIBS)) $(CPPFLAGS)
+ endif
diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out
index 354fff2..049fdd5 100644
*** a/contrib/hstore/expected/hstore.out
--- b/contrib/hstore/expected/hstore.out
*************** select count(*) from testhstore where h
*** 1461,1463 ****
--- 1461,1502 ----
1
(1 row)
+ -- plpython integration
+ set plpython.hstore = 'public.hstore';
+ create language plpythonu;
+ create or replace function select_one(h hstore, idx text) returns text as $$
+ try:
+ return h.get(idx)
+ except AttributeError:
+ # h has not been transformed into a dict and is a string
+ return None
+ $$ language plpythonu;
+ select coalesce(select_one(h, 'node'), '<null>') as node from testhstore where select_one(h, 'line')::integer < 10;
+ node
+ --------
+ AA
+ CBB
+ <null>
+ <null>
+ CBA
+ CBC
+ <null>
+ <null>
+ <null>
+ (9 rows)
+
+ reset plpython.hstore;
+ create or replace function select_one(h hstore, idx text) returns text as $$
+ try:
+ return h.get(idx)
+ except AttributeError:
+ # h has not been transformed into a dict and is a string
+ return None
+ $$ language plpythonu;
+ select coalesce(select_one(h, 'node'), '<null>') as node from testhstore where select_one(h, 'line')::integer < 10;
+ node
+ ------
+ (0 rows)
+
+ drop function select_one(hstore, text);
+ drop language plpythonu;
diff --git a/contrib/hstore/expected/hstore_0.out b/contrib/hstore/expected/hstore_0.out
index ...af06337 .
*** a/contrib/hstore/expected/hstore_0.out
--- b/contrib/hstore/expected/hstore_0.out
***************
*** 0 ****
--- 1,1495 ----
+ --
+ -- first, define the datatype. Turn off echoing so that expected file
+ -- does not depend on contents of hstore.sql.
+ --
+ SET client_min_messages = warning;
+ \set ECHO none
+ psql:hstore.sql:228: WARNING: => is deprecated as an operator name
+ DETAIL: This name may be disallowed altogether in future versions of PostgreSQL.
+ RESET client_min_messages;
+ set escape_string_warning=off;
+ --hstore;
+ select ''::hstore;
+ hstore
+ --------
+
+ (1 row)
+
+ select 'a=>b'::hstore;
+ hstore
+ ----------
+ "a"=>"b"
+ (1 row)
+
+ select ' a=>b'::hstore;
+ hstore
+ ----------
+ "a"=>"b"
+ (1 row)
+
+ select 'a =>b'::hstore;
+ hstore
+ ----------
+ "a"=>"b"
+ (1 row)
+
+ select 'a=>b '::hstore;
+ hstore
+ ----------
+ "a"=>"b"
+ (1 row)
+
+ select 'a=> b'::hstore;
+ hstore
+ ----------
+ "a"=>"b"
+ (1 row)
+
+ select '"a"=>"b"'::hstore;
+ hstore
+ ----------
+ "a"=>"b"
+ (1 row)
+
+ select ' "a"=>"b"'::hstore;
+ hstore
+ ----------
+ "a"=>"b"
+ (1 row)
+
+ select '"a" =>"b"'::hstore;
+ hstore
+ ----------
+ "a"=>"b"
+ (1 row)
+
+ select '"a"=>"b" '::hstore;
+ hstore
+ ----------
+ "a"=>"b"
+ (1 row)
+
+ select '"a"=> "b"'::hstore;
+ hstore
+ ----------
+ "a"=>"b"
+ (1 row)
+
+ select 'aa=>bb'::hstore;
+ hstore
+ ------------
+ "aa"=>"bb"
+ (1 row)
+
+ select ' aa=>bb'::hstore;
+ hstore
+ ------------
+ "aa"=>"bb"
+ (1 row)
+
+ select 'aa =>bb'::hstore;
+ hstore
+ ------------
+ "aa"=>"bb"
+ (1 row)
+
+ select 'aa=>bb '::hstore;
+ hstore
+ ------------
+ "aa"=>"bb"
+ (1 row)
+
+ select 'aa=> bb'::hstore;
+ hstore
+ ------------
+ "aa"=>"bb"
+ (1 row)
+
+ select '"aa"=>"bb"'::hstore;
+ hstore
+ ------------
+ "aa"=>"bb"
+ (1 row)
+
+ select ' "aa"=>"bb"'::hstore;
+ hstore
+ ------------
+ "aa"=>"bb"
+ (1 row)
+
+ select '"aa" =>"bb"'::hstore;
+ hstore
+ ------------
+ "aa"=>"bb"
+ (1 row)
+
+ select '"aa"=>"bb" '::hstore;
+ hstore
+ ------------
+ "aa"=>"bb"
+ (1 row)
+
+ select '"aa"=> "bb"'::hstore;
+ hstore
+ ------------
+ "aa"=>"bb"
+ (1 row)
+
+ select 'aa=>bb, cc=>dd'::hstore;
+ hstore
+ ------------------------
+ "aa"=>"bb", "cc"=>"dd"
+ (1 row)
+
+ select 'aa=>bb , cc=>dd'::hstore;
+ hstore
+ ------------------------
+ "aa"=>"bb", "cc"=>"dd"
+ (1 row)
+
+ select 'aa=>bb ,cc=>dd'::hstore;
+ hstore
+ ------------------------
+ "aa"=>"bb", "cc"=>"dd"
+ (1 row)
+
+ select 'aa=>bb, "cc"=>dd'::hstore;
+ hstore
+ ------------------------
+ "aa"=>"bb", "cc"=>"dd"
+ (1 row)
+
+ select 'aa=>bb , "cc"=>dd'::hstore;
+ hstore
+ ------------------------
+ "aa"=>"bb", "cc"=>"dd"
+ (1 row)
+
+ select 'aa=>bb ,"cc"=>dd'::hstore;
+ hstore
+ ------------------------
+ "aa"=>"bb", "cc"=>"dd"
+ (1 row)
+
+ select 'aa=>"bb", cc=>dd'::hstore;
+ hstore
+ ------------------------
+ "aa"=>"bb", "cc"=>"dd"
+ (1 row)
+
+ select 'aa=>"bb" , cc=>dd'::hstore;
+ hstore
+ ------------------------
+ "aa"=>"bb", "cc"=>"dd"
+ (1 row)
+
+ select 'aa=>"bb" ,cc=>dd'::hstore;
+ hstore
+ ------------------------
+ "aa"=>"bb", "cc"=>"dd"
+ (1 row)
+
+ select 'aa=>null'::hstore;
+ hstore
+ ------------
+ "aa"=>NULL
+ (1 row)
+
+ select 'aa=>NuLl'::hstore;
+ hstore
+ ------------
+ "aa"=>NULL
+ (1 row)
+
+ select 'aa=>"NuLl"'::hstore;
+ hstore
+ --------------
+ "aa"=>"NuLl"
+ (1 row)
+
+ select e'\\=a=>q=w'::hstore;
+ hstore
+ -------------
+ "=a"=>"q=w"
+ (1 row)
+
+ select e'"=a"=>q\\=w'::hstore;
+ hstore
+ -------------
+ "=a"=>"q=w"
+ (1 row)
+
+ select e'"\\"a"=>q>w'::hstore;
+ hstore
+ --------------
+ "\"a"=>"q>w"
+ (1 row)
+
+ select e'\\"a=>q"w'::hstore;
+ hstore
+ ---------------
+ "\"a"=>"q\"w"
+ (1 row)
+
+ select ''::hstore;
+ hstore
+ --------
+
+ (1 row)
+
+ select ' '::hstore;
+ hstore
+ --------
+
+ (1 row)
+
+ -- -> operator
+ select 'aa=>b, c=>d , b=>16'::hstore->'c';
+ ?column?
+ ----------
+ d
+ (1 row)
+
+ select 'aa=>b, c=>d , b=>16'::hstore->'b';
+ ?column?
+ ----------
+ 16
+ (1 row)
+
+ select 'aa=>b, c=>d , b=>16'::hstore->'aa';
+ ?column?
+ ----------
+ b
+ (1 row)
+
+ select ('aa=>b, c=>d , b=>16'::hstore->'gg') is null;
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select ('aa=>NULL, c=>d , b=>16'::hstore->'aa') is null;
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select ('aa=>"NULL", c=>d , b=>16'::hstore->'aa') is null;
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ -- -> array operator
+ select 'aa=>"NULL", c=>d , b=>16'::hstore -> ARRAY['aa','c'];
+ ?column?
+ ------------
+ {"NULL",d}
+ (1 row)
+
+ select 'aa=>"NULL", c=>d , b=>16'::hstore -> ARRAY['c','aa'];
+ ?column?
+ ------------
+ {d,"NULL"}
+ (1 row)
+
+ select 'aa=>NULL, c=>d , b=>16'::hstore -> ARRAY['aa','c',null];
+ ?column?
+ ---------------
+ {NULL,d,NULL}
+ (1 row)
+
+ select 'aa=>1, c=>3, b=>2, d=>4'::hstore -> ARRAY[['b','d'],['aa','c']];
+ ?column?
+ ---------------
+ {{2,4},{1,3}}
+ (1 row)
+
+ -- exists/defined
+ select exist('a=>NULL, b=>qq', 'a');
+ exist
+ -------
+ t
+ (1 row)
+
+ select exist('a=>NULL, b=>qq', 'b');
+ exist
+ -------
+ t
+ (1 row)
+
+ select exist('a=>NULL, b=>qq', 'c');
+ exist
+ -------
+ f
+ (1 row)
+
+ select exist('a=>"NULL", b=>qq', 'a');
+ exist
+ -------
+ t
+ (1 row)
+
+ select defined('a=>NULL, b=>qq', 'a');
+ defined
+ ---------
+ f
+ (1 row)
+
+ select defined('a=>NULL, b=>qq', 'b');
+ defined
+ ---------
+ t
+ (1 row)
+
+ select defined('a=>NULL, b=>qq', 'c');
+ defined
+ ---------
+ f
+ (1 row)
+
+ select defined('a=>"NULL", b=>qq', 'a');
+ defined
+ ---------
+ t
+ (1 row)
+
+ select hstore 'a=>NULL, b=>qq' ? 'a';
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select hstore 'a=>NULL, b=>qq' ? 'b';
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select hstore 'a=>NULL, b=>qq' ? 'c';
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ select hstore 'a=>"NULL", b=>qq' ? 'a';
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select hstore 'a=>NULL, b=>qq' ?| ARRAY['a','b'];
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select hstore 'a=>NULL, b=>qq' ?| ARRAY['b','a'];
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','a'];
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','d'];
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ select hstore 'a=>NULL, b=>qq' ?| '{}'::text[];
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ select hstore 'a=>NULL, b=>qq' ?& ARRAY['a','b'];
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select hstore 'a=>NULL, b=>qq' ?& ARRAY['b','a'];
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','a'];
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','d'];
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ select hstore 'a=>NULL, b=>qq' ?& '{}'::text[];
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ -- delete
+ select delete('a=>1 , b=>2, c=>3'::hstore, 'a');
+ delete
+ --------------------
+ "b"=>"2", "c"=>"3"
+ (1 row)
+
+ select delete('a=>null , b=>2, c=>3'::hstore, 'a');
+ delete
+ --------------------
+ "b"=>"2", "c"=>"3"
+ (1 row)
+
+ select delete('a=>1 , b=>2, c=>3'::hstore, 'b');
+ delete
+ --------------------
+ "a"=>"1", "c"=>"3"
+ (1 row)
+
+ select delete('a=>1 , b=>2, c=>3'::hstore, 'c');
+ delete
+ --------------------
+ "a"=>"1", "b"=>"2"
+ (1 row)
+
+ select delete('a=>1 , b=>2, c=>3'::hstore, 'd');
+ delete
+ ------------------------------
+ "a"=>"1", "b"=>"2", "c"=>"3"
+ (1 row)
+
+ select 'a=>1 , b=>2, c=>3'::hstore - 'a'::text;
+ ?column?
+ --------------------
+ "b"=>"2", "c"=>"3"
+ (1 row)
+
+ select 'a=>null , b=>2, c=>3'::hstore - 'a'::text;
+ ?column?
+ --------------------
+ "b"=>"2", "c"=>"3"
+ (1 row)
+
+ select 'a=>1 , b=>2, c=>3'::hstore - 'b'::text;
+ ?column?
+ --------------------
+ "a"=>"1", "c"=>"3"
+ (1 row)
+
+ select 'a=>1 , b=>2, c=>3'::hstore - 'c'::text;
+ ?column?
+ --------------------
+ "a"=>"1", "b"=>"2"
+ (1 row)
+
+ select 'a=>1 , b=>2, c=>3'::hstore - 'd'::text;
+ ?column?
+ ------------------------------
+ "a"=>"1", "b"=>"2", "c"=>"3"
+ (1 row)
+
+ select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b'::text)
+ = pg_column_size('a=>1, b=>2'::hstore);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ -- delete (array)
+ select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','e']);
+ delete
+ ------------------------------
+ "a"=>"1", "b"=>"2", "c"=>"3"
+ (1 row)
+
+ select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','b']);
+ delete
+ --------------------
+ "a"=>"1", "c"=>"3"
+ (1 row)
+
+ select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['a','c']);
+ delete
+ ----------
+ "b"=>"2"
+ (1 row)
+
+ select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY[['b'],['c'],['a']]);
+ delete
+ --------
+
+ (1 row)
+
+ select delete('a=>1 , b=>2, c=>3'::hstore, '{}'::text[]);
+ delete
+ ------------------------------
+ "a"=>"1", "b"=>"2", "c"=>"3"
+ (1 row)
+
+ select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','e'];
+ ?column?
+ ------------------------------
+ "a"=>"1", "b"=>"2", "c"=>"3"
+ (1 row)
+
+ select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','b'];
+ ?column?
+ --------------------
+ "a"=>"1", "c"=>"3"
+ (1 row)
+
+ select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c'];
+ ?column?
+ ----------
+ "b"=>"2"
+ (1 row)
+
+ select 'a=>1 , b=>2, c=>3'::hstore - ARRAY[['b'],['c'],['a']];
+ ?column?
+ ----------
+
+ (1 row)
+
+ select 'a=>1 , b=>2, c=>3'::hstore - '{}'::text[];
+ ?column?
+ ------------------------------
+ "a"=>"1", "b"=>"2", "c"=>"3"
+ (1 row)
+
+ select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c'])
+ = pg_column_size('b=>2'::hstore);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select pg_column_size('a=>1 , b=>2, c=>3'::hstore - '{}'::text[])
+ = pg_column_size('a=>1, b=>2, c=>3'::hstore);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ -- delete (hstore)
+ select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>4, b=>2'::hstore);
+ delete
+ ---------------------
+ "c"=>"3", "aa"=>"1"
+ (1 row)
+
+ select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>NULL, c=>3'::hstore);
+ delete
+ ---------------------
+ "b"=>"2", "aa"=>"1"
+ (1 row)
+
+ select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>1, b=>2, c=>3'::hstore);
+ delete
+ --------
+
+ (1 row)
+
+ select delete('aa=>1 , b=>2, c=>3'::hstore, 'b=>2'::hstore);
+ delete
+ ---------------------
+ "c"=>"3", "aa"=>"1"
+ (1 row)
+
+ select delete('aa=>1 , b=>2, c=>3'::hstore, ''::hstore);
+ delete
+ -------------------------------
+ "b"=>"2", "c"=>"3", "aa"=>"1"
+ (1 row)
+
+ select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>4, b=>2'::hstore;
+ ?column?
+ ---------------------
+ "c"=>"3", "aa"=>"1"
+ (1 row)
+
+ select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>NULL, c=>3'::hstore;
+ ?column?
+ ---------------------
+ "b"=>"2", "aa"=>"1"
+ (1 row)
+
+ select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>1, b=>2, c=>3'::hstore;
+ ?column?
+ ----------
+
+ (1 row)
+
+ select 'aa=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore;
+ ?column?
+ ---------------------
+ "c"=>"3", "aa"=>"1"
+ (1 row)
+
+ select 'aa=>1 , b=>2, c=>3'::hstore - ''::hstore;
+ ?column?
+ -------------------------------
+ "b"=>"2", "c"=>"3", "aa"=>"1"
+ (1 row)
+
+ select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore)
+ = pg_column_size('a=>1, c=>3'::hstore);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ''::hstore)
+ = pg_column_size('a=>1, b=>2, c=>3'::hstore);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ -- ||
+ select 'aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f';
+ ?column?
+ -------------------------------------------
+ "b"=>"g", "aa"=>"1", "cq"=>"l", "fg"=>"f"
+ (1 row)
+
+ select 'aa=>1 , b=>2, cq=>3'::hstore || 'aq=>l';
+ ?column?
+ -------------------------------------------
+ "b"=>"2", "aa"=>"1", "aq"=>"l", "cq"=>"3"
+ (1 row)
+
+ select 'aa=>1 , b=>2, cq=>3'::hstore || 'aa=>l';
+ ?column?
+ --------------------------------
+ "b"=>"2", "aa"=>"l", "cq"=>"3"
+ (1 row)
+
+ select 'aa=>1 , b=>2, cq=>3'::hstore || '';
+ ?column?
+ --------------------------------
+ "b"=>"2", "aa"=>"1", "cq"=>"3"
+ (1 row)
+
+ select ''::hstore || 'cq=>l, b=>g, fg=>f';
+ ?column?
+ --------------------------------
+ "b"=>"g", "cq"=>"l", "fg"=>"f"
+ (1 row)
+
+ select pg_column_size(''::hstore || ''::hstore) = pg_column_size(''::hstore);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select pg_column_size('aa=>1'::hstore || 'b=>2'::hstore)
+ = pg_column_size('aa=>1, b=>2'::hstore);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select pg_column_size('aa=>1, b=>2'::hstore || ''::hstore)
+ = pg_column_size('aa=>1, b=>2'::hstore);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select pg_column_size(''::hstore || 'aa=>1, b=>2'::hstore)
+ = pg_column_size('aa=>1, b=>2'::hstore);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ -- =>
+ select 'a=>g, b=>c'::hstore || ( 'asd'=>'gf' );
+ ?column?
+ ---------------------------------
+ "a"=>"g", "b"=>"c", "asd"=>"gf"
+ (1 row)
+
+ select 'a=>g, b=>c'::hstore || ( 'b'=>'gf' );
+ ?column?
+ ---------------------
+ "a"=>"g", "b"=>"gf"
+ (1 row)
+
+ select 'a=>g, b=>c'::hstore || ( 'b'=>'NULL' );
+ ?column?
+ -----------------------
+ "a"=>"g", "b"=>"NULL"
+ (1 row)
+
+ select 'a=>g, b=>c'::hstore || ( 'b'=>NULL );
+ ?column?
+ ---------------------
+ "a"=>"g", "b"=>NULL
+ (1 row)
+
+ select ('a=>g, b=>c'::hstore || ( NULL=>'b' )) is null;
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select pg_column_size(('b'=>'gf'))
+ = pg_column_size('b=>gf'::hstore);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select pg_column_size('a=>g, b=>c'::hstore || ('b'=>'gf'))
+ = pg_column_size('a=>g, b=>gf'::hstore);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ -- slice()
+ select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['g','h','i']);
+ slice
+ -------
+
+ (1 row)
+
+ select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b']);
+ slice
+ --------------------
+ "b"=>"2", "c"=>"3"
+ (1 row)
+
+ select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['aa','b']);
+ slice
+ ---------------------
+ "b"=>"2", "aa"=>"1"
+ (1 row)
+
+ select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b','aa']);
+ slice
+ -------------------------------
+ "b"=>"2", "c"=>"3", "aa"=>"1"
+ (1 row)
+
+ select pg_column_size(slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b']))
+ = pg_column_size('b=>2, c=>3'::hstore);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select pg_column_size(slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b','aa']))
+ = pg_column_size('aa=>1, b=>2, c=>3'::hstore);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ -- array input
+ select '{}'::text[]::hstore;
+ hstore
+ --------
+
+ (1 row)
+
+ select ARRAY['a','g','b','h','asd']::hstore;
+ ERROR: array must have even number of elements
+ select ARRAY['a','g','b','h','asd','i']::hstore;
+ array
+ --------------------------------
+ "a"=>"g", "b"=>"h", "asd"=>"i"
+ (1 row)
+
+ select ARRAY[['a','g'],['b','h'],['asd','i']]::hstore;
+ array
+ --------------------------------
+ "a"=>"g", "b"=>"h", "asd"=>"i"
+ (1 row)
+
+ select ARRAY[['a','g','b'],['h','asd','i']]::hstore;
+ ERROR: array must have two columns
+ select ARRAY[[['a','g'],['b','h'],['asd','i']]]::hstore;
+ ERROR: wrong number of array subscripts
+ select hstore('{}'::text[]);
+ hstore
+ --------
+
+ (1 row)
+
+ select hstore(ARRAY['a','g','b','h','asd']);
+ ERROR: array must have even number of elements
+ select hstore(ARRAY['a','g','b','h','asd','i']);
+ hstore
+ --------------------------------
+ "a"=>"g", "b"=>"h", "asd"=>"i"
+ (1 row)
+
+ select hstore(ARRAY[['a','g'],['b','h'],['asd','i']]);
+ hstore
+ --------------------------------
+ "a"=>"g", "b"=>"h", "asd"=>"i"
+ (1 row)
+
+ select hstore(ARRAY[['a','g','b'],['h','asd','i']]);
+ ERROR: array must have two columns
+ select hstore(ARRAY[[['a','g'],['b','h'],['asd','i']]]);
+ ERROR: wrong number of array subscripts
+ select hstore('[0:5]={a,g,b,h,asd,i}'::text[]);
+ hstore
+ --------------------------------
+ "a"=>"g", "b"=>"h", "asd"=>"i"
+ (1 row)
+
+ select hstore('[0:2][1:2]={{a,g},{b,h},{asd,i}}'::text[]);
+ hstore
+ --------------------------------
+ "a"=>"g", "b"=>"h", "asd"=>"i"
+ (1 row)
+
+ -- pairs of arrays
+ select hstore(ARRAY['a','b','asd'], ARRAY['g','h','i']);
+ hstore
+ --------------------------------
+ "a"=>"g", "b"=>"h", "asd"=>"i"
+ (1 row)
+
+ select hstore(ARRAY['a','b','asd'], ARRAY['g','h',NULL]);
+ hstore
+ ---------------------------------
+ "a"=>"g", "b"=>"h", "asd"=>NULL
+ (1 row)
+
+ select hstore(ARRAY['z','y','x'], ARRAY['1','2','3']);
+ hstore
+ ------------------------------
+ "x"=>"3", "y"=>"2", "z"=>"1"
+ (1 row)
+
+ select hstore(ARRAY['aaa','bb','c','d'], ARRAY[null::text,null,null,null]);
+ hstore
+ -----------------------------------------------
+ "c"=>NULL, "d"=>NULL, "bb"=>NULL, "aaa"=>NULL
+ (1 row)
+
+ select hstore(ARRAY['aaa','bb','c','d'], null);
+ hstore
+ -----------------------------------------------
+ "c"=>NULL, "d"=>NULL, "bb"=>NULL, "aaa"=>NULL
+ (1 row)
+
+ select quote_literal(hstore('{}'::text[], '{}'::text[]));
+ quote_literal
+ ---------------
+ ''
+ (1 row)
+
+ select quote_literal(hstore('{}'::text[], null));
+ quote_literal
+ ---------------
+ ''
+ (1 row)
+
+ select hstore(ARRAY['a'], '{}'::text[]); -- error
+ ERROR: arrays must have same bounds
+ select hstore('{}'::text[], ARRAY['a']); -- error
+ ERROR: arrays must have same bounds
+ select pg_column_size(hstore(ARRAY['a','b','asd'], ARRAY['g','h','i']))
+ = pg_column_size('a=>g, b=>h, asd=>i'::hstore);
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ -- records
+ select hstore(v) from (values (1, 'foo', 1.2, 3::float8)) v(a,b,c,d);
+ hstore
+ ------------------------------------------------
+ "f1"=>"1", "f2"=>"foo", "f3"=>"1.2", "f4"=>"3"
+ (1 row)
+
+ create domain hstestdom1 as integer not null default 0;
+ create table testhstore0 (a integer, b text, c numeric, d float8);
+ create table testhstore1 (a integer, b text, c numeric, d float8, e hstestdom1);
+ insert into testhstore0 values (1, 'foo', 1.2, 3::float8);
+ insert into testhstore1 values (1, 'foo', 1.2, 3::float8);
+ select hstore(v) from testhstore1 v;
+ hstore
+ ------------------------------------------------------
+ "a"=>"1", "b"=>"foo", "c"=>"1.2", "d"=>"3", "e"=>"0"
+ (1 row)
+
+ select hstore(null::testhstore0);
+ hstore
+ --------------------------------------------
+ "a"=>NULL, "b"=>NULL, "c"=>NULL, "d"=>NULL
+ (1 row)
+
+ select hstore(null::testhstore1);
+ hstore
+ -------------------------------------------------------
+ "a"=>NULL, "b"=>NULL, "c"=>NULL, "d"=>NULL, "e"=>NULL
+ (1 row)
+
+ select pg_column_size(hstore(v))
+ = pg_column_size('a=>1, b=>"foo", c=>"1.2", d=>"3", e=>"0"'::hstore)
+ from testhstore1 v;
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select populate_record(v, ('c' => '3.45')) from testhstore1 v;
+ populate_record
+ ------------------
+ (1,foo,3.45,3,0)
+ (1 row)
+
+ select populate_record(v, ('d' => '3.45')) from testhstore1 v;
+ populate_record
+ --------------------
+ (1,foo,1.2,3.45,0)
+ (1 row)
+
+ select populate_record(v, ('e' => '123')) from testhstore1 v;
+ populate_record
+ -------------------
+ (1,foo,1.2,3,123)
+ (1 row)
+
+ select populate_record(v, ('e' => null)) from testhstore1 v;
+ ERROR: domain hstestdom1 does not allow null values
+ select populate_record(v, ('c' => null)) from testhstore1 v;
+ populate_record
+ -----------------
+ (1,foo,,3,0)
+ (1 row)
+
+ select populate_record(v, ('b' => 'foo') || ('a' => '123')) from testhstore1 v;
+ populate_record
+ -------------------
+ (123,foo,1.2,3,0)
+ (1 row)
+
+ select populate_record(v, ('b' => 'foo') || ('e' => null)) from testhstore0 v;
+ populate_record
+ -----------------
+ (1,foo,1.2,3)
+ (1 row)
+
+ select populate_record(v, ('b' => 'foo') || ('e' => null)) from testhstore1 v;
+ ERROR: domain hstestdom1 does not allow null values
+ select populate_record(v, '') from testhstore0 v;
+ populate_record
+ -----------------
+ (1,foo,1.2,3)
+ (1 row)
+
+ select populate_record(v, '') from testhstore1 v;
+ populate_record
+ -----------------
+ (1,foo,1.2,3,0)
+ (1 row)
+
+ select populate_record(null::testhstore1, ('c' => '3.45') || ('a' => '123'));
+ ERROR: domain hstestdom1 does not allow null values
+ select populate_record(null::testhstore1, ('c' => '3.45') || ('e' => '123'));
+ populate_record
+ -----------------
+ (,,3.45,,123)
+ (1 row)
+
+ select populate_record(null::testhstore0, '');
+ populate_record
+ -----------------
+ (,,,)
+ (1 row)
+
+ select populate_record(null::testhstore1, '');
+ ERROR: domain hstestdom1 does not allow null values
+ select v #= ('c' => '3.45') from testhstore1 v;
+ ?column?
+ ------------------
+ (1,foo,3.45,3,0)
+ (1 row)
+
+ select v #= ('d' => '3.45') from testhstore1 v;
+ ?column?
+ --------------------
+ (1,foo,1.2,3.45,0)
+ (1 row)
+
+ select v #= ('e' => '123') from testhstore1 v;
+ ?column?
+ -------------------
+ (1,foo,1.2,3,123)
+ (1 row)
+
+ select v #= ('c' => null) from testhstore1 v;
+ ?column?
+ --------------
+ (1,foo,,3,0)
+ (1 row)
+
+ select v #= ('e' => null) from testhstore0 v;
+ ?column?
+ ---------------
+ (1,foo,1.2,3)
+ (1 row)
+
+ select v #= ('e' => null) from testhstore1 v;
+ ERROR: domain hstestdom1 does not allow null values
+ select v #= (('b' => 'foo') || ('a' => '123')) from testhstore1 v;
+ ?column?
+ -------------------
+ (123,foo,1.2,3,0)
+ (1 row)
+
+ select v #= (('b' => 'foo') || ('e' => '123')) from testhstore1 v;
+ ?column?
+ -------------------
+ (1,foo,1.2,3,123)
+ (1 row)
+
+ select v #= hstore '' from testhstore0 v;
+ ?column?
+ ---------------
+ (1,foo,1.2,3)
+ (1 row)
+
+ select v #= hstore '' from testhstore1 v;
+ ?column?
+ -----------------
+ (1,foo,1.2,3,0)
+ (1 row)
+
+ select null::testhstore1 #= (('c' => '3.45') || ('a' => '123'));
+ ERROR: domain hstestdom1 does not allow null values
+ select null::testhstore1 #= (('c' => '3.45') || ('e' => '123'));
+ ?column?
+ ---------------
+ (,,3.45,,123)
+ (1 row)
+
+ select null::testhstore0 #= hstore '';
+ ?column?
+ ----------
+ (,,,)
+ (1 row)
+
+ select null::testhstore1 #= hstore '';
+ ERROR: domain hstestdom1 does not allow null values
+ select v #= h from testhstore1 v, (values (hstore 'a=>123',1),('b=>foo,c=>3.21',2),('a=>null',3),('e=>123',4),('f=>blah',5)) x(h,i) order by i;
+ ?column?
+ -------------------
+ (123,foo,1.2,3,0)
+ (1,foo,3.21,3,0)
+ (,foo,1.2,3,0)
+ (1,foo,1.2,3,123)
+ (1,foo,1.2,3,0)
+ (5 rows)
+
+ -- keys/values
+ select akeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
+ akeys
+ --------------
+ {b,aa,cq,fg}
+ (1 row)
+
+ select akeys('""=>1');
+ akeys
+ -------
+ {""}
+ (1 row)
+
+ select akeys('');
+ akeys
+ -------
+ {}
+ (1 row)
+
+ select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
+ avals
+ -----------
+ {g,1,l,f}
+ (1 row)
+
+ select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>NULL');
+ avals
+ --------------
+ {g,1,l,NULL}
+ (1 row)
+
+ select avals('""=>1');
+ avals
+ -------
+ {1}
+ (1 row)
+
+ select avals('');
+ avals
+ -------
+ {}
+ (1 row)
+
+ select hstore_to_array('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore);
+ hstore_to_array
+ -------------------------
+ {b,g,aa,1,cq,l,fg,NULL}
+ (1 row)
+
+ select %% 'aa=>1, cq=>l, b=>g, fg=>NULL';
+ ?column?
+ -------------------------
+ {b,g,aa,1,cq,l,fg,NULL}
+ (1 row)
+
+ select hstore_to_matrix('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore);
+ hstore_to_matrix
+ ---------------------------------
+ {{b,g},{aa,1},{cq,l},{fg,NULL}}
+ (1 row)
+
+ select %# 'aa=>1, cq=>l, b=>g, fg=>NULL';
+ ?column?
+ ---------------------------------
+ {{b,g},{aa,1},{cq,l},{fg,NULL}}
+ (1 row)
+
+ select * from skeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
+ skeys
+ -------
+ b
+ aa
+ cq
+ fg
+ (4 rows)
+
+ select * from skeys('""=>1');
+ skeys
+ -------
+
+ (1 row)
+
+ select * from skeys('');
+ skeys
+ -------
+ (0 rows)
+
+ select * from svals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
+ svals
+ -------
+ g
+ 1
+ l
+ f
+ (4 rows)
+
+ select *, svals is null from svals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>NULL');
+ svals | ?column?
+ -------+----------
+ g | f
+ 1 | f
+ l | f
+ | t
+ (4 rows)
+
+ select * from svals('""=>1');
+ svals
+ -------
+ 1
+ (1 row)
+
+ select * from svals('');
+ svals
+ -------
+ (0 rows)
+
+ select * from each('aaa=>bq, b=>NULL, ""=>1 ');
+ key | value
+ -----+-------
+ | 1
+ b |
+ aaa | bq
+ (3 rows)
+
+ -- @>
+ select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b';
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, c=>NULL';
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, g=>NULL';
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ select 'a=>b, b=>1, c=>NULL'::hstore @> 'g=>NULL';
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>c';
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b';
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, c=>q';
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ CREATE TABLE testhstore (h hstore);
+ \copy testhstore from 'data/hstore.data'
+ select count(*) from testhstore where h @> 'wait=>NULL';
+ count
+ -------
+ 1
+ (1 row)
+
+ select count(*) from testhstore where h @> 'wait=>CC';
+ count
+ -------
+ 15
+ (1 row)
+
+ select count(*) from testhstore where h @> 'wait=>CC, public=>t';
+ count
+ -------
+ 2
+ (1 row)
+
+ select count(*) from testhstore where h ? 'public';
+ count
+ -------
+ 194
+ (1 row)
+
+ select count(*) from testhstore where h ?| ARRAY['public','disabled'];
+ count
+ -------
+ 337
+ (1 row)
+
+ select count(*) from testhstore where h ?& ARRAY['public','disabled'];
+ count
+ -------
+ 42
+ (1 row)
+
+ create index hidx on testhstore using gist(h);
+ set enable_seqscan=off;
+ select count(*) from testhstore where h @> 'wait=>NULL';
+ count
+ -------
+ 1
+ (1 row)
+
+ select count(*) from testhstore where h @> 'wait=>CC';
+ count
+ -------
+ 15
+ (1 row)
+
+ select count(*) from testhstore where h @> 'wait=>CC, public=>t';
+ count
+ -------
+ 2
+ (1 row)
+
+ select count(*) from testhstore where h ? 'public';
+ count
+ -------
+ 194
+ (1 row)
+
+ select count(*) from testhstore where h ?| ARRAY['public','disabled'];
+ count
+ -------
+ 337
+ (1 row)
+
+ select count(*) from testhstore where h ?& ARRAY['public','disabled'];
+ count
+ -------
+ 42
+ (1 row)
+
+ drop index hidx;
+ create index hidx on testhstore using gin (h);
+ set enable_seqscan=off;
+ select count(*) from testhstore where h @> 'wait=>NULL';
+ count
+ -------
+ 1
+ (1 row)
+
+ select count(*) from testhstore where h @> 'wait=>CC';
+ count
+ -------
+ 15
+ (1 row)
+
+ select count(*) from testhstore where h @> 'wait=>CC, public=>t';
+ count
+ -------
+ 2
+ (1 row)
+
+ select count(*) from testhstore where h ? 'public';
+ count
+ -------
+ 194
+ (1 row)
+
+ select count(*) from testhstore where h ?| ARRAY['public','disabled'];
+ count
+ -------
+ 337
+ (1 row)
+
+ select count(*) from testhstore where h ?& ARRAY['public','disabled'];
+ count
+ -------
+ 42
+ (1 row)
+
+ select count(*) from (select (each(h)).key from testhstore) as wow ;
+ count
+ -------
+ 4781
+ (1 row)
+
+ select key, count(*) from (select (each(h)).key from testhstore) as wow group by key order by count desc, key;
+ key | count
+ -----------+-------
+ line | 884
+ query | 207
+ pos | 203
+ node | 202
+ space | 197
+ status | 195
+ public | 194
+ title | 190
+ wait | 190
+ org | 189
+ user | 189
+ coauthors | 188
+ disabled | 185
+ indexed | 184
+ cleaned | 180
+ bad | 179
+ date | 179
+ world | 176
+ state | 172
+ subtitle | 169
+ auth | 168
+ abstract | 161
+ (22 rows)
+
+ -- sort/hash
+ select count(distinct h) from testhstore;
+ count
+ -------
+ 885
+ (1 row)
+
+ set enable_hashagg = false;
+ select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
+ count
+ -------
+ 885
+ (1 row)
+
+ set enable_hashagg = true;
+ set enable_sort = false;
+ select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
+ count
+ -------
+ 885
+ (1 row)
+
+ select distinct * from (values (hstore '' || ''),('')) v(h);
+ h
+ ---
+
+ (1 row)
+
+ set enable_sort = true;
+ -- btree
+ drop index hidx;
+ create index hidx on testhstore using btree (h);
+ set enable_seqscan=off;
+ select count(*) from testhstore where h #># 'p=>1';
+ count
+ -------
+ 125
+ (1 row)
+
+ select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexed=>t';
+ count
+ -------
+ 1
+ (1 row)
+
+ -- plpython integration
+ set plpython.hstore = 'public.hstore';
+ ERROR: unrecognized configuration parameter "plpython.hstore"
+ create language plpythonu;
+ create or replace function select_one(h hstore, idx text) returns text as $$
+ try:
+ return h.get(idx)
+ except AttributeError:
+ # h has not been transformed into a dict and is a string
+ return None
+ $$ language plpythonu;
+ select coalesce(select_one(h, 'node'), '<null>') as node from testhstore where select_one(h, 'line')::integer < 10;
+ node
+ ------
+ (0 rows)
+
+ reset plpython.hstore;
+ ERROR: unrecognized configuration parameter "plpython.hstore"
+ create or replace function select_one(h hstore, idx text) returns text as $$
+ try:
+ return h.get(idx)
+ except AttributeError:
+ # h has not been transformed into a dict and is a string
+ return None
+ $$ language plpythonu;
+ select coalesce(select_one(h, 'node'), '<null>') as node from testhstore where select_one(h, 'line')::integer < 10;
+ node
+ ------
+ (0 rows)
+
+ drop function select_one(hstore, text);
+ drop language plpythonu;
diff --git a/contrib/hstore/hstore.h b/contrib/hstore/hstore.h
index 8906397..6edfc70 100644
*** a/contrib/hstore/hstore.h
--- b/contrib/hstore/hstore.h
*************** extern Pairs *hstoreArrayToPairs(ArrayTy
*** 174,179 ****
--- 174,182 ----
#define HStoreExistsAllStrategyNumber 11
#define HStoreOldContainsStrategyNumber 13 /* backwards compatibility */
+ /* PL/Python support */
+ extern void hstore_plpython_init(void);
+
/*
* defining HSTORE_POLLUTE_NAMESPACE=0 will prevent use of old function names;
* for now, we default to on for the benefit of people restoring old dumps
diff --git a/contrib/hstore/hstore_io.c b/contrib/hstore/hstore_io.c
index 0d6f0b6..92c8db9 100644
*** a/contrib/hstore/hstore_io.c
--- b/contrib/hstore/hstore_io.c
*************** PG_MODULE_MAGIC;
*** 20,25 ****
--- 20,26 ----
/* old names for C functions */
HSTORE_POLLUTE(hstore_from_text, tconvert);
+ void _PG_init(void);
typedef struct
{
*************** hstore_send(PG_FUNCTION_ARGS)
*** 1211,1213 ****
--- 1212,1220 ----
PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
}
+
+ void
+ _PG_init(void)
+ {
+ hstore_plpython_init();
+ }
diff --git a/contrib/hstore/hstore_plpython.c b/contrib/hstore/hstore_plpython.c
index ...4bcdd83 .
*** a/contrib/hstore/hstore_plpython.c
--- b/contrib/hstore/hstore_plpython.c
***************
*** 0 ****
--- 1,247 ----
+ /*
+ * contrib/src/hstore_plpython.c
+ *
+ * bidirectional transformation between hstores and Python dictionary objects
+ */
+
+ /* Only build if PL/Python support is needed */
+ #if defined(HSTORE_PLPYTHON_SUPPORT)
+
+ #if defined(_MSC_VER) && defined(_DEBUG)
+ /* Python uses #pragma to bring in a non-default libpython on VC++ if
+ * _DEBUG is defined */
+ #undef _DEBUG
+ /* Also hide away errcode, since we load Python.h before postgres.h */
+ #define errcode __msvc_errcode
+ #include <Python.h>
+ #undef errcode
+ #define _DEBUG
+ #elif defined (_MSC_VER)
+ #define errcode __msvc_errcode
+ #include <Python.h>
+ #undef errcode
+ #else
+ #include <Python.h>
+ #endif
+
+ #include "postgres.h"
+ #include "utils/guc.h"
+ #include "utils/builtins.h"
+ #include "utils/syscache.h"
+ #include "catalog/namespace.h"
+
+ #include "plpython.h"
+ #include "hstore.h"
+
+ static Oid get_hstore_oid(const char *name);
+ static void set_hstore_parsers(Oid);
+
+ static PyObject *hstore_to_dict(void *, Datum);
+ static Datum dict_to_hstore(void *, int32, PyObject *);
+
+ /* GUC variables */
+
+ static char *hstore_name;
+
+ /* Previous hstore OID */
+
+ static Oid previous;
+
+ PLyParsers parsers;
+
+ static PyObject *
+ hstore_to_dict(void *ignored, Datum d)
+ {
+ HStore *hstore = DatumGetHStoreP(d);
+ char *base;
+ HEntry *entries;
+ int count;
+ int i;
+ PyObject *ret;
+
+ base = STRPTR(hstore);
+ entries = ARRPTR(hstore);
+
+ ret = PyDict_New();
+
+ count = HS_COUNT(hstore);
+
+ for (i = 0; i < count; i++)
+ {
+ PyObject *key, *val;
+
+ key = PyString_FromStringAndSize(HS_KEY(entries, base, i),
+ HS_KEYLEN(entries, i));
+ if (HS_VALISNULL(entries, i)) {
+ Py_INCREF(Py_None);
+ val = Py_None;
+ }
+ else {
+ val = PyString_FromStringAndSize(HS_VAL(entries, base, i),
+ HS_VALLEN(entries, i));
+ }
+
+ PyDict_SetItem(ret, key, val);
+ }
+
+ return ret;
+ }
+
+ static Datum
+ dict_to_hstore(void *ignored, int32 typmod, PyObject *dict)
+ {
+ HStore *hstore;
+ int pcount;
+ Pairs *pairs;
+ PyObject *key;
+ PyObject *value;
+ Py_ssize_t pos;
+ char *keys;
+ char *vals;
+ int keylen;
+ int vallen;
+ int buflen;
+ int i;
+
+ if (!PyDict_Check(dict))
+ ereport(ERROR,
+ (errmsg("hstores can only be constructed "
+ "from Python dictionaries")));
+
+ pcount = PyDict_Size(dict);
+ pairs = palloc(pcount * sizeof(Pairs));
+ pos = i = 0;
+ /* loop over the dictionary, creating a Pair for each key/value pair */
+ while (PyDict_Next(dict, &pos, &key, &value)) {
+ if (!PyString_Check(key))
+ elog(ERROR, "hstore keys have to be strings");
+
+ PyString_AsStringAndSize(key, &keys, &keylen);
+
+ if (strlen(keys) != keylen)
+ elog(ERROR, "hstore keys cannot contain NUL bytes");
+
+ pairs[i].key = pstrdup(keys);
+ pairs[i].keylen = hstoreCheckKeyLen(keylen);
+ pairs[i].needfree = true;
+
+ if (value == Py_None) {
+ pairs[i].val = NULL;
+ pairs[i].vallen = 0;
+ pairs[i].isnull = true;
+ }
+ else {
+ if (!PyString_Check(value))
+ elog(ERROR, "hstore values have to be strings");
+
+ PyString_AsStringAndSize(value, &vals, &vallen);
+
+ if (strlen(vals) != vallen)
+ elog(ERROR, "hstore values cannot contain NUL bytes");
+
+ pairs[i].val = pstrdup(vals);
+ pairs[i].vallen = hstoreCheckValLen(vallen);
+ pairs[i].isnull = false;
+ }
+
+ i++;
+ }
+ pcount = hstoreUniquePairs(pairs, pcount, &buflen);
+ hstore = hstorePairs(pairs, pcount, buflen);
+
+ return PointerGetDatum(hstore);
+ }
+
+ static const char *
+ recheck_hstore_oid(const char *newvalue, bool doit, GucSource source)
+ {
+ Oid hstore_oid;
+
+ hstore_oid = get_hstore_oid(newvalue);
+
+ if (*newvalue && !OidIsValid(hstore_oid))
+ return NULL;
+
+ if (doit)
+ set_hstore_parsers(hstore_oid);
+
+ return newvalue;
+ }
+
+ void
+ hstore_plpython_init(void)
+ {
+ DefineCustomStringVariable("plpython.hstore",
+ "The fully qualified name of the hstore type.",
+ NULL,
+ &hstore_name,
+ "",
+ PGC_SUSET,
+ 0,
+ recheck_hstore_oid,
+ NULL);
+
+ EmitWarningsOnPlaceholders("plpython");
+
+ previous = InvalidOid;
+ parsers.in = hstore_to_dict;
+ parsers.out = dict_to_hstore;
+
+ if (hstore_name && *hstore_name)
+ recheck_hstore_oid(hstore_name, true, PGC_S_FILE);
+ }
+
+ static Oid
+ get_hstore_oid(const char *name)
+ {
+ text *text_name;
+ List *hstore_name;
+ char *type_name;
+ Oid type_namespace;
+ Oid typoid;
+
+ Assert(name != NULL);
+
+ if (!(*name))
+ return InvalidOid;
+
+ text_name = cstring_to_text(name);
+ hstore_name = textToQualifiedNameList(text_name);
+ pfree(text_name);
+
+ type_namespace = QualifiedNameGetCreationNamespace(hstore_name, &type_name);
+
+ typoid = GetSysCacheOid2(TYPENAMENSP,
+ CStringGetDatum(type_name),
+ ObjectIdGetDatum(type_namespace));
+
+ return typoid;
+ }
+
+ static void
+ set_hstore_parsers(Oid hstore_oid)
+ {
+ char name[NAMEDATALEN];
+
+ if (OidIsValid(previous))
+ {
+ snprintf(name, NAMEDATALEN, PARSERS_VARIABLE_PATTERN, previous);
+ *find_rendezvous_variable(name) = NULL;
+ }
+
+ if (OidIsValid(hstore_oid))
+ {
+ snprintf(name, NAMEDATALEN, PARSERS_VARIABLE_PATTERN, hstore_oid);
+ *find_rendezvous_variable(name) = &parsers;
+ previous = hstore_oid;
+ }
+ }
+
+ #else /* !defined(HSTORE_PLPYTHON_SUPPORT) */
+
+ extern void hstore_plpython_init(void);
+
+ void
+ hstore_plpython_init(void) {};
+
+ #endif /* defined(HSTORE_PLPYTHON_SUPPORT) */
diff --git a/contrib/hstore/sql/hstore.sql b/contrib/hstore/sql/hstore.sql
index 58a7967..065e9a1 100644
*** a/contrib/hstore/sql/hstore.sql
--- b/contrib/hstore/sql/hstore.sql
*************** set enable_seqscan=off;
*** 338,340 ****
--- 338,369 ----
select count(*) from testhstore where h #># 'p=>1';
select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexed=>t';
+
+ -- plpython integration
+ set plpython.hstore = 'public.hstore';
+ create language plpythonu;
+
+ create or replace function select_one(h hstore, idx text) returns text as $$
+ try:
+ return h.get(idx)
+ except AttributeError:
+ # h has not been transformed into a dict and is a string
+ return None
+ $$ language plpythonu;
+
+ select coalesce(select_one(h, 'node'), '<null>') as node from testhstore where select_one(h, 'line')::integer < 10;
+
+ reset plpython.hstore;
+
+ create or replace function select_one(h hstore, idx text) returns text as $$
+ try:
+ return h.get(idx)
+ except AttributeError:
+ # h has not been transformed into a dict and is a string
+ return None
+ $$ language plpythonu;
+
+ select coalesce(select_one(h, 'node'), '<null>') as node from testhstore where select_one(h, 'line')::integer < 10;
+
+ drop function select_one(hstore, text);
+ drop language plpythonu;
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index fff7de7..2ed06f4 100644
*** a/src/pl/plpython/plpython.c
--- b/src/pl/plpython/plpython.c
*************** typedef int Py_ssize_t;
*** 90,95 ****
--- 90,97 ----
#include <fcntl.h>
/* postgreSQL stuff */
+ #include "plpython.h"
+
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "commands/trigger.h"
*************** static PyObject *PLyList_FromArray(PLyDa
*** 358,363 ****
--- 360,368 ----
static PyObject *PLyDict_FromTuple(PLyTypeInfo *, HeapTuple, TupleDesc);
+ static PLyParserIn PLy_get_custom_input_function(Oid oid);
+ static PLyParserOut PLy_get_custom_output_function(Oid oid);
+
static Datum PLyObject_ToBool(PLyObToDatum *, int32, PyObject *);
static Datum PLyObject_ToBytea(PLyObToDatum *, int32, PyObject *);
static Datum PLyObject_ToDatum(PLyObToDatum *, int32, PyObject *);
*************** PLy_output_datum_func2(PLyObToDatum *arg
*** 1819,1824 ****
--- 1824,1830 ----
{
Form_pg_type typeStruct = (Form_pg_type) GETSTRUCT(typeTup);
Oid element_type;
+ Oid argument_type;
perm_fmgr_info(typeStruct->typinput, &arg->typfunc);
arg->typoid = HeapTupleGetOid(typeTup);
*************** PLy_output_datum_func2(PLyObToDatum *arg
*** 1826,1837 ****
arg->typbyval = typeStruct->typbyval;
element_type = get_element_type(arg->typoid);
/*
* Select a conversion function to convert Python objects to PostgreSQL
* datums. Most data types can go through the generic function.
*/
! switch (getBaseType(element_type ? element_type : arg->typoid))
{
case BOOLOID:
arg->func = PLyObject_ToBool;
--- 1832,1844 ----
arg->typbyval = typeStruct->typbyval;
element_type = get_element_type(arg->typoid);
+ argument_type = getBaseType(element_type ? element_type : arg->typoid);
/*
* Select a conversion function to convert Python objects to PostgreSQL
* datums. Most data types can go through the generic function.
*/
! switch (argument_type)
{
case BOOLOID:
arg->func = PLyObject_ToBool;
*************** PLy_output_datum_func2(PLyObToDatum *arg
*** 1840,1846 ****
arg->func = PLyObject_ToBytea;
break;
default:
! arg->func = PLyObject_ToDatum;
break;
}
--- 1847,1859 ----
arg->func = PLyObject_ToBytea;
break;
default:
! /* Last ditch effort of finding a rendezvous variable pointing to
! * a parser function, useful for extension modules plugging in
! * their own parsers
! */
! arg->func = (PLyObToDatumFunc) PLy_get_custom_output_function(argument_type);
! if (arg->func == NULL)
! arg->func = PLyObject_ToDatum;
break;
}
*************** PLy_input_datum_func2(PLyDatumToOb *arg,
*** 1882,1887 ****
--- 1895,1901 ----
{
Form_pg_type typeStruct = (Form_pg_type) GETSTRUCT(typeTup);
Oid element_type = get_element_type(typeOid);
+ Oid argument_type;
/* Get the type's conversion information */
perm_fmgr_info(typeStruct->typoutput, &arg->typfunc);
*************** PLy_input_datum_func2(PLyDatumToOb *arg,
*** 1891,1898 ****
arg->typlen = typeStruct->typlen;
arg->typalign = typeStruct->typalign;
/* Determine which kind of Python object we will convert to */
! switch (getBaseType(element_type ? element_type : typeOid))
{
case BOOLOID:
arg->func = PLyBool_FromBool;
--- 1905,1914 ----
arg->typlen = typeStruct->typlen;
arg->typalign = typeStruct->typalign;
+ argument_type = getBaseType(element_type ? element_type : typeOid);
+
/* Determine which kind of Python object we will convert to */
! switch (argument_type)
{
case BOOLOID:
arg->func = PLyBool_FromBool;
*************** PLy_input_datum_func2(PLyDatumToOb *arg,
*** 1919,1925 ****
arg->func = PLyBytes_FromBytea;
break;
default:
! arg->func = PLyString_FromDatum;
break;
}
--- 1935,1947 ----
arg->func = PLyBytes_FromBytea;
break;
default:
! /* Last ditch effort of finding a rendezvous variable pointing to
! * a parser function, useful for extension modules plugging in
! * their own parsers
! */
! arg->func = (PLyDatumToObFunc) PLy_get_custom_input_function(argument_type);
! if (arg->func == NULL)
! arg->func = PLyString_FromDatum;
break;
}
*************** PLy_typeinfo_dealloc(PLyTypeInfo *arg)
*** 1960,1965 ****
--- 1982,2021 ----
}
}
+ /*
+ * Getting the parser functions from a rendezvous variable set by another
+ * extension.
+ */
+ static PLyParserIn
+ PLy_get_custom_input_function(Oid oid)
+ {
+ PLyParsers *parsers;
+ char name[NAMEDATALEN];
+
+ snprintf(name, NAMEDATALEN, PARSERS_VARIABLE_PATTERN, oid);
+ parsers = *find_rendezvous_variable(name);
+
+ if (parsers == NULL)
+ return NULL;
+
+ return parsers->in;
+ }
+
+ static PLyParserOut
+ PLy_get_custom_output_function(Oid oid)
+ {
+ PLyParsers *parsers;
+ char name[NAMEDATALEN];
+
+ snprintf(name, NAMEDATALEN, PARSERS_VARIABLE_PATTERN, oid);
+ parsers = *find_rendezvous_variable(name);
+
+ if (parsers == NULL)
+ return NULL;
+
+ return parsers->out;
+ }
+
static PyObject *
PLyBool_FromBool(PLyDatumToOb *arg, Datum d)
{
diff --git a/src/pl/plpython/plpython.h b/src/pl/plpython/plpython.h
index ...63c40c5 .
*** a/src/pl/plpython/plpython.h
--- b/src/pl/plpython/plpython.h
***************
*** 0 ****
--- 1,40 ----
+ /*
+ * src/pl/plpython/plpython.h
+ */
+ #ifndef __PLPYTHON_H__
+ #define __PLPYTHON_H__
+
+
+
+ /*
+ * Rendezvous variable pattern for parsers exported from other extensions
+ *
+ * An extension providing parsres for type X should look up the type's OID and
+ * set a rendezvous variable using this pattern that points to a PLyParsers
+ * structure. PL/Python will then use these parsers for arguments with that
+ * OID.
+ */
+ #define PARSERS_VARIABLE_PATTERN "plpython_%u_parsers"
+
+ /*
+ * Types for parsers functions that other modules can export to transform
+ * Datums into PyObjects and back. The types need to be compatible with
+ * PLyObToDatumFunc and PLyDatumToObFunc, but we don't want to expose too much
+ * of plpython.c's guts here, so the first arguments is mandated to be a void
+ * pointer that should not be touched. An extension should know exactly what
+ * it's dealing with, so there's no need for it to look at anything contained
+ * in PLyTypeInfo, which is what gets passed here.
+ *
+ * The output parser also gets the type's typmod, which might actually be
+ * useful.
+ */
+ typedef PyObject *(*PLyParserIn) (void *, Datum);
+ typedef Datum (*PLyParserOut) (void *, int32, PyObject *);
+
+ typedef struct PLyParsers
+ {
+ PLyParserIn in;
+ PLyParserOut out;
+ } PLyParsers;
+
+ #endif /* __PLPYTHON_H__ */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
