From 0ff7a2c2ad04404aeb1f45e1ae5d67dc91b575ee Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 14 May 2005 17:55:22 +0000 Subject: Convert the existing regression test scripts for the various optional PLs to use the standard pg_regress infrastructure. No changes in the tests themselves. Andrew Dunstan --- src/pl/Makefile | 22 +- src/pl/plperl/GNUmakefile | 13 +- src/pl/plperl/expected/plperl.out | 370 +++++++++++++++++++++ src/pl/plperl/sql/plperl.sql | 236 +++++++++++++ src/pl/plperl/test/runtest | 26 -- src/pl/plperl/test/test.expected | 357 -------------------- src/pl/plperl/test/test_queries.sql | 236 ------------- src/pl/plpython/Makefile | 17 +- src/pl/plpython/error.expected | 30 -- src/pl/plpython/expected/plpython_drop.out | 5 + src/pl/plpython/expected/plpython_error.out | 34 ++ src/pl/plpython/expected/plpython_function.out | 276 ++++++++++++++++ src/pl/plpython/expected/plpython_populate.out | 22 ++ src/pl/plpython/expected/plpython_schema.out | 43 +++ src/pl/plpython/expected/plpython_test.out | 184 +++++++++++ src/pl/plpython/feature.expected | 157 --------- src/pl/plpython/plpython_depopulate.sql | 2 - src/pl/plpython/plpython_deschema.sql | 17 - src/pl/plpython/plpython_drop.sql | 11 - src/pl/plpython/plpython_error.sql | 13 - src/pl/plpython/plpython_function.sql | 324 ------------------ src/pl/plpython/plpython_populate.sql | 28 -- src/pl/plpython/plpython_schema.sql | 42 --- src/pl/plpython/plpython_setof.sql | 11 - src/pl/plpython/plpython_test.sql | 70 ---- src/pl/plpython/sql/plpython_drop.sql | 6 + src/pl/plpython/sql/plpython_error.sql | 13 + src/pl/plpython/sql/plpython_function.sql | 324 ++++++++++++++++++ src/pl/plpython/sql/plpython_populate.sql | 28 ++ src/pl/plpython/sql/plpython_schema.sql | 42 +++ src/pl/plpython/sql/plpython_test.sql | 70 ++++ src/pl/plpython/test.sh | 58 ---- src/pl/tcl/Makefile | 13 +- src/pl/tcl/expected/pltcl_queries.out | 185 +++++++++++ src/pl/tcl/expected/pltcl_setup.out | 404 +++++++++++++++++++++++ src/pl/tcl/sql/pltcl_queries.sql | 75 +++++ src/pl/tcl/sql/pltcl_setup.sql | 438 +++++++++++++++++++++++++ src/pl/tcl/test/README | 8 - src/pl/tcl/test/runtest | 30 -- src/pl/tcl/test/test.expected | 178 ---------- src/pl/tcl/test/test_queries.sql | 75 ----- src/pl/tcl/test/test_setup.sql | 438 ------------------------- 42 files changed, 2807 insertions(+), 2124 deletions(-) create mode 100644 src/pl/plperl/expected/plperl.out create mode 100644 src/pl/plperl/sql/plperl.sql delete mode 100755 src/pl/plperl/test/runtest delete mode 100644 src/pl/plperl/test/test.expected delete mode 100644 src/pl/plperl/test/test_queries.sql delete mode 100644 src/pl/plpython/error.expected create mode 100644 src/pl/plpython/expected/plpython_drop.out create mode 100644 src/pl/plpython/expected/plpython_error.out create mode 100644 src/pl/plpython/expected/plpython_function.out create mode 100644 src/pl/plpython/expected/plpython_populate.out create mode 100644 src/pl/plpython/expected/plpython_schema.out create mode 100644 src/pl/plpython/expected/plpython_test.out delete mode 100644 src/pl/plpython/feature.expected delete mode 100644 src/pl/plpython/plpython_depopulate.sql delete mode 100644 src/pl/plpython/plpython_deschema.sql delete mode 100644 src/pl/plpython/plpython_drop.sql delete mode 100644 src/pl/plpython/plpython_error.sql delete mode 100644 src/pl/plpython/plpython_function.sql delete mode 100644 src/pl/plpython/plpython_populate.sql delete mode 100644 src/pl/plpython/plpython_schema.sql delete mode 100644 src/pl/plpython/plpython_setof.sql delete mode 100644 src/pl/plpython/plpython_test.sql create mode 100644 src/pl/plpython/sql/plpython_drop.sql create mode 100644 src/pl/plpython/sql/plpython_error.sql create mode 100644 src/pl/plpython/sql/plpython_function.sql create mode 100644 src/pl/plpython/sql/plpython_populate.sql create mode 100644 src/pl/plpython/sql/plpython_schema.sql create mode 100644 src/pl/plpython/sql/plpython_test.sql delete mode 100755 src/pl/plpython/test.sh create mode 100644 src/pl/tcl/expected/pltcl_queries.out create mode 100644 src/pl/tcl/expected/pltcl_setup.out create mode 100644 src/pl/tcl/sql/pltcl_queries.sql create mode 100644 src/pl/tcl/sql/pltcl_setup.sql delete mode 100644 src/pl/tcl/test/README delete mode 100755 src/pl/tcl/test/runtest delete mode 100644 src/pl/tcl/test/test.expected delete mode 100644 src/pl/tcl/test/test_queries.sql delete mode 100644 src/pl/tcl/test/test_setup.sql (limited to 'src') diff --git a/src/pl/Makefile b/src/pl/Makefile index 18cfeffc320..17a69c6a2a8 100644 --- a/src/pl/Makefile +++ b/src/pl/Makefile @@ -4,7 +4,7 @@ # # Copyright (c) 1994, Regents of the University of California # -# $PostgreSQL: pgsql/src/pl/Makefile,v 1.22 2003/11/29 19:52:12 pgsql Exp $ +# $PostgreSQL: pgsql/src/pl/Makefile,v 1.23 2005/05/14 17:55:20 tgl Exp $ # #------------------------------------------------------------------------- @@ -14,10 +14,6 @@ include $(top_builddir)/src/Makefile.global DIRS := plpgsql -ifeq ($(with_tcl), yes) -DIRS += tcl -endif - ifeq ($(with_perl), yes) DIRS += plperl endif @@ -26,8 +22,22 @@ ifeq ($(with_python), yes) DIRS += plpython endif +ifeq ($(with_tcl), yes) +DIRS += tcl +endif + all install installdirs uninstall depend distprep: - @for dir in $(DIRS); do $(MAKE) -C $$dir $@ || exit; done + @for dir in $(DIRS); do $(MAKE) -C $$dir $@ || exit 1; done clean distclean maintainer-clean: @for dir in $(DIRS); do $(MAKE) -C $$dir $@; done + +# We'd like check operations to run all the subtests before failing; +# also insert a sleep to ensure the previous test backend exited before +# we try to drop the regression database. +check installcheck: + @CHECKERR=0; for dir in $(DIRS); do \ + sleep 1; \ + $(MAKE) -C $$dir $@ || CHECKERR=$$?; \ + done; \ + exit $$CHECKERR diff --git a/src/pl/plperl/GNUmakefile b/src/pl/plperl/GNUmakefile index eb633d400b0..6a0d8fa9edf 100644 --- a/src/pl/plperl/GNUmakefile +++ b/src/pl/plperl/GNUmakefile @@ -1,5 +1,5 @@ # Makefile for PL/Perl -# $PostgreSQL: pgsql/src/pl/plperl/GNUmakefile,v 1.18 2004/11/19 19:22:58 tgl Exp $ +# $PostgreSQL: pgsql/src/pl/plperl/GNUmakefile,v 1.19 2005/05/14 17:55:20 tgl Exp $ subdir = src/pl/plperl top_builddir = ../../.. @@ -36,6 +36,8 @@ OBJS = plperl.o spi_internal.o SPI.o SHLIB_LINK = $(perl_embed_ldflags) $(BE_DLLLIBS) +REGRESS = plperl + include $(top_srcdir)/src/Makefile.shlib @@ -59,8 +61,17 @@ installdirs: uninstall: rm -f $(DESTDIR)$(pkglibdir)/plperl$(DLSUFFIX) +installcheck: submake + $(SHELL) $(top_builddir)/src/test/regress/pg_regress --load-language=plperl $(REGRESS) + +.PHONY: submake +submake: + $(MAKE) -C $(top_builddir)/src/test/regress pg_regress + clean distclean maintainer-clean: clean-lib rm -f SPI.c $(OBJS) + rm -rf results + rm -f regression.diffs regression.out else # can't build diff --git a/src/pl/plperl/expected/plperl.out b/src/pl/plperl/expected/plperl.out new file mode 100644 index 00000000000..c488c4a4fca --- /dev/null +++ b/src/pl/plperl/expected/plperl.out @@ -0,0 +1,370 @@ +-- +-- checkpoint so that if we have a crash in the tests, replay of the +-- just-completed CREATE DATABASE won't discard the core dump file +-- +checkpoint; +-- +-- Test result value processing +-- +CREATE OR REPLACE FUNCTION perl_int(int) RETURNS INTEGER AS $$ +return undef; +$$ LANGUAGE plperl; +SELECT perl_int(11); + perl_int +---------- + +(1 row) + +SELECT * FROM perl_int(42); + perl_int +---------- + +(1 row) + +CREATE OR REPLACE FUNCTION perl_int(int) RETURNS INTEGER AS $$ +return $_[0] + 1; +$$ LANGUAGE plperl; +SELECT perl_int(11); + perl_int +---------- + 12 +(1 row) + +SELECT * FROM perl_int(42); + perl_int +---------- + 43 +(1 row) + +CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$ +return undef; +$$ LANGUAGE plperl; +SELECT perl_set_int(5); + perl_set_int +-------------- +(0 rows) + +SELECT * FROM perl_set_int(5); + perl_set_int +-------------- +(0 rows) + +CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$ +return [0..$_[0]]; +$$ LANGUAGE plperl; +SELECT perl_set_int(5); + perl_set_int +-------------- + 0 + 1 + 2 + 3 + 4 + 5 +(6 rows) + +SELECT * FROM perl_set_int(5); + perl_set_int +-------------- + 0 + 1 + 2 + 3 + 4 + 5 +(6 rows) + +CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text); +CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$ + return undef; +$$ LANGUAGE plperl; +SELECT perl_row(); + perl_row +---------- + +(1 row) + +SELECT * FROM perl_row(); + f1 | f2 | f3 +----+----+---- + | | +(1 row) + +CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$ + return {f2 => 'hello', f1 => 1, f3 => 'world'}; +$$ LANGUAGE plperl; +SELECT perl_row(); + perl_row +----------------- + (1,hello,world) +(1 row) + +SELECT * FROM perl_row(); + f1 | f2 | f3 +----+-------+------- + 1 | hello | world +(1 row) + +CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$ + return undef; +$$ LANGUAGE plperl; +SELECT perl_set(); + perl_set +---------- +(0 rows) + +SELECT * FROM perl_set(); + f1 | f2 | f3 +----+----+---- +(0 rows) + +CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$ + return [ + { f1 => 1, f2 => 'Hello', f3 => 'World' }, + undef, + { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } + ]; +$$ LANGUAGE plperl; +SELECT perl_set(); +ERROR: elements of Perl result array must be reference to hash +SELECT * FROM perl_set(); +ERROR: elements of Perl result array must be reference to hash +CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$ + return [ + { f1 => 1, f2 => 'Hello', f3 => 'World' }, + { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, + { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } + ]; +$$ LANGUAGE plperl; +SELECT perl_set(); + perl_set +---------------------- + (1,Hello,World) + (2,Hello,PostgreSQL) + (3,Hello,PL/Perl) +(3 rows) + +SELECT * FROM perl_set(); + f1 | f2 | f3 +----+-------+------------ + 1 | Hello | World + 2 | Hello | PostgreSQL + 3 | Hello | PL/Perl +(3 rows) + +CREATE OR REPLACE FUNCTION perl_record() RETURNS record AS $$ + return undef; +$$ LANGUAGE plperl; +SELECT perl_record(); + perl_record +------------- + +(1 row) + +SELECT * FROM perl_record(); +ERROR: a column definition list is required for functions returning "record" +SELECT * FROM perl_record() AS (f1 integer, f2 text, f3 text); + f1 | f2 | f3 +----+----+---- + | | +(1 row) + +CREATE OR REPLACE FUNCTION perl_record() RETURNS record AS $$ + return {f2 => 'hello', f1 => 1, f3 => 'world'}; +$$ LANGUAGE plperl; +SELECT perl_record(); +ERROR: function returning record called in context that cannot accept type record +SELECT * FROM perl_record(); +ERROR: a column definition list is required for functions returning "record" +SELECT * FROM perl_record() AS (f1 integer, f2 text, f3 text); + f1 | f2 | f3 +----+-------+------- + 1 | hello | world +(1 row) + +CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$ + return undef; +$$ LANGUAGE plperl; +SELECT perl_record_set(); + perl_record_set +----------------- +(0 rows) + +SELECT * FROM perl_record_set(); +ERROR: a column definition list is required for functions returning "record" +SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text); + f1 | f2 | f3 +----+----+---- +(0 rows) + +CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$ + return [ + { f1 => 1, f2 => 'Hello', f3 => 'World' }, + undef, + { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } + ]; +$$ LANGUAGE plperl; +SELECT perl_record_set(); +ERROR: function returning record called in context that cannot accept type record +SELECT * FROM perl_record_set(); +ERROR: a column definition list is required for functions returning "record" +SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text); +ERROR: elements of Perl result array must be reference to hash +CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$ + return [ + { f1 => 1, f2 => 'Hello', f3 => 'World' }, + { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, + { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } + ]; +$$ LANGUAGE plperl; +SELECT perl_record_set(); +ERROR: function returning record called in context that cannot accept type record +SELECT * FROM perl_record_set(); +ERROR: a column definition list is required for functions returning "record" +SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text); + f1 | f2 | f3 +----+-------+------------ + 1 | Hello | World + 2 | Hello | PostgreSQL + 3 | Hello | PL/Perl +(3 rows) + +CREATE OR REPLACE FUNCTION +perl_out_params(f1 out integer, f2 out text, f3 out text) AS $$ + return {f2 => 'hello', f1 => 1, f3 => 'world'}; +$$ LANGUAGE plperl; +SELECT perl_out_params(); + perl_out_params +----------------- + (1,hello,world) +(1 row) + +SELECT * FROM perl_out_params(); + f1 | f2 | f3 +----+-------+------- + 1 | hello | world +(1 row) + +SELECT (perl_out_params()).f2; + f2 +------- + hello +(1 row) + +CREATE OR REPLACE FUNCTION +perl_out_params_set(out f1 integer, out f2 text, out f3 text) +RETURNS SETOF record AS $$ + return [ + { f1 => 1, f2 => 'Hello', f3 => 'World' }, + { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, + { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } + ]; +$$ LANGUAGE plperl; +SELECT perl_out_params_set(); + perl_out_params_set +---------------------- + (1,Hello,World) + (2,Hello,PostgreSQL) + (3,Hello,PL/Perl) +(3 rows) + +SELECT * FROM perl_out_params_set(); + f1 | f2 | f3 +----+-------+------------ + 1 | Hello | World + 2 | Hello | PostgreSQL + 3 | Hello | PL/Perl +(3 rows) + +SELECT (perl_out_params_set()).f3; + f3 +------------ + World + PostgreSQL + PL/Perl +(3 rows) + +-- +-- Check behavior with erroneous return values +-- +CREATE TYPE footype AS (x INTEGER, y INTEGER); +CREATE OR REPLACE FUNCTION foo_good() RETURNS SETOF footype AS $$ +return [ + {x => 1, y => 2}, + {x => 3, y => 4} +]; +$$ LANGUAGE plperl; +SELECT * FROM foo_good(); + x | y +---+--- + 1 | 2 + 3 | 4 +(2 rows) + +CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$ + return {y => 3, z => 4}; +$$ LANGUAGE plperl; +SELECT * FROM foo_bad(); +ERROR: Perl hash contains nonexistent column "z" +CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$ +return 42; +$$ LANGUAGE plperl; +SELECT * FROM foo_bad(); +ERROR: composite-returning Perl function must return reference to hash +CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$ +return [ + [1, 2], + [3, 4] +]; +$$ LANGUAGE plperl; +SELECT * FROM foo_bad(); +ERROR: composite-returning Perl function must return reference to hash +CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ + return 42; +$$ LANGUAGE plperl; +SELECT * FROM foo_set_bad(); +ERROR: set-returning Perl function must return reference to array +CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ + return {y => 3, z => 4}; +$$ LANGUAGE plperl; +SELECT * FROM foo_set_bad(); +ERROR: set-returning Perl function must return reference to array +CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ +return [ + [1, 2], + [3, 4] +]; +$$ LANGUAGE plperl; +SELECT * FROM foo_set_bad(); +ERROR: elements of Perl result array must be reference to hash +CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ +return [ + {y => 3, z => 4} +]; +$$ LANGUAGE plperl; +SELECT * FROM foo_set_bad(); +ERROR: Perl hash contains nonexistent column "z" +-- +-- Check passing a tuple argument +-- +CREATE OR REPLACE FUNCTION perl_get_field(footype, text) RETURNS integer AS $$ + return $_[0]->{$_[1]}; +$$ LANGUAGE plperl; +SELECT perl_get_field((11,12), 'x'); + perl_get_field +---------------- + 11 +(1 row) + +SELECT perl_get_field((11,12), 'y'); + perl_get_field +---------------- + 12 +(1 row) + +SELECT perl_get_field((11,12), 'z'); + perl_get_field +---------------- + +(1 row) + diff --git a/src/pl/plperl/sql/plperl.sql b/src/pl/plperl/sql/plperl.sql new file mode 100644 index 00000000000..0cfcd1752db --- /dev/null +++ b/src/pl/plperl/sql/plperl.sql @@ -0,0 +1,236 @@ +-- +-- checkpoint so that if we have a crash in the tests, replay of the +-- just-completed CREATE DATABASE won't discard the core dump file +-- +checkpoint; + +-- +-- Test result value processing +-- + +CREATE OR REPLACE FUNCTION perl_int(int) RETURNS INTEGER AS $$ +return undef; +$$ LANGUAGE plperl; + +SELECT perl_int(11); +SELECT * FROM perl_int(42); + +CREATE OR REPLACE FUNCTION perl_int(int) RETURNS INTEGER AS $$ +return $_[0] + 1; +$$ LANGUAGE plperl; + +SELECT perl_int(11); +SELECT * FROM perl_int(42); + + +CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$ +return undef; +$$ LANGUAGE plperl; + +SELECT perl_set_int(5); +SELECT * FROM perl_set_int(5); + +CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$ +return [0..$_[0]]; +$$ LANGUAGE plperl; + +SELECT perl_set_int(5); +SELECT * FROM perl_set_int(5); + + +CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text); + +CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$ + return undef; +$$ LANGUAGE plperl; + +SELECT perl_row(); +SELECT * FROM perl_row(); + +CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$ + return {f2 => 'hello', f1 => 1, f3 => 'world'}; +$$ LANGUAGE plperl; + +SELECT perl_row(); +SELECT * FROM perl_row(); + + +CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$ + return undef; +$$ LANGUAGE plperl; + +SELECT perl_set(); +SELECT * FROM perl_set(); + +CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$ + return [ + { f1 => 1, f2 => 'Hello', f3 => 'World' }, + undef, + { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } + ]; +$$ LANGUAGE plperl; + +SELECT perl_set(); +SELECT * FROM perl_set(); + +CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$ + return [ + { f1 => 1, f2 => 'Hello', f3 => 'World' }, + { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, + { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } + ]; +$$ LANGUAGE plperl; + +SELECT perl_set(); +SELECT * FROM perl_set(); + + + +CREATE OR REPLACE FUNCTION perl_record() RETURNS record AS $$ + return undef; +$$ LANGUAGE plperl; + +SELECT perl_record(); +SELECT * FROM perl_record(); +SELECT * FROM perl_record() AS (f1 integer, f2 text, f3 text); + +CREATE OR REPLACE FUNCTION perl_record() RETURNS record AS $$ + return {f2 => 'hello', f1 => 1, f3 => 'world'}; +$$ LANGUAGE plperl; + +SELECT perl_record(); +SELECT * FROM perl_record(); +SELECT * FROM perl_record() AS (f1 integer, f2 text, f3 text); + + +CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$ + return undef; +$$ LANGUAGE plperl; + +SELECT perl_record_set(); +SELECT * FROM perl_record_set(); +SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text); + +CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$ + return [ + { f1 => 1, f2 => 'Hello', f3 => 'World' }, + undef, + { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } + ]; +$$ LANGUAGE plperl; + +SELECT perl_record_set(); +SELECT * FROM perl_record_set(); +SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text); + +CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$ + return [ + { f1 => 1, f2 => 'Hello', f3 => 'World' }, + { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, + { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } + ]; +$$ LANGUAGE plperl; + +SELECT perl_record_set(); +SELECT * FROM perl_record_set(); +SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text); + +CREATE OR REPLACE FUNCTION +perl_out_params(f1 out integer, f2 out text, f3 out text) AS $$ + return {f2 => 'hello', f1 => 1, f3 => 'world'}; +$$ LANGUAGE plperl; + +SELECT perl_out_params(); +SELECT * FROM perl_out_params(); +SELECT (perl_out_params()).f2; + +CREATE OR REPLACE FUNCTION +perl_out_params_set(out f1 integer, out f2 text, out f3 text) +RETURNS SETOF record AS $$ + return [ + { f1 => 1, f2 => 'Hello', f3 => 'World' }, + { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, + { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } + ]; +$$ LANGUAGE plperl; + +SELECT perl_out_params_set(); +SELECT * FROM perl_out_params_set(); +SELECT (perl_out_params_set()).f3; + +-- +-- Check behavior with erroneous return values +-- + +CREATE TYPE footype AS (x INTEGER, y INTEGER); + +CREATE OR REPLACE FUNCTION foo_good() RETURNS SETOF footype AS $$ +return [ + {x => 1, y => 2}, + {x => 3, y => 4} +]; +$$ LANGUAGE plperl; + +SELECT * FROM foo_good(); + +CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$ + return {y => 3, z => 4}; +$$ LANGUAGE plperl; + +SELECT * FROM foo_bad(); + +CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$ +return 42; +$$ LANGUAGE plperl; + +SELECT * FROM foo_bad(); + +CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$ +return [ + [1, 2], + [3, 4] +]; +$$ LANGUAGE plperl; + +SELECT * FROM foo_bad(); + +CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ + return 42; +$$ LANGUAGE plperl; + +SELECT * FROM foo_set_bad(); + +CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ + return {y => 3, z => 4}; +$$ LANGUAGE plperl; + +SELECT * FROM foo_set_bad(); + +CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ +return [ + [1, 2], + [3, 4] +]; +$$ LANGUAGE plperl; + +SELECT * FROM foo_set_bad(); + +CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ +return [ + {y => 3, z => 4} +]; +$$ LANGUAGE plperl; + +SELECT * FROM foo_set_bad(); + +-- +-- Check passing a tuple argument +-- + +CREATE OR REPLACE FUNCTION perl_get_field(footype, text) RETURNS integer AS $$ + return $_[0]->{$_[1]}; +$$ LANGUAGE plperl; + +SELECT perl_get_field((11,12), 'x'); +SELECT perl_get_field((11,12), 'y'); +SELECT perl_get_field((11,12), 'z'); diff --git a/src/pl/plperl/test/runtest b/src/pl/plperl/test/runtest deleted file mode 100755 index 2d0e487670f..00000000000 --- a/src/pl/plperl/test/runtest +++ /dev/null @@ -1,26 +0,0 @@ -#!/bin/sh - -DBNAME=plperl_test -export DBNAME - -echo "**** Destroy old database $DBNAME ****" -dropdb $DBNAME - -sleep 1 - -echo "**** Create test database $DBNAME ****" -createdb $DBNAME || exit 1 - -echo "**** Create procedural language plperl ****" -createlang plperl $DBNAME || exit 1 - -echo "**** Running test queries ****" -psql -q -n -e $DBNAME test.out 2>&1 - -if diff test.expected test.out >/dev/null 2>&1 ; then - echo " Tests passed O.K." - rm test.out -else - echo " Tests failed - look at diffs between" - echo " test.expected and test.out" -fi diff --git a/src/pl/plperl/test/test.expected b/src/pl/plperl/test/test.expected deleted file mode 100644 index 340ed638b71..00000000000 --- a/src/pl/plperl/test/test.expected +++ /dev/null @@ -1,357 +0,0 @@ -checkpoint; -CREATE OR REPLACE FUNCTION perl_int(int) RETURNS INTEGER AS $$ -return undef; -$$ LANGUAGE plperl; -SELECT perl_int(11); - perl_int ----------- - -(1 row) - -SELECT * FROM perl_int(42); - perl_int ----------- - -(1 row) - -CREATE OR REPLACE FUNCTION perl_int(int) RETURNS INTEGER AS $$ -return $_[0] + 1; -$$ LANGUAGE plperl; -SELECT perl_int(11); - perl_int ----------- - 12 -(1 row) - -SELECT * FROM perl_int(42); - perl_int ----------- - 43 -(1 row) - -CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$ -return undef; -$$ LANGUAGE plperl; -SELECT perl_set_int(5); - perl_set_int --------------- -(0 rows) - -SELECT * FROM perl_set_int(5); - perl_set_int --------------- -(0 rows) - -CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$ -return [0..$_[0]]; -$$ LANGUAGE plperl; -SELECT perl_set_int(5); - perl_set_int --------------- - 0 - 1 - 2 - 3 - 4 - 5 -(6 rows) - -SELECT * FROM perl_set_int(5); - perl_set_int --------------- - 0 - 1 - 2 - 3 - 4 - 5 -(6 rows) - -CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text); -CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$ - return undef; -$$ LANGUAGE plperl; -SELECT perl_row(); - perl_row ----------- - -(1 row) - -SELECT * FROM perl_row(); - f1 | f2 | f3 -----+----+---- - | | -(1 row) - -CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$ - return {f2 => 'hello', f1 => 1, f3 => 'world'}; -$$ LANGUAGE plperl; -SELECT perl_row(); - perl_row ------------------ - (1,hello,world) -(1 row) - -SELECT * FROM perl_row(); - f1 | f2 | f3 -----+-------+------- - 1 | hello | world -(1 row) - -CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$ - return undef; -$$ LANGUAGE plperl; -SELECT perl_set(); - perl_set ----------- -(0 rows) - -SELECT * FROM perl_set(); - f1 | f2 | f3 -----+----+---- -(0 rows) - -CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$ - return [ - { f1 => 1, f2 => 'Hello', f3 => 'World' }, - undef, - { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } - ]; -$$ LANGUAGE plperl; -SELECT perl_set(); -ERROR: elements of Perl result array must be reference to hash -SELECT * FROM perl_set(); -ERROR: elements of Perl result array must be reference to hash -CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$ - return [ - { f1 => 1, f2 => 'Hello', f3 => 'World' }, - { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, - { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } - ]; -$$ LANGUAGE plperl; -SELECT perl_set(); - perl_set ----------------------- - (1,Hello,World) - (2,Hello,PostgreSQL) - (3,Hello,PL/Perl) -(3 rows) - -SELECT * FROM perl_set(); - f1 | f2 | f3 -----+-------+------------ - 1 | Hello | World - 2 | Hello | PostgreSQL - 3 | Hello | PL/Perl -(3 rows) - -CREATE OR REPLACE FUNCTION perl_record() RETURNS record AS $$ - return undef; -$$ LANGUAGE plperl; -SELECT perl_record(); - perl_record -------------- - -(1 row) - -SELECT * FROM perl_record(); -ERROR: a column definition list is required for functions returning "record" -SELECT * FROM perl_record() AS (f1 integer, f2 text, f3 text); - f1 | f2 | f3 -----+----+---- - | | -(1 row) - -CREATE OR REPLACE FUNCTION perl_record() RETURNS record AS $$ - return {f2 => 'hello', f1 => 1, f3 => 'world'}; -$$ LANGUAGE plperl; -SELECT perl_record(); -ERROR: function returning record called in context that cannot accept type record -SELECT * FROM perl_record(); -ERROR: a column definition list is required for functions returning "record" -SELECT * FROM perl_record() AS (f1 integer, f2 text, f3 text); - f1 | f2 | f3 -----+-------+------- - 1 | hello | world -(1 row) - -CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$ - return undef; -$$ LANGUAGE plperl; -SELECT perl_record_set(); - perl_record_set ------------------ -(0 rows) - -SELECT * FROM perl_record_set(); -ERROR: a column definition list is required for functions returning "record" -SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text); - f1 | f2 | f3 -----+----+---- -(0 rows) - -CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$ - return [ - { f1 => 1, f2 => 'Hello', f3 => 'World' }, - undef, - { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } - ]; -$$ LANGUAGE plperl; -SELECT perl_record_set(); -ERROR: function returning record called in context that cannot accept type record -SELECT * FROM perl_record_set(); -ERROR: a column definition list is required for functions returning "record" -SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text); -ERROR: elements of Perl result array must be reference to hash -CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$ - return [ - { f1 => 1, f2 => 'Hello', f3 => 'World' }, - { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, - { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } - ]; -$$ LANGUAGE plperl; -SELECT perl_record_set(); -ERROR: function returning record called in context that cannot accept type record -SELECT * FROM perl_record_set(); -ERROR: a column definition list is required for functions returning "record" -SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text); - f1 | f2 | f3 -----+-------+------------ - 1 | Hello | World - 2 | Hello | PostgreSQL - 3 | Hello | PL/Perl -(3 rows) - -CREATE OR REPLACE FUNCTION -perl_out_params(f1 out integer, f2 out text, f3 out text) AS $$ - return {f2 => 'hello', f1 => 1, f3 => 'world'}; -$$ LANGUAGE plperl; -SELECT perl_out_params(); - perl_out_params ------------------ - (1,hello,world) -(1 row) - -SELECT * FROM perl_out_params(); - f1 | f2 | f3 -----+-------+------- - 1 | hello | world -(1 row) - -SELECT (perl_out_params()).f2; - f2 -------- - hello -(1 row) - -CREATE OR REPLACE FUNCTION -perl_out_params_set(out f1 integer, out f2 text, out f3 text) -RETURNS SETOF record AS $$ - return [ - { f1 => 1, f2 => 'Hello', f3 => 'World' }, - { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, - { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } - ]; -$$ LANGUAGE plperl; -SELECT perl_out_params_set(); - perl_out_params_set ----------------------- - (1,Hello,World) - (2,Hello,PostgreSQL) - (3,Hello,PL/Perl) -(3 rows) - -SELECT * FROM perl_out_params_set(); - f1 | f2 | f3 -----+-------+------------ - 1 | Hello | World - 2 | Hello | PostgreSQL - 3 | Hello | PL/Perl -(3 rows) - -SELECT (perl_out_params_set()).f3; - f3 ------------- - World - PostgreSQL - PL/Perl -(3 rows) - -CREATE TYPE footype AS (x INTEGER, y INTEGER); -CREATE OR REPLACE FUNCTION foo_good() RETURNS SETOF footype AS $$ -return [ - {x => 1, y => 2}, - {x => 3, y => 4} -]; -$$ LANGUAGE plperl; -SELECT * FROM foo_good(); - x | y ----+--- - 1 | 2 - 3 | 4 -(2 rows) - -CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$ - return {y => 3, z => 4}; -$$ LANGUAGE plperl; -SELECT * FROM foo_bad(); -ERROR: Perl hash contains nonexistent column "z" -CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$ -return 42; -$$ LANGUAGE plperl; -SELECT * FROM foo_bad(); -ERROR: composite-returning Perl function must return reference to hash -CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$ -return [ - [1, 2], - [3, 4] -]; -$$ LANGUAGE plperl; -SELECT * FROM foo_bad(); -ERROR: composite-returning Perl function must return reference to hash -CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ - return 42; -$$ LANGUAGE plperl; -SELECT * FROM foo_set_bad(); -ERROR: set-returning Perl function must return reference to array -CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ - return {y => 3, z => 4}; -$$ LANGUAGE plperl; -SELECT * FROM foo_set_bad(); -ERROR: set-returning Perl function must return reference to array -CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ -return [ - [1, 2], - [3, 4] -]; -$$ LANGUAGE plperl; -SELECT * FROM foo_set_bad(); -ERROR: elements of Perl result array must be reference to hash -CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ -return [ - {y => 3, z => 4} -]; -$$ LANGUAGE plperl; -SELECT * FROM foo_set_bad(); -ERROR: Perl hash contains nonexistent column "z" -CREATE OR REPLACE FUNCTION perl_get_field(footype, text) RETURNS integer AS $$ - return $_[0]->{$_[1]}; -$$ LANGUAGE plperl; -SELECT perl_get_field((11,12), 'x'); - perl_get_field ----------------- - 11 -(1 row) - -SELECT perl_get_field((11,12), 'y'); - perl_get_field ----------------- - 12 -(1 row) - -SELECT perl_get_field((11,12), 'z'); - perl_get_field ----------------- - -(1 row) - diff --git a/src/pl/plperl/test/test_queries.sql b/src/pl/plperl/test/test_queries.sql deleted file mode 100644 index 0cfcd1752db..00000000000 --- a/src/pl/plperl/test/test_queries.sql +++ /dev/null @@ -1,236 +0,0 @@ --- --- checkpoint so that if we have a crash in the tests, replay of the --- just-completed CREATE DATABASE won't discard the core dump file --- -checkpoint; - --- --- Test result value processing --- - -CREATE OR REPLACE FUNCTION perl_int(int) RETURNS INTEGER AS $$ -return undef; -$$ LANGUAGE plperl; - -SELECT perl_int(11); -SELECT * FROM perl_int(42); - -CREATE OR REPLACE FUNCTION perl_int(int) RETURNS INTEGER AS $$ -return $_[0] + 1; -$$ LANGUAGE plperl; - -SELECT perl_int(11); -SELECT * FROM perl_int(42); - - -CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$ -return undef; -$$ LANGUAGE plperl; - -SELECT perl_set_int(5); -SELECT * FROM perl_set_int(5); - -CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$ -return [0..$_[0]]; -$$ LANGUAGE plperl; - -SELECT perl_set_int(5); -SELECT * FROM perl_set_int(5); - - -CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text); - -CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$ - return undef; -$$ LANGUAGE plperl; - -SELECT perl_row(); -SELECT * FROM perl_row(); - -CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$ - return {f2 => 'hello', f1 => 1, f3 => 'world'}; -$$ LANGUAGE plperl; - -SELECT perl_row(); -SELECT * FROM perl_row(); - - -CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$ - return undef; -$$ LANGUAGE plperl; - -SELECT perl_set(); -SELECT * FROM perl_set(); - -CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$ - return [ - { f1 => 1, f2 => 'Hello', f3 => 'World' }, - undef, - { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } - ]; -$$ LANGUAGE plperl; - -SELECT perl_set(); -SELECT * FROM perl_set(); - -CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$ - return [ - { f1 => 1, f2 => 'Hello', f3 => 'World' }, - { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, - { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } - ]; -$$ LANGUAGE plperl; - -SELECT perl_set(); -SELECT * FROM perl_set(); - - - -CREATE OR REPLACE FUNCTION perl_record() RETURNS record AS $$ - return undef; -$$ LANGUAGE plperl; - -SELECT perl_record(); -SELECT * FROM perl_record(); -SELECT * FROM perl_record() AS (f1 integer, f2 text, f3 text); - -CREATE OR REPLACE FUNCTION perl_record() RETURNS record AS $$ - return {f2 => 'hello', f1 => 1, f3 => 'world'}; -$$ LANGUAGE plperl; - -SELECT perl_record(); -SELECT * FROM perl_record(); -SELECT * FROM perl_record() AS (f1 integer, f2 text, f3 text); - - -CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$ - return undef; -$$ LANGUAGE plperl; - -SELECT perl_record_set(); -SELECT * FROM perl_record_set(); -SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text); - -CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$ - return [ - { f1 => 1, f2 => 'Hello', f3 => 'World' }, - undef, - { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } - ]; -$$ LANGUAGE plperl; - -SELECT perl_record_set(); -SELECT * FROM perl_record_set(); -SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text); - -CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$ - return [ - { f1 => 1, f2 => 'Hello', f3 => 'World' }, - { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, - { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } - ]; -$$ LANGUAGE plperl; - -SELECT perl_record_set(); -SELECT * FROM perl_record_set(); -SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text); - -CREATE OR REPLACE FUNCTION -perl_out_params(f1 out integer, f2 out text, f3 out text) AS $$ - return {f2 => 'hello', f1 => 1, f3 => 'world'}; -$$ LANGUAGE plperl; - -SELECT perl_out_params(); -SELECT * FROM perl_out_params(); -SELECT (perl_out_params()).f2; - -CREATE OR REPLACE FUNCTION -perl_out_params_set(out f1 integer, out f2 text, out f3 text) -RETURNS SETOF record AS $$ - return [ - { f1 => 1, f2 => 'Hello', f3 => 'World' }, - { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, - { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } - ]; -$$ LANGUAGE plperl; - -SELECT perl_out_params_set(); -SELECT * FROM perl_out_params_set(); -SELECT (perl_out_params_set()).f3; - --- --- Check behavior with erroneous return values --- - -CREATE TYPE footype AS (x INTEGER, y INTEGER); - -CREATE OR REPLACE FUNCTION foo_good() RETURNS SETOF footype AS $$ -return [ - {x => 1, y => 2}, - {x => 3, y => 4} -]; -$$ LANGUAGE plperl; - -SELECT * FROM foo_good(); - -CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$ - return {y => 3, z => 4}; -$$ LANGUAGE plperl; - -SELECT * FROM foo_bad(); - -CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$ -return 42; -$$ LANGUAGE plperl; - -SELECT * FROM foo_bad(); - -CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$ -return [ - [1, 2], - [3, 4] -]; -$$ LANGUAGE plperl; - -SELECT * FROM foo_bad(); - -CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ - return 42; -$$ LANGUAGE plperl; - -SELECT * FROM foo_set_bad(); - -CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ - return {y => 3, z => 4}; -$$ LANGUAGE plperl; - -SELECT * FROM foo_set_bad(); - -CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ -return [ - [1, 2], - [3, 4] -]; -$$ LANGUAGE plperl; - -SELECT * FROM foo_set_bad(); - -CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$ -return [ - {y => 3, z => 4} -]; -$$ LANGUAGE plperl; - -SELECT * FROM foo_set_bad(); - --- --- Check passing a tuple argument --- - -CREATE OR REPLACE FUNCTION perl_get_field(footype, text) RETURNS integer AS $$ - return $_[0]->{$_[1]}; -$$ LANGUAGE plperl; - -SELECT perl_get_field((11,12), 'x'); -SELECT perl_get_field((11,12), 'y'); -SELECT perl_get_field((11,12), 'z'); diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile index f2793d9ed06..e6f9a39e8f0 100644 --- a/src/pl/plpython/Makefile +++ b/src/pl/plpython/Makefile @@ -1,4 +1,4 @@ -# $PostgreSQL: pgsql/src/pl/plpython/Makefile,v 1.18 2004/11/19 19:23:01 tgl Exp $ +# $PostgreSQL: pgsql/src/pl/plpython/Makefile,v 1.19 2005/05/14 17:55:21 tgl Exp $ subdir = src/pl/plpython top_builddir = ../../.. @@ -58,6 +58,8 @@ endif SHLIB_LINK = $(BE_DLLLIBS) $(python_libspec) $(python_additional_libs) +REGRESS = plpython_schema plpython_populate plpython_function plpython_test plpython_error plpython_drop + include $(top_srcdir)/src/Makefile.shlib @@ -78,16 +80,21 @@ installdirs: uninstall: rm -f $(DESTDIR)$(pkglibdir)/plpython$(DLSUFFIX) +installcheck: submake + $(SHELL) $(top_builddir)/src/test/regress/pg_regress --load-language=plpythonu $(REGRESS) + +.PHONY: submake +submake: + $(MAKE) -C $(top_builddir)/src/test/regress pg_regress + clean distclean maintainer-clean: clean-lib rm -f $(OBJS) - @rm -f error.diff feature.diff error.output feature.output test.log + rm -rf results + rm -f regression.diffs regression.out ifeq ($(PORTNAME), win32) rm -f python${pytverstr}.def endif -installcheck: - PATH=$(bindir):$$PATH $(SHELL) $(srcdir)/test.sh - else # can't build all: diff --git a/src/pl/plpython/error.expected b/src/pl/plpython/error.expected deleted file mode 100644 index 3c1614769e8..00000000000 --- a/src/pl/plpython/error.expected +++ /dev/null @@ -1,30 +0,0 @@ -SELECT invalid_type_uncaught('rick'); -WARNING: plpython: in function invalid_type_uncaught: -DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare -ERROR: type "test" does not exist -SELECT invalid_type_caught('rick'); -WARNING: plpython: in function invalid_type_caught: -DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare -ERROR: type "test" does not exist -SELECT invalid_type_reraised('rick'); -WARNING: plpython: in function invalid_type_reraised: -DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare -ERROR: type "test" does not exist -SELECT valid_type('rick'); - valid_type ------------- - -(1 row) - -SELECT write_file('/tmp/plpython','Only trusted users should be able to do this!'); - write_file ------------------------------- - Wrote to file: /tmp/plpython -(1 row) - -SELECT read_file('/tmp/plpython'); - read_file ------------------------------------------------ - Only trusted users should be able to do this! -(1 row) - diff --git a/src/pl/plpython/expected/plpython_drop.out b/src/pl/plpython/expected/plpython_drop.out new file mode 100644 index 00000000000..fef642f7c06 --- /dev/null +++ b/src/pl/plpython/expected/plpython_drop.out @@ -0,0 +1,5 @@ +-- +-- For paranoia's sake, don't leave an untrusted language sitting around +-- +SET client_min_messages = WARNING; +DROP PROCEDURAL LANGUAGE plpythonu CASCADE; diff --git a/src/pl/plpython/expected/plpython_error.out b/src/pl/plpython/expected/plpython_error.out new file mode 100644 index 00000000000..f2bf34f996a --- /dev/null +++ b/src/pl/plpython/expected/plpython_error.out @@ -0,0 +1,34 @@ +-- test error handling, i forgot to restore Warn_restart in +-- the trigger handler once. the errors and subsequent core dump were +-- interesting. +SELECT invalid_type_uncaught('rick'); +WARNING: plpython: in function invalid_type_uncaught: +DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare +ERROR: type "test" does not exist +SELECT invalid_type_caught('rick'); +WARNING: plpython: in function invalid_type_caught: +DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare +ERROR: type "test" does not exist +SELECT invalid_type_reraised('rick'); +WARNING: plpython: in function invalid_type_reraised: +DETAIL: plpy.SPIError: Unknown error in PLy_spi_prepare +ERROR: type "test" does not exist +SELECT valid_type('rick'); + valid_type +------------ + +(1 row) + +-- Security sandbox tests +SELECT write_file('/tmp/plpython','Only trusted users should be able to do this!'); + write_file +------------------------------ + Wrote to file: /tmp/plpython +(1 row) + +SELECT read_file('/tmp/plpython'); + read_file +----------------------------------------------- + Only trusted users should be able to do this! +(1 row) + diff --git a/src/pl/plpython/expected/plpython_function.out b/src/pl/plpython/expected/plpython_function.out new file mode 100644 index 00000000000..def301affb1 --- /dev/null +++ b/src/pl/plpython/expected/plpython_function.out @@ -0,0 +1,276 @@ +CREATE FUNCTION global_test_one() returns text + AS +'if not SD.has_key("global_test"): + SD["global_test"] = "set by global_test_one" +if not GD.has_key("global_test"): + GD["global_test"] = "set by global_test_one" +return "SD: " + SD["global_test"] + ", GD: " + GD["global_test"]' + LANGUAGE plpythonu; +CREATE FUNCTION global_test_two() returns text + AS +'if not SD.has_key("global_test"): + SD["global_test"] = "set by global_test_two" +if not GD.has_key("global_test"): + GD["global_test"] = "set by global_test_two" +return "SD: " + SD["global_test"] + ", GD: " + GD["global_test"]' + LANGUAGE plpythonu; +CREATE FUNCTION static_test() returns int4 + AS +'if SD.has_key("call"): + SD["call"] = SD["call"] + 1 +else: + SD["call"] = 1 +return SD["call"] +' + LANGUAGE plpythonu; +-- import python modules +CREATE FUNCTION import_fail() returns text + AS +'try: + import foosocket +except Exception, ex: + plpy.notice("import socket failed -- %s" % str(ex)) + return "failed as expected" +return "succeeded, that wasn''t supposed to happen"' + LANGUAGE plpythonu; +CREATE FUNCTION import_succeed() returns text + AS +'try: + import array + import bisect + import calendar + import cmath + import errno + import math + import md5 + import operator + import random + import re + import sha + import string + import time + import whrandom +except Exception, ex: + plpy.notice("import failed -- %s" % str(ex)) + return "failed, that wasn''t supposed to happen" +return "succeeded, as expected"' + LANGUAGE plpythonu; +CREATE FUNCTION import_test_one(text) RETURNS text + AS +'import sha +digest = sha.new(args[0]) +return digest.hexdigest()' + LANGUAGE plpythonu; +CREATE FUNCTION import_test_two(users) RETURNS text + AS +'import sha +plain = args[0]["fname"] + args[0]["lname"] +digest = sha.new(plain); +return "sha hash of " + plain + " is " + digest.hexdigest()' + LANGUAGE plpythonu; +CREATE FUNCTION argument_test_one(users, text, text) RETURNS text + AS +'keys = args[0].keys() +keys.sort() +out = [] +for key in keys: + out.append("%s: %s" % (key, args[0][key])) +words = args[1] + " " + args[2] + " => {" + ", ".join(out) + "}" +return words' + LANGUAGE plpythonu; +-- these triggers are dedicated to HPHC of RI who +-- decided that my kid's name was william not willem, and +-- vigorously resisted all efforts at correction. they have +-- since gone bankrupt... +CREATE FUNCTION users_insert() returns trigger + AS +'if TD["new"]["fname"] == None or TD["new"]["lname"] == None: + return "SKIP" +if TD["new"]["username"] == None: + TD["new"]["username"] = TD["new"]["fname"][:1] + "_" + TD["new"]["lname"] + rv = "MODIFY" +else: + rv = None +if TD["new"]["fname"] == "william": + TD["new"]["fname"] = TD["args"][0] + rv = "MODIFY" +return rv' + LANGUAGE plpythonu; +CREATE FUNCTION users_update() returns trigger + AS +'if TD["event"] == "UPDATE": + if TD["old"]["fname"] != TD["new"]["fname"] and TD["old"]["fname"] == TD["args"][0]: + return "SKIP" +return None' + LANGUAGE plpythonu; +CREATE FUNCTION users_delete() RETURNS trigger + AS +'if TD["old"]["fname"] == TD["args"][0]: + return "SKIP" +return None' + LANGUAGE plpythonu; +CREATE TRIGGER users_insert_trig BEFORE INSERT ON users FOR EACH ROW + EXECUTE PROCEDURE users_insert ('willem'); +CREATE TRIGGER users_update_trig BEFORE UPDATE ON users FOR EACH ROW + EXECUTE PROCEDURE users_update ('willem'); +CREATE TRIGGER users_delete_trig BEFORE DELETE ON users FOR EACH ROW + EXECUTE PROCEDURE users_delete ('willem'); +-- nested calls +-- +CREATE FUNCTION nested_call_one(text) RETURNS text + AS +'q = "SELECT nested_call_two(''%s'')" % args[0] +r = plpy.execute(q) +return r[0]' + LANGUAGE plpythonu ; +CREATE FUNCTION nested_call_two(text) RETURNS text + AS +'q = "SELECT nested_call_three(''%s'')" % args[0] +r = plpy.execute(q) +return r[0]' + LANGUAGE plpythonu ; +CREATE FUNCTION nested_call_three(text) RETURNS text + AS +'return args[0]' + LANGUAGE plpythonu ; +-- some spi stuff +CREATE FUNCTION spi_prepared_plan_test_one(text) RETURNS text + AS +'if not SD.has_key("myplan"): + q = "SELECT count(*) FROM users WHERE lname = $1" + SD["myplan"] = plpy.prepare(q, [ "text" ]) +try: + rv = plpy.execute(SD["myplan"], [args[0]]) + return "there are " + str(rv[0]["count"]) + " " + str(args[0]) + "s" +except Exception, ex: + plpy.error(str(ex)) +return None +' + LANGUAGE plpythonu; +CREATE FUNCTION spi_prepared_plan_test_nested(text) RETURNS text + AS +'if not SD.has_key("myplan"): + q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % args[0] + SD["myplan"] = plpy.prepare(q) +try: + rv = plpy.execute(SD["myplan"]) + if len(rv): + return rv[0]["count"] +except Exception, ex: + plpy.error(str(ex)) +return None +' + LANGUAGE plpythonu; +/* really stupid function just to get the module loaded +*/ +CREATE FUNCTION stupid() RETURNS text AS 'return "zarkon"' LANGUAGE plpythonu; +/* a typo +*/ +CREATE FUNCTION invalid_type_uncaught(text) RETURNS text + AS +'if not SD.has_key("plan"): + q = "SELECT fname FROM users WHERE lname = $1" + SD["plan"] = plpy.prepare(q, [ "test" ]) +rv = plpy.execute(SD["plan"], [ args[0] ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpythonu; +/* for what it's worth catch the exception generated by + * the typo, and return None + */ +CREATE FUNCTION invalid_type_caught(text) RETURNS text + AS +'if not SD.has_key("plan"): + q = "SELECT fname FROM users WHERE lname = $1" + try: + SD["plan"] = plpy.prepare(q, [ "test" ]) + except plpy.SPIError, ex: + plpy.notice(str(ex)) + return None +rv = plpy.execute(SD["plan"], [ args[0] ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpythonu; +/* for what it's worth catch the exception generated by + * the typo, and reraise it as a plain error + */ +CREATE FUNCTION invalid_type_reraised(text) RETURNS text + AS +'if not SD.has_key("plan"): + q = "SELECT fname FROM users WHERE lname = $1" + try: + SD["plan"] = plpy.prepare(q, [ "test" ]) + except plpy.SPIError, ex: + plpy.error(str(ex)) +rv = plpy.execute(SD["plan"], [ args[0] ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpythonu; +/* no typo no messing about +*/ +CREATE FUNCTION valid_type(text) RETURNS text + AS +'if not SD.has_key("plan"): + SD["plan"] = plpy.prepare("SELECT fname FROM users WHERE lname = $1", [ "text" ]) +rv = plpy.execute(SD["plan"], [ args[0] ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpythonu; +/* Flat out syntax error +*/ +CREATE FUNCTION sql_syntax_error() RETURNS text + AS +'plpy.execute("syntax error")' + LANGUAGE plpythonu; +/* check the handling of uncaught python exceptions + */ +CREATE FUNCTION exception_index_invalid(text) RETURNS text + AS +'return args[1]' + LANGUAGE plpythonu; +/* check handling of nested exceptions + */ +CREATE FUNCTION exception_index_invalid_nested() RETURNS text + AS +'rv = plpy.execute("SELECT test5(''foo'')") +return rv[0]' + LANGUAGE plpythonu; +CREATE FUNCTION join_sequences(sequences) RETURNS text + AS +'if not args[0]["multipart"]: + return args[0]["sequence"] +q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % args[0]["pid"] +rv = plpy.execute(q) +seq = args[0]["sequence"] +for r in rv: + seq = seq + r["sequence"] +return seq +' + LANGUAGE plpythonu; +CREATE OR REPLACE FUNCTION read_file(text) RETURNS text AS ' + return open(args[0]).read() +' LANGUAGE plpythonu; +CREATE OR REPLACE FUNCTION write_file(text,text) RETURNS text AS ' + open(args[0],"w").write(args[1]) + return "Wrote to file: %s" % args[0] +' LANGUAGE plpythonu; +-- +-- Universal Newline Support +-- +CREATE OR REPLACE FUNCTION newline_lf() RETURNS integer AS +'x = 100\ny = 23\nreturn x + y\n' +LANGUAGE plpythonu; +CREATE OR REPLACE FUNCTION newline_cr() RETURNS integer AS +'x = 100\ry = 23\rreturn x + y\r' +LANGUAGE plpythonu; +CREATE OR REPLACE FUNCTION newline_crlf() RETURNS integer AS +'x = 100\r\ny = 23\r\nreturn x + y\r\n' +LANGUAGE plpythonu; diff --git a/src/pl/plpython/expected/plpython_populate.out b/src/pl/plpython/expected/plpython_populate.out new file mode 100644 index 00000000000..4db75b074f8 --- /dev/null +++ b/src/pl/plpython/expected/plpython_populate.out @@ -0,0 +1,22 @@ +INSERT INTO users (fname, lname, username) VALUES ('jane', 'doe', 'j_doe'); +INSERT INTO users (fname, lname, username) VALUES ('john', 'doe', 'johnd'); +INSERT INTO users (fname, lname, username) VALUES ('willem', 'doe', 'w_doe'); +INSERT INTO users (fname, lname, username) VALUES ('rick', 'smith', 'slash'); +-- multi table tests +-- +INSERT INTO taxonomy (name) VALUES ('HIV I') ; +INSERT INTO taxonomy (name) VALUES ('HIV II') ; +INSERT INTO taxonomy (name) VALUES ('HCV') ; +INSERT INTO entry (accession, txid) VALUES ('A00001', '1') ; +INSERT INTO entry (accession, txid) VALUES ('A00002', '1') ; +INSERT INTO entry (accession, txid) VALUES ('A00003', '1') ; +INSERT INTO entry (accession, txid) VALUES ('A00004', '2') ; +INSERT INTO entry (accession, txid) VALUES ('A00005', '2') ; +INSERT INTO entry (accession, txid) VALUES ('A00006', '3') ; +INSERT INTO sequences (sequence, eid, product, multipart) VALUES ('ABCDEF', 1, 'env', 'true') ; +INSERT INTO xsequences (sequence, pid) VALUES ('GHIJKL', 1) ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 2, 'env') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 3, 'env') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 4, 'gag') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 5, 'env') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 6, 'ns1') ; diff --git a/src/pl/plpython/expected/plpython_schema.out b/src/pl/plpython/expected/plpython_schema.out new file mode 100644 index 00000000000..e94e7bbcf88 --- /dev/null +++ b/src/pl/plpython/expected/plpython_schema.out @@ -0,0 +1,43 @@ +CREATE TABLE users ( + fname text not null, + lname text not null, + username text, + userid serial, + PRIMARY KEY(lname, fname) + ) ; +NOTICE: CREATE TABLE will create implicit sequence "users_userid_seq" for serial column "users.userid" +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users" +CREATE INDEX users_username_idx ON users(username); +CREATE INDEX users_fname_idx ON users(fname); +CREATE INDEX users_lname_idx ON users(lname); +CREATE INDEX users_userid_idx ON users(userid); +CREATE TABLE taxonomy ( + id serial primary key, + name text unique + ) ; +NOTICE: CREATE TABLE will create implicit sequence "taxonomy_id_seq" for serial column "taxonomy.id" +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "taxonomy_pkey" for table "taxonomy" +NOTICE: CREATE TABLE / UNIQUE will create implicit index "taxonomy_name_key" for table "taxonomy" +CREATE TABLE entry ( + accession text not null primary key, + eid serial unique, + txid int2 not null references taxonomy(id) + ) ; +NOTICE: CREATE TABLE will create implicit sequence "entry_eid_seq" for serial column "entry.eid" +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "entry_pkey" for table "entry" +NOTICE: CREATE TABLE / UNIQUE will create implicit index "entry_eid_key" for table "entry" +CREATE TABLE sequences ( + eid int4 not null references entry(eid), + pid serial primary key, + product text not null, + sequence text not null, + multipart bool default 'false' + ) ; +NOTICE: CREATE TABLE will create implicit sequence "sequences_pid_seq" for serial column "sequences.pid" +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "sequences_pkey" for table "sequences" +CREATE INDEX sequences_product_idx ON sequences(product) ; +CREATE TABLE xsequences ( + pid int4 not null references sequences(pid), + sequence text not null + ) ; +CREATE INDEX xsequences_pid_idx ON xsequences(pid) ; diff --git a/src/pl/plpython/expected/plpython_test.out b/src/pl/plpython/expected/plpython_test.out new file mode 100644 index 00000000000..08704cb571d --- /dev/null +++ b/src/pl/plpython/expected/plpython_test.out @@ -0,0 +1,184 @@ +-- first some tests of basic functionality +-- +-- better succeed +-- +select stupid(); + stupid +-------- + zarkon +(1 row) + +-- check static and global data +-- +SELECT static_test(); + static_test +------------- + 1 +(1 row) + +SELECT static_test(); + static_test +------------- + 2 +(1 row) + +SELECT global_test_one(); + global_test_one +-------------------------------------------------------- + SD: set by global_test_one, GD: set by global_test_one +(1 row) + +SELECT global_test_two(); + global_test_two +-------------------------------------------------------- + SD: set by global_test_two, GD: set by global_test_one +(1 row) + +-- import python modules +-- +SELECT import_fail(); +NOTICE: ('import socket failed -- No module named foosocket',) + import_fail +-------------------- + failed as expected +(1 row) + +SELECT import_succeed(); + import_succeed +------------------------ + succeeded, as expected +(1 row) + +-- test import and simple argument handling +-- +SELECT import_test_one('sha hash of this string'); + import_test_one +------------------------------------------ + a04e23cb9b1a09cd1051a04a7c571aae0f90346c +(1 row) + +-- test import and tuple argument handling +-- +select import_test_two(users) from users where fname = 'willem'; + import_test_two +------------------------------------------------------------------- + sha hash of willemdoe is 3cde6b574953b0ca937b4d76ebc40d534d910759 +(1 row) + +-- test multiple arguments +-- +select argument_test_one(users, fname, lname) from users where lname = 'doe' order by 1; + argument_test_one +----------------------------------------------------------------------- + jane doe => {fname: jane, lname: doe, userid: 1, username: j_doe} + john doe => {fname: john, lname: doe, userid: 2, username: johnd} + willem doe => {fname: willem, lname: doe, userid: 3, username: w_doe} +(3 rows) + +-- spi and nested calls +-- +select nested_call_one('pass this along'); + nested_call_one +----------------------------------------------------------------- + {'nested_call_two': "{'nested_call_three': 'pass this along'}"} +(1 row) + +select spi_prepared_plan_test_one('doe'); + spi_prepared_plan_test_one +---------------------------- + there are 3 does +(1 row) + +select spi_prepared_plan_test_one('smith'); + spi_prepared_plan_test_one +---------------------------- + there are 1 smiths +(1 row) + +select spi_prepared_plan_test_nested('smith'); + spi_prepared_plan_test_nested +------------------------------- + there are 1 smiths +(1 row) + +-- quick peek at the table +-- +SELECT * FROM users; + fname | lname | username | userid +--------+-------+----------+-------- + jane | doe | j_doe | 1 + john | doe | johnd | 2 + willem | doe | w_doe | 3 + rick | smith | slash | 4 +(4 rows) + +-- should fail +-- +UPDATE users SET fname = 'william' WHERE fname = 'willem'; +-- should modify william to willem and create username +-- +INSERT INTO users (fname, lname) VALUES ('william', 'smith'); +INSERT INTO users (fname, lname, username) VALUES ('charles', 'darwin', 'beagle'); +SELECT * FROM users; + fname | lname | username | userid +---------+--------+----------+-------- + jane | doe | j_doe | 1 + john | doe | johnd | 2 + willem | doe | w_doe | 3 + rick | smith | slash | 4 + willem | smith | w_smith | 5 + charles | darwin | beagle | 6 +(6 rows) + +SELECT join_sequences(sequences) FROM sequences; + join_sequences +---------------- + ABCDEFGHIJKL + ABCDEF + ABCDEF + ABCDEF + ABCDEF + ABCDEF +(6 rows) + +SELECT join_sequences(sequences) FROM sequences + WHERE join_sequences(sequences) ~* '^A'; + join_sequences +---------------- + ABCDEFGHIJKL + ABCDEF + ABCDEF + ABCDEF + ABCDEF + ABCDEF +(6 rows) + +SELECT join_sequences(sequences) FROM sequences + WHERE join_sequences(sequences) ~* '^B'; + join_sequences +---------------- +(0 rows) + +-- error in trigger +-- +-- +-- Check Universal Newline Support +-- +SELECT newline_lf(); + newline_lf +------------ + 123 +(1 row) + +SELECT newline_cr(); + newline_cr +------------ + 123 +(1 row) + +SELECT newline_crlf(); + newline_crlf +-------------- + 123 +(1 row) + diff --git a/src/pl/plpython/feature.expected b/src/pl/plpython/feature.expected deleted file mode 100644 index 90d161422c5..00000000000 --- a/src/pl/plpython/feature.expected +++ /dev/null @@ -1,157 +0,0 @@ -select stupid(); - stupid --------- - zarkon -(1 row) - -SELECT static_test(); - static_test -------------- - 1 -(1 row) - -SELECT static_test(); - static_test -------------- - 2 -(1 row) - -SELECT global_test_one(); - global_test_one --------------------------------------------------------- - SD: set by global_test_one, GD: set by global_test_one -(1 row) - -SELECT global_test_two(); - global_test_two --------------------------------------------------------- - SD: set by global_test_two, GD: set by global_test_one -(1 row) - -SELECT import_fail(); -NOTICE: ('import socket failed -- No module named foosocket',) - import_fail --------------------- - failed as expected -(1 row) - -SELECT import_succeed(); - import_succeed ------------------------- - succeeded, as expected -(1 row) - -SELECT import_test_one('sha hash of this string'); - import_test_one ------------------------------------------- - a04e23cb9b1a09cd1051a04a7c571aae0f90346c -(1 row) - -select import_test_two(users) from users where fname = 'willem'; - import_test_two -------------------------------------------------------------------- - sha hash of willemdoe is 3cde6b574953b0ca937b4d76ebc40d534d910759 -(1 row) - -select argument_test_one(users, fname, lname) from users where lname = 'doe' order by 1; - argument_test_one ------------------------------------------------------------------------ - jane doe => {fname: jane, lname: doe, userid: 1, username: j_doe} - john doe => {fname: john, lname: doe, userid: 2, username: johnd} - willem doe => {fname: willem, lname: doe, userid: 3, username: w_doe} -(3 rows) - -select nested_call_one('pass this along'); - nested_call_one ------------------------------------------------------------------ - {'nested_call_two': "{'nested_call_three': 'pass this along'}"} -(1 row) - -select spi_prepared_plan_test_one('doe'); - spi_prepared_plan_test_one ----------------------------- - there are 3 does -(1 row) - -select spi_prepared_plan_test_one('smith'); - spi_prepared_plan_test_one ----------------------------- - there are 1 smiths -(1 row) - -select spi_prepared_plan_test_nested('smith'); - spi_prepared_plan_test_nested -------------------------------- - there are 1 smiths -(1 row) - -SELECT * FROM users; - fname | lname | username | userid ---------+-------+----------+-------- - jane | doe | j_doe | 1 - john | doe | johnd | 2 - willem | doe | w_doe | 3 - rick | smith | slash | 4 -(4 rows) - -UPDATE users SET fname = 'william' WHERE fname = 'willem'; -INSERT INTO users (fname, lname) VALUES ('william', 'smith'); -INSERT INTO users (fname, lname, username) VALUES ('charles', 'darwin', 'beagle'); -SELECT * FROM users; - fname | lname | username | userid ----------+--------+----------+-------- - jane | doe | j_doe | 1 - john | doe | johnd | 2 - willem | doe | w_doe | 3 - rick | smith | slash | 4 - willem | smith | w_smith | 5 - charles | darwin | beagle | 6 -(6 rows) - -SELECT join_sequences(sequences) FROM sequences; - join_sequences ----------------- - ABCDEFGHIJKL - ABCDEF - ABCDEF - ABCDEF - ABCDEF - ABCDEF -(6 rows) - -SELECT join_sequences(sequences) FROM sequences - WHERE join_sequences(sequences) ~* '^A'; - join_sequences ----------------- - ABCDEFGHIJKL - ABCDEF - ABCDEF - ABCDEF - ABCDEF - ABCDEF -(6 rows) - -SELECT join_sequences(sequences) FROM sequences - WHERE join_sequences(sequences) ~* '^B'; - join_sequences ----------------- -(0 rows) - -SELECT newline_lf(); - newline_lf ------------- - 123 -(1 row) - -SELECT newline_cr(); - newline_cr ------------- - 123 -(1 row) - -SELECT newline_crlf(); - newline_crlf --------------- - 123 -(1 row) - diff --git a/src/pl/plpython/plpython_depopulate.sql b/src/pl/plpython/plpython_depopulate.sql deleted file mode 100644 index 857ceff795c..00000000000 --- a/src/pl/plpython/plpython_depopulate.sql +++ /dev/null @@ -1,2 +0,0 @@ - -DELETE FROM users ; diff --git a/src/pl/plpython/plpython_deschema.sql b/src/pl/plpython/plpython_deschema.sql deleted file mode 100644 index ad66226462a..00000000000 --- a/src/pl/plpython/plpython_deschema.sql +++ /dev/null @@ -1,17 +0,0 @@ -DROP INDEX xsequences_pid_idx ; -DROP TABLE xsequences ; -DROP INDEX sequences_product_idx ; -DROP TABLE sequences ; -DROP SEQUENCE sequences_pid_seq ; -DROP TABLE taxonomy ; -DROP SEQUENCE taxonomy_id_seq ; -DROP TABLE entry ; -DROP SEQUENCE entry_eid_seq ; -DROP INDEX logins_userid_idx ; -DROP TABLE logins; -DROP INDEX users_username_idx ; -DROP INDEX users_fname_idx ; -DROP INDEX users_lname_idx ; -DROP INDEX users_userid_idx ; -DROP TABLE users ; -DROP SEQUENCE users_userid_seq ; diff --git a/src/pl/plpython/plpython_drop.sql b/src/pl/plpython/plpython_drop.sql deleted file mode 100644 index 7dbb3b0a43e..00000000000 --- a/src/pl/plpython/plpython_drop.sql +++ /dev/null @@ -1,11 +0,0 @@ -DROP FUNCTION plglobals() ; -DROP FUNCTION plstatic() ; -DROP FUNCTION plfail() ; -DROP TRIGGER users_insert_trig on users ; -DROP FUNCTION users_insert() ; -DROP TRIGGER users_update_trig on users ; -DROP FUNCTION users_update() ; -DROP TRIGGER users_delete_trig on users ; -DROP FUNCTION users_delete() ; -DROP PROCEDURAL LANGUAGE plpythonu ; -DROP FUNCTION plpython_call_handler() ; diff --git a/src/pl/plpython/plpython_error.sql b/src/pl/plpython/plpython_error.sql deleted file mode 100644 index f1939eb8ffb..00000000000 --- a/src/pl/plpython/plpython_error.sql +++ /dev/null @@ -1,13 +0,0 @@ - --- test error handling, i forgot to restore Warn_restart in --- the trigger handler once. the errors and subsequent core dump were --- interesting. - -SELECT invalid_type_uncaught('rick'); -SELECT invalid_type_caught('rick'); -SELECT invalid_type_reraised('rick'); -SELECT valid_type('rick'); - --- Security sandbox tests -SELECT write_file('/tmp/plpython','Only trusted users should be able to do this!'); -SELECT read_file('/tmp/plpython'); diff --git a/src/pl/plpython/plpython_function.sql b/src/pl/plpython/plpython_function.sql deleted file mode 100644 index c849c3e5b97..00000000000 --- a/src/pl/plpython/plpython_function.sql +++ /dev/null @@ -1,324 +0,0 @@ - - -CREATE FUNCTION global_test_one() returns text - AS -'if not SD.has_key("global_test"): - SD["global_test"] = "set by global_test_one" -if not GD.has_key("global_test"): - GD["global_test"] = "set by global_test_one" -return "SD: " + SD["global_test"] + ", GD: " + GD["global_test"]' - LANGUAGE plpythonu; - -CREATE FUNCTION global_test_two() returns text - AS -'if not SD.has_key("global_test"): - SD["global_test"] = "set by global_test_two" -if not GD.has_key("global_test"): - GD["global_test"] = "set by global_test_two" -return "SD: " + SD["global_test"] + ", GD: " + GD["global_test"]' - LANGUAGE plpythonu; - - -CREATE FUNCTION static_test() returns int4 - AS -'if SD.has_key("call"): - SD["call"] = SD["call"] + 1 -else: - SD["call"] = 1 -return SD["call"] -' - LANGUAGE plpythonu; - --- import python modules - -CREATE FUNCTION import_fail() returns text - AS -'try: - import foosocket -except Exception, ex: - plpy.notice("import socket failed -- %s" % str(ex)) - return "failed as expected" -return "succeeded, that wasn''t supposed to happen"' - LANGUAGE plpythonu; - - -CREATE FUNCTION import_succeed() returns text - AS -'try: - import array - import bisect - import calendar - import cmath - import errno - import math - import md5 - import operator - import random - import re - import sha - import string - import time - import whrandom -except Exception, ex: - plpy.notice("import failed -- %s" % str(ex)) - return "failed, that wasn''t supposed to happen" -return "succeeded, as expected"' - LANGUAGE plpythonu; - -CREATE FUNCTION import_test_one(text) RETURNS text - AS -'import sha -digest = sha.new(args[0]) -return digest.hexdigest()' - LANGUAGE plpythonu; - -CREATE FUNCTION import_test_two(users) RETURNS text - AS -'import sha -plain = args[0]["fname"] + args[0]["lname"] -digest = sha.new(plain); -return "sha hash of " + plain + " is " + digest.hexdigest()' - LANGUAGE plpythonu; - -CREATE FUNCTION argument_test_one(users, text, text) RETURNS text - AS -'keys = args[0].keys() -keys.sort() -out = [] -for key in keys: - out.append("%s: %s" % (key, args[0][key])) -words = args[1] + " " + args[2] + " => {" + ", ".join(out) + "}" -return words' - LANGUAGE plpythonu; - - --- these triggers are dedicated to HPHC of RI who --- decided that my kid's name was william not willem, and --- vigorously resisted all efforts at correction. they have --- since gone bankrupt... - -CREATE FUNCTION users_insert() returns trigger - AS -'if TD["new"]["fname"] == None or TD["new"]["lname"] == None: - return "SKIP" -if TD["new"]["username"] == None: - TD["new"]["username"] = TD["new"]["fname"][:1] + "_" + TD["new"]["lname"] - rv = "MODIFY" -else: - rv = None -if TD["new"]["fname"] == "william": - TD["new"]["fname"] = TD["args"][0] - rv = "MODIFY" -return rv' - LANGUAGE plpythonu; - - -CREATE FUNCTION users_update() returns trigger - AS -'if TD["event"] == "UPDATE": - if TD["old"]["fname"] != TD["new"]["fname"] and TD["old"]["fname"] == TD["args"][0]: - return "SKIP" -return None' - LANGUAGE plpythonu; - - -CREATE FUNCTION users_delete() RETURNS trigger - AS -'if TD["old"]["fname"] == TD["args"][0]: - return "SKIP" -return None' - LANGUAGE plpythonu; - - -CREATE TRIGGER users_insert_trig BEFORE INSERT ON users FOR EACH ROW - EXECUTE PROCEDURE users_insert ('willem'); - -CREATE TRIGGER users_update_trig BEFORE UPDATE ON users FOR EACH ROW - EXECUTE PROCEDURE users_update ('willem'); - -CREATE TRIGGER users_delete_trig BEFORE DELETE ON users FOR EACH ROW - EXECUTE PROCEDURE users_delete ('willem'); - - --- nested calls --- - -CREATE FUNCTION nested_call_one(text) RETURNS text - AS -'q = "SELECT nested_call_two(''%s'')" % args[0] -r = plpy.execute(q) -return r[0]' - LANGUAGE plpythonu ; - -CREATE FUNCTION nested_call_two(text) RETURNS text - AS -'q = "SELECT nested_call_three(''%s'')" % args[0] -r = plpy.execute(q) -return r[0]' - LANGUAGE plpythonu ; - -CREATE FUNCTION nested_call_three(text) RETURNS text - AS -'return args[0]' - LANGUAGE plpythonu ; - --- some spi stuff - -CREATE FUNCTION spi_prepared_plan_test_one(text) RETURNS text - AS -'if not SD.has_key("myplan"): - q = "SELECT count(*) FROM users WHERE lname = $1" - SD["myplan"] = plpy.prepare(q, [ "text" ]) -try: - rv = plpy.execute(SD["myplan"], [args[0]]) - return "there are " + str(rv[0]["count"]) + " " + str(args[0]) + "s" -except Exception, ex: - plpy.error(str(ex)) -return None -' - LANGUAGE plpythonu; - -CREATE FUNCTION spi_prepared_plan_test_nested(text) RETURNS text - AS -'if not SD.has_key("myplan"): - q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % args[0] - SD["myplan"] = plpy.prepare(q) -try: - rv = plpy.execute(SD["myplan"]) - if len(rv): - return rv[0]["count"] -except Exception, ex: - plpy.error(str(ex)) -return None -' - LANGUAGE plpythonu; - - -/* really stupid function just to get the module loaded -*/ -CREATE FUNCTION stupid() RETURNS text AS 'return "zarkon"' LANGUAGE plpythonu; - -/* a typo -*/ -CREATE FUNCTION invalid_type_uncaught(text) RETURNS text - AS -'if not SD.has_key("plan"): - q = "SELECT fname FROM users WHERE lname = $1" - SD["plan"] = plpy.prepare(q, [ "test" ]) -rv = plpy.execute(SD["plan"], [ args[0] ]) -if len(rv): - return rv[0]["fname"] -return None -' - LANGUAGE plpythonu; - -/* for what it's worth catch the exception generated by - * the typo, and return None - */ -CREATE FUNCTION invalid_type_caught(text) RETURNS text - AS -'if not SD.has_key("plan"): - q = "SELECT fname FROM users WHERE lname = $1" - try: - SD["plan"] = plpy.prepare(q, [ "test" ]) - except plpy.SPIError, ex: - plpy.notice(str(ex)) - return None -rv = plpy.execute(SD["plan"], [ args[0] ]) -if len(rv): - return rv[0]["fname"] -return None -' - LANGUAGE plpythonu; - -/* for what it's worth catch the exception generated by - * the typo, and reraise it as a plain error - */ -CREATE FUNCTION invalid_type_reraised(text) RETURNS text - AS -'if not SD.has_key("plan"): - q = "SELECT fname FROM users WHERE lname = $1" - try: - SD["plan"] = plpy.prepare(q, [ "test" ]) - except plpy.SPIError, ex: - plpy.error(str(ex)) -rv = plpy.execute(SD["plan"], [ args[0] ]) -if len(rv): - return rv[0]["fname"] -return None -' - LANGUAGE plpythonu; - - -/* no typo no messing about -*/ -CREATE FUNCTION valid_type(text) RETURNS text - AS -'if not SD.has_key("plan"): - SD["plan"] = plpy.prepare("SELECT fname FROM users WHERE lname = $1", [ "text" ]) -rv = plpy.execute(SD["plan"], [ args[0] ]) -if len(rv): - return rv[0]["fname"] -return None -' - LANGUAGE plpythonu; -/* Flat out syntax error -*/ -CREATE FUNCTION sql_syntax_error() RETURNS text - AS -'plpy.execute("syntax error")' - LANGUAGE plpythonu; - -/* check the handling of uncaught python exceptions - */ -CREATE FUNCTION exception_index_invalid(text) RETURNS text - AS -'return args[1]' - LANGUAGE plpythonu; - -/* check handling of nested exceptions - */ -CREATE FUNCTION exception_index_invalid_nested() RETURNS text - AS -'rv = plpy.execute("SELECT test5(''foo'')") -return rv[0]' - LANGUAGE plpythonu; - - -CREATE FUNCTION join_sequences(sequences) RETURNS text - AS -'if not args[0]["multipart"]: - return args[0]["sequence"] -q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % args[0]["pid"] -rv = plpy.execute(q) -seq = args[0]["sequence"] -for r in rv: - seq = seq + r["sequence"] -return seq -' - LANGUAGE plpythonu; - -CREATE OR REPLACE FUNCTION read_file(text) RETURNS text AS ' - return open(args[0]).read() -' LANGUAGE plpythonu; - -CREATE OR REPLACE FUNCTION write_file(text,text) RETURNS text AS ' - open(args[0],"w").write(args[1]) - return "Wrote to file: %s" % args[0] -' LANGUAGE plpythonu; - --- --- Universal Newline Support --- - -CREATE OR REPLACE FUNCTION newline_lf() RETURNS integer AS -'x = 100\ny = 23\nreturn x + y\n' -LANGUAGE plpythonu; - -CREATE OR REPLACE FUNCTION newline_cr() RETURNS integer AS -'x = 100\ry = 23\rreturn x + y\r' -LANGUAGE plpythonu; - -CREATE OR REPLACE FUNCTION newline_crlf() RETURNS integer AS -'x = 100\r\ny = 23\r\nreturn x + y\r\n' -LANGUAGE plpythonu; diff --git a/src/pl/plpython/plpython_populate.sql b/src/pl/plpython/plpython_populate.sql deleted file mode 100644 index a1963e76eea..00000000000 --- a/src/pl/plpython/plpython_populate.sql +++ /dev/null @@ -1,28 +0,0 @@ - -INSERT INTO users (fname, lname, username) VALUES ('jane', 'doe', 'j_doe'); -INSERT INTO users (fname, lname, username) VALUES ('john', 'doe', 'johnd'); -INSERT INTO users (fname, lname, username) VALUES ('willem', 'doe', 'w_doe'); -INSERT INTO users (fname, lname, username) VALUES ('rick', 'smith', 'slash'); - - --- multi table tests --- - -INSERT INTO taxonomy (name) VALUES ('HIV I') ; -INSERT INTO taxonomy (name) VALUES ('HIV II') ; -INSERT INTO taxonomy (name) VALUES ('HCV') ; - -INSERT INTO entry (accession, txid) VALUES ('A00001', '1') ; -INSERT INTO entry (accession, txid) VALUES ('A00002', '1') ; -INSERT INTO entry (accession, txid) VALUES ('A00003', '1') ; -INSERT INTO entry (accession, txid) VALUES ('A00004', '2') ; -INSERT INTO entry (accession, txid) VALUES ('A00005', '2') ; -INSERT INTO entry (accession, txid) VALUES ('A00006', '3') ; - -INSERT INTO sequences (sequence, eid, product, multipart) VALUES ('ABCDEF', 1, 'env', 'true') ; -INSERT INTO xsequences (sequence, pid) VALUES ('GHIJKL', 1) ; -INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 2, 'env') ; -INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 3, 'env') ; -INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 4, 'gag') ; -INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 5, 'env') ; -INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 6, 'ns1') ; \ No newline at end of file diff --git a/src/pl/plpython/plpython_schema.sql b/src/pl/plpython/plpython_schema.sql deleted file mode 100644 index 2f8766431fa..00000000000 --- a/src/pl/plpython/plpython_schema.sql +++ /dev/null @@ -1,42 +0,0 @@ - -CREATE TABLE users ( - fname text not null, - lname text not null, - username text, - userid serial, - PRIMARY KEY(lname, fname) - ) ; - -CREATE INDEX users_username_idx ON users(username); -CREATE INDEX users_fname_idx ON users(fname); -CREATE INDEX users_lname_idx ON users(lname); -CREATE INDEX users_userid_idx ON users(userid); - - -CREATE TABLE taxonomy ( - id serial primary key, - name text unique - ) ; - -CREATE TABLE entry ( - accession text not null primary key, - eid serial unique, - txid int2 not null references taxonomy(id) - ) ; - -CREATE TABLE sequences ( - eid int4 not null references entry(eid), - pid serial primary key, - product text not null, - sequence text not null, - multipart bool default 'false' - ) ; -CREATE INDEX sequences_product_idx ON sequences(product) ; - -CREATE TABLE xsequences ( - pid int4 not null references sequences(pid), - sequence text not null - ) ; -CREATE INDEX xsequences_pid_idx ON xsequences(pid) ; - - diff --git a/src/pl/plpython/plpython_setof.sql b/src/pl/plpython/plpython_setof.sql deleted file mode 100644 index f0d273f9486..00000000000 --- a/src/pl/plpython/plpython_setof.sql +++ /dev/null @@ -1,11 +0,0 @@ - -CREATE FUNCTION test_setof() returns setof text - AS -'if GD.has_key("calls"): - GD["calls"] = GD["calls"] + 1 - if GD["calls"] > 2: - return None -else: - GD["calls"] = 1 -return str(GD["calls"])' - LANGUAGE plpythonu; diff --git a/src/pl/plpython/plpython_test.sql b/src/pl/plpython/plpython_test.sql deleted file mode 100644 index 17d6b2e7922..00000000000 --- a/src/pl/plpython/plpython_test.sql +++ /dev/null @@ -1,70 +0,0 @@ --- first some tests of basic functionality --- --- better succeed --- -select stupid(); - --- check static and global data --- -SELECT static_test(); -SELECT static_test(); -SELECT global_test_one(); -SELECT global_test_two(); - --- import python modules --- -SELECT import_fail(); -SELECT import_succeed(); - --- test import and simple argument handling --- -SELECT import_test_one('sha hash of this string'); - --- test import and tuple argument handling --- -select import_test_two(users) from users where fname = 'willem'; - --- test multiple arguments --- -select argument_test_one(users, fname, lname) from users where lname = 'doe' order by 1; - - --- spi and nested calls --- -select nested_call_one('pass this along'); -select spi_prepared_plan_test_one('doe'); -select spi_prepared_plan_test_one('smith'); -select spi_prepared_plan_test_nested('smith'); - --- quick peek at the table --- -SELECT * FROM users; - --- should fail --- -UPDATE users SET fname = 'william' WHERE fname = 'willem'; - --- should modify william to willem and create username --- -INSERT INTO users (fname, lname) VALUES ('william', 'smith'); -INSERT INTO users (fname, lname, username) VALUES ('charles', 'darwin', 'beagle'); - -SELECT * FROM users; - - -SELECT join_sequences(sequences) FROM sequences; -SELECT join_sequences(sequences) FROM sequences - WHERE join_sequences(sequences) ~* '^A'; -SELECT join_sequences(sequences) FROM sequences - WHERE join_sequences(sequences) ~* '^B'; - --- error in trigger --- - --- --- Check Universal Newline Support --- - -SELECT newline_lf(); -SELECT newline_cr(); -SELECT newline_crlf(); diff --git a/src/pl/plpython/sql/plpython_drop.sql b/src/pl/plpython/sql/plpython_drop.sql new file mode 100644 index 00000000000..319d5e0925d --- /dev/null +++ b/src/pl/plpython/sql/plpython_drop.sql @@ -0,0 +1,6 @@ +-- +-- For paranoia's sake, don't leave an untrusted language sitting around +-- +SET client_min_messages = WARNING; + +DROP PROCEDURAL LANGUAGE plpythonu CASCADE; diff --git a/src/pl/plpython/sql/plpython_error.sql b/src/pl/plpython/sql/plpython_error.sql new file mode 100644 index 00000000000..f1939eb8ffb --- /dev/null +++ b/src/pl/plpython/sql/plpython_error.sql @@ -0,0 +1,13 @@ + +-- test error handling, i forgot to restore Warn_restart in +-- the trigger handler once. the errors and subsequent core dump were +-- interesting. + +SELECT invalid_type_uncaught('rick'); +SELECT invalid_type_caught('rick'); +SELECT invalid_type_reraised('rick'); +SELECT valid_type('rick'); + +-- Security sandbox tests +SELECT write_file('/tmp/plpython','Only trusted users should be able to do this!'); +SELECT read_file('/tmp/plpython'); diff --git a/src/pl/plpython/sql/plpython_function.sql b/src/pl/plpython/sql/plpython_function.sql new file mode 100644 index 00000000000..c849c3e5b97 --- /dev/null +++ b/src/pl/plpython/sql/plpython_function.sql @@ -0,0 +1,324 @@ + + +CREATE FUNCTION global_test_one() returns text + AS +'if not SD.has_key("global_test"): + SD["global_test"] = "set by global_test_one" +if not GD.has_key("global_test"): + GD["global_test"] = "set by global_test_one" +return "SD: " + SD["global_test"] + ", GD: " + GD["global_test"]' + LANGUAGE plpythonu; + +CREATE FUNCTION global_test_two() returns text + AS +'if not SD.has_key("global_test"): + SD["global_test"] = "set by global_test_two" +if not GD.has_key("global_test"): + GD["global_test"] = "set by global_test_two" +return "SD: " + SD["global_test"] + ", GD: " + GD["global_test"]' + LANGUAGE plpythonu; + + +CREATE FUNCTION static_test() returns int4 + AS +'if SD.has_key("call"): + SD["call"] = SD["call"] + 1 +else: + SD["call"] = 1 +return SD["call"] +' + LANGUAGE plpythonu; + +-- import python modules + +CREATE FUNCTION import_fail() returns text + AS +'try: + import foosocket +except Exception, ex: + plpy.notice("import socket failed -- %s" % str(ex)) + return "failed as expected" +return "succeeded, that wasn''t supposed to happen"' + LANGUAGE plpythonu; + + +CREATE FUNCTION import_succeed() returns text + AS +'try: + import array + import bisect + import calendar + import cmath + import errno + import math + import md5 + import operator + import random + import re + import sha + import string + import time + import whrandom +except Exception, ex: + plpy.notice("import failed -- %s" % str(ex)) + return "failed, that wasn''t supposed to happen" +return "succeeded, as expected"' + LANGUAGE plpythonu; + +CREATE FUNCTION import_test_one(text) RETURNS text + AS +'import sha +digest = sha.new(args[0]) +return digest.hexdigest()' + LANGUAGE plpythonu; + +CREATE FUNCTION import_test_two(users) RETURNS text + AS +'import sha +plain = args[0]["fname"] + args[0]["lname"] +digest = sha.new(plain); +return "sha hash of " + plain + " is " + digest.hexdigest()' + LANGUAGE plpythonu; + +CREATE FUNCTION argument_test_one(users, text, text) RETURNS text + AS +'keys = args[0].keys() +keys.sort() +out = [] +for key in keys: + out.append("%s: %s" % (key, args[0][key])) +words = args[1] + " " + args[2] + " => {" + ", ".join(out) + "}" +return words' + LANGUAGE plpythonu; + + +-- these triggers are dedicated to HPHC of RI who +-- decided that my kid's name was william not willem, and +-- vigorously resisted all efforts at correction. they have +-- since gone bankrupt... + +CREATE FUNCTION users_insert() returns trigger + AS +'if TD["new"]["fname"] == None or TD["new"]["lname"] == None: + return "SKIP" +if TD["new"]["username"] == None: + TD["new"]["username"] = TD["new"]["fname"][:1] + "_" + TD["new"]["lname"] + rv = "MODIFY" +else: + rv = None +if TD["new"]["fname"] == "william": + TD["new"]["fname"] = TD["args"][0] + rv = "MODIFY" +return rv' + LANGUAGE plpythonu; + + +CREATE FUNCTION users_update() returns trigger + AS +'if TD["event"] == "UPDATE": + if TD["old"]["fname"] != TD["new"]["fname"] and TD["old"]["fname"] == TD["args"][0]: + return "SKIP" +return None' + LANGUAGE plpythonu; + + +CREATE FUNCTION users_delete() RETURNS trigger + AS +'if TD["old"]["fname"] == TD["args"][0]: + return "SKIP" +return None' + LANGUAGE plpythonu; + + +CREATE TRIGGER users_insert_trig BEFORE INSERT ON users FOR EACH ROW + EXECUTE PROCEDURE users_insert ('willem'); + +CREATE TRIGGER users_update_trig BEFORE UPDATE ON users FOR EACH ROW + EXECUTE PROCEDURE users_update ('willem'); + +CREATE TRIGGER users_delete_trig BEFORE DELETE ON users FOR EACH ROW + EXECUTE PROCEDURE users_delete ('willem'); + + +-- nested calls +-- + +CREATE FUNCTION nested_call_one(text) RETURNS text + AS +'q = "SELECT nested_call_two(''%s'')" % args[0] +r = plpy.execute(q) +return r[0]' + LANGUAGE plpythonu ; + +CREATE FUNCTION nested_call_two(text) RETURNS text + AS +'q = "SELECT nested_call_three(''%s'')" % args[0] +r = plpy.execute(q) +return r[0]' + LANGUAGE plpythonu ; + +CREATE FUNCTION nested_call_three(text) RETURNS text + AS +'return args[0]' + LANGUAGE plpythonu ; + +-- some spi stuff + +CREATE FUNCTION spi_prepared_plan_test_one(text) RETURNS text + AS +'if not SD.has_key("myplan"): + q = "SELECT count(*) FROM users WHERE lname = $1" + SD["myplan"] = plpy.prepare(q, [ "text" ]) +try: + rv = plpy.execute(SD["myplan"], [args[0]]) + return "there are " + str(rv[0]["count"]) + " " + str(args[0]) + "s" +except Exception, ex: + plpy.error(str(ex)) +return None +' + LANGUAGE plpythonu; + +CREATE FUNCTION spi_prepared_plan_test_nested(text) RETURNS text + AS +'if not SD.has_key("myplan"): + q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % args[0] + SD["myplan"] = plpy.prepare(q) +try: + rv = plpy.execute(SD["myplan"]) + if len(rv): + return rv[0]["count"] +except Exception, ex: + plpy.error(str(ex)) +return None +' + LANGUAGE plpythonu; + + +/* really stupid function just to get the module loaded +*/ +CREATE FUNCTION stupid() RETURNS text AS 'return "zarkon"' LANGUAGE plpythonu; + +/* a typo +*/ +CREATE FUNCTION invalid_type_uncaught(text) RETURNS text + AS +'if not SD.has_key("plan"): + q = "SELECT fname FROM users WHERE lname = $1" + SD["plan"] = plpy.prepare(q, [ "test" ]) +rv = plpy.execute(SD["plan"], [ args[0] ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpythonu; + +/* for what it's worth catch the exception generated by + * the typo, and return None + */ +CREATE FUNCTION invalid_type_caught(text) RETURNS text + AS +'if not SD.has_key("plan"): + q = "SELECT fname FROM users WHERE lname = $1" + try: + SD["plan"] = plpy.prepare(q, [ "test" ]) + except plpy.SPIError, ex: + plpy.notice(str(ex)) + return None +rv = plpy.execute(SD["plan"], [ args[0] ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpythonu; + +/* for what it's worth catch the exception generated by + * the typo, and reraise it as a plain error + */ +CREATE FUNCTION invalid_type_reraised(text) RETURNS text + AS +'if not SD.has_key("plan"): + q = "SELECT fname FROM users WHERE lname = $1" + try: + SD["plan"] = plpy.prepare(q, [ "test" ]) + except plpy.SPIError, ex: + plpy.error(str(ex)) +rv = plpy.execute(SD["plan"], [ args[0] ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpythonu; + + +/* no typo no messing about +*/ +CREATE FUNCTION valid_type(text) RETURNS text + AS +'if not SD.has_key("plan"): + SD["plan"] = plpy.prepare("SELECT fname FROM users WHERE lname = $1", [ "text" ]) +rv = plpy.execute(SD["plan"], [ args[0] ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpythonu; +/* Flat out syntax error +*/ +CREATE FUNCTION sql_syntax_error() RETURNS text + AS +'plpy.execute("syntax error")' + LANGUAGE plpythonu; + +/* check the handling of uncaught python exceptions + */ +CREATE FUNCTION exception_index_invalid(text) RETURNS text + AS +'return args[1]' + LANGUAGE plpythonu; + +/* check handling of nested exceptions + */ +CREATE FUNCTION exception_index_invalid_nested() RETURNS text + AS +'rv = plpy.execute("SELECT test5(''foo'')") +return rv[0]' + LANGUAGE plpythonu; + + +CREATE FUNCTION join_sequences(sequences) RETURNS text + AS +'if not args[0]["multipart"]: + return args[0]["sequence"] +q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % args[0]["pid"] +rv = plpy.execute(q) +seq = args[0]["sequence"] +for r in rv: + seq = seq + r["sequence"] +return seq +' + LANGUAGE plpythonu; + +CREATE OR REPLACE FUNCTION read_file(text) RETURNS text AS ' + return open(args[0]).read() +' LANGUAGE plpythonu; + +CREATE OR REPLACE FUNCTION write_file(text,text) RETURNS text AS ' + open(args[0],"w").write(args[1]) + return "Wrote to file: %s" % args[0] +' LANGUAGE plpythonu; + +-- +-- Universal Newline Support +-- + +CREATE OR REPLACE FUNCTION newline_lf() RETURNS integer AS +'x = 100\ny = 23\nreturn x + y\n' +LANGUAGE plpythonu; + +CREATE OR REPLACE FUNCTION newline_cr() RETURNS integer AS +'x = 100\ry = 23\rreturn x + y\r' +LANGUAGE plpythonu; + +CREATE OR REPLACE FUNCTION newline_crlf() RETURNS integer AS +'x = 100\r\ny = 23\r\nreturn x + y\r\n' +LANGUAGE plpythonu; diff --git a/src/pl/plpython/sql/plpython_populate.sql b/src/pl/plpython/sql/plpython_populate.sql new file mode 100644 index 00000000000..a1963e76eea --- /dev/null +++ b/src/pl/plpython/sql/plpython_populate.sql @@ -0,0 +1,28 @@ + +INSERT INTO users (fname, lname, username) VALUES ('jane', 'doe', 'j_doe'); +INSERT INTO users (fname, lname, username) VALUES ('john', 'doe', 'johnd'); +INSERT INTO users (fname, lname, username) VALUES ('willem', 'doe', 'w_doe'); +INSERT INTO users (fname, lname, username) VALUES ('rick', 'smith', 'slash'); + + +-- multi table tests +-- + +INSERT INTO taxonomy (name) VALUES ('HIV I') ; +INSERT INTO taxonomy (name) VALUES ('HIV II') ; +INSERT INTO taxonomy (name) VALUES ('HCV') ; + +INSERT INTO entry (accession, txid) VALUES ('A00001', '1') ; +INSERT INTO entry (accession, txid) VALUES ('A00002', '1') ; +INSERT INTO entry (accession, txid) VALUES ('A00003', '1') ; +INSERT INTO entry (accession, txid) VALUES ('A00004', '2') ; +INSERT INTO entry (accession, txid) VALUES ('A00005', '2') ; +INSERT INTO entry (accession, txid) VALUES ('A00006', '3') ; + +INSERT INTO sequences (sequence, eid, product, multipart) VALUES ('ABCDEF', 1, 'env', 'true') ; +INSERT INTO xsequences (sequence, pid) VALUES ('GHIJKL', 1) ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 2, 'env') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 3, 'env') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 4, 'gag') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 5, 'env') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 6, 'ns1') ; \ No newline at end of file diff --git a/src/pl/plpython/sql/plpython_schema.sql b/src/pl/plpython/sql/plpython_schema.sql new file mode 100644 index 00000000000..2f8766431fa --- /dev/null +++ b/src/pl/plpython/sql/plpython_schema.sql @@ -0,0 +1,42 @@ + +CREATE TABLE users ( + fname text not null, + lname text not null, + username text, + userid serial, + PRIMARY KEY(lname, fname) + ) ; + +CREATE INDEX users_username_idx ON users(username); +CREATE INDEX users_fname_idx ON users(fname); +CREATE INDEX users_lname_idx ON users(lname); +CREATE INDEX users_userid_idx ON users(userid); + + +CREATE TABLE taxonomy ( + id serial primary key, + name text unique + ) ; + +CREATE TABLE entry ( + accession text not null primary key, + eid serial unique, + txid int2 not null references taxonomy(id) + ) ; + +CREATE TABLE sequences ( + eid int4 not null references entry(eid), + pid serial primary key, + product text not null, + sequence text not null, + multipart bool default 'false' + ) ; +CREATE INDEX sequences_product_idx ON sequences(product) ; + +CREATE TABLE xsequences ( + pid int4 not null references sequences(pid), + sequence text not null + ) ; +CREATE INDEX xsequences_pid_idx ON xsequences(pid) ; + + diff --git a/src/pl/plpython/sql/plpython_test.sql b/src/pl/plpython/sql/plpython_test.sql new file mode 100644 index 00000000000..17d6b2e7922 --- /dev/null +++ b/src/pl/plpython/sql/plpython_test.sql @@ -0,0 +1,70 @@ +-- first some tests of basic functionality +-- +-- better succeed +-- +select stupid(); + +-- check static and global data +-- +SELECT static_test(); +SELECT static_test(); +SELECT global_test_one(); +SELECT global_test_two(); + +-- import python modules +-- +SELECT import_fail(); +SELECT import_succeed(); + +-- test import and simple argument handling +-- +SELECT import_test_one('sha hash of this string'); + +-- test import and tuple argument handling +-- +select import_test_two(users) from users where fname = 'willem'; + +-- test multiple arguments +-- +select argument_test_one(users, fname, lname) from users where lname = 'doe' order by 1; + + +-- spi and nested calls +-- +select nested_call_one('pass this along'); +select spi_prepared_plan_test_one('doe'); +select spi_prepared_plan_test_one('smith'); +select spi_prepared_plan_test_nested('smith'); + +-- quick peek at the table +-- +SELECT * FROM users; + +-- should fail +-- +UPDATE users SET fname = 'william' WHERE fname = 'willem'; + +-- should modify william to willem and create username +-- +INSERT INTO users (fname, lname) VALUES ('william', 'smith'); +INSERT INTO users (fname, lname, username) VALUES ('charles', 'darwin', 'beagle'); + +SELECT * FROM users; + + +SELECT join_sequences(sequences) FROM sequences; +SELECT join_sequences(sequences) FROM sequences + WHERE join_sequences(sequences) ~* '^A'; +SELECT join_sequences(sequences) FROM sequences + WHERE join_sequences(sequences) ~* '^B'; + +-- error in trigger +-- + +-- +-- Check Universal Newline Support +-- + +SELECT newline_lf(); +SELECT newline_cr(); +SELECT newline_crlf(); diff --git a/src/pl/plpython/test.sh b/src/pl/plpython/test.sh deleted file mode 100755 index 9ed3c07662a..00000000000 --- a/src/pl/plpython/test.sh +++ /dev/null @@ -1,58 +0,0 @@ -#!/bin/sh - -DBNAME=pltest - -echo -n "*** Destroy $DBNAME." -dropdb $DBNAME > test.log 2>&1 -# drop failure is ok... -echo " Done. ***" - -sleep 1 - -echo -n "*** Create $DBNAME." -if createdb $DBNAME >> test.log 2>&1 ; then - echo " Done. ***" -else - echo " Failed! See test.log. ***" - exit 1 -fi - -echo -n "*** Create plpython." -if createlang plpythonu $DBNAME >> test.log 2>&1 ; then - echo " Done. ***" -else - echo " Failed! See test.log. ***" - exit 1 -fi - -echo -n "*** Create tables" -psql -q $DBNAME < plpython_schema.sql >> test.log 2>&1 -echo -n ", data" -psql -q $DBNAME < plpython_populate.sql >> test.log 2>&1 -echo -n ", and functions and triggers." -psql -q $DBNAME < plpython_function.sql >> test.log 2>&1 -echo " Done. ***" - -echo -n "*** Running feature tests." -psql -q -e $DBNAME < plpython_test.sql > feature.output 2>&1 -echo " Done. ***" - -echo -n "*** Running error handling tests." -psql -q -e $DBNAME < plpython_error.sql > error.output 2>&1 -echo " Done. ***" - -echo -n "*** Checking the results of the feature tests." -if diff -c feature.expected feature.output > feature.diff 2>&1 ; then - echo -n " passed!" -else - echo -n " failed! Please examine feature.diff." -fi -echo " Done. ***" - -echo -n "*** Checking the results of the error handling tests." -if diff -c error.expected error.output > error.diff 2>&1 ; then - echo -n " passed!" -else - echo -n " failed! Please examine error.diff." -fi -echo " Done. ***" diff --git a/src/pl/tcl/Makefile b/src/pl/tcl/Makefile index 043e399f4d6..ff65b0e020e 100644 --- a/src/pl/tcl/Makefile +++ b/src/pl/tcl/Makefile @@ -2,7 +2,7 @@ # # Makefile for the pltcl shared object # -# $PostgreSQL: pgsql/src/pl/tcl/Makefile,v 1.44 2004/12/16 20:41:01 tgl Exp $ +# $PostgreSQL: pgsql/src/pl/tcl/Makefile,v 1.45 2005/05/14 17:55:22 tgl Exp $ # #------------------------------------------------------------------------- @@ -40,6 +40,8 @@ SO_MAJOR_VERSION = 2 SO_MINOR_VERSION = 0 OBJS = pltcl.o +REGRESS = pltcl_setup pltcl_queries + include $(top_srcdir)/src/Makefile.shlib ifeq ($(TCL_SHARED_BUILD), 1) @@ -65,6 +67,13 @@ uninstall: rm -f $(DESTDIR)$(pkglibdir)/$(NAME)$(DLSUFFIX) $(MAKE) -C modules $@ +installcheck: submake + $(SHELL) $(top_builddir)/src/test/regress/pg_regress --load-language=pltcl $(REGRESS) + +.PHONY: submake +submake: + $(MAKE) -C $(top_builddir)/src/test/regress pg_regress + else # TCL_SHARED_BUILD = 0 # Provide dummy targets for the case where we can't build the shared library. @@ -77,4 +86,6 @@ endif # TCL_SHARED_BUILD = 0 clean distclean maintainer-clean: clean-lib rm -f $(OBJS) + rm -rf results + rm -f regression.diffs regression.out $(MAKE) -C modules $@ diff --git a/src/pl/tcl/expected/pltcl_queries.out b/src/pl/tcl/expected/pltcl_queries.out new file mode 100644 index 00000000000..600b15821bc --- /dev/null +++ b/src/pl/tcl/expected/pltcl_queries.out @@ -0,0 +1,185 @@ +-- suppress CONTEXT so that function OIDs aren't in output +\set VERBOSITY terse +insert into T_pkey1 values (1, 'key1-1', 'test key'); +insert into T_pkey1 values (1, 'key1-2', 'test key'); +insert into T_pkey1 values (1, 'key1-3', 'test key'); +insert into T_pkey1 values (2, 'key2-1', 'test key'); +insert into T_pkey1 values (2, 'key2-2', 'test key'); +insert into T_pkey1 values (2, 'key2-3', 'test key'); +insert into T_pkey2 values (1, 'key1-1', 'test key'); +insert into T_pkey2 values (1, 'key1-2', 'test key'); +insert into T_pkey2 values (1, 'key1-3', 'test key'); +insert into T_pkey2 values (2, 'key2-1', 'test key'); +insert into T_pkey2 values (2, 'key2-2', 'test key'); +insert into T_pkey2 values (2, 'key2-3', 'test key'); +select * from T_pkey1; + key1 | key2 | txt +------+----------------------+------------------------------------------ + 1 | key1-1 | test key + 1 | key1-2 | test key + 1 | key1-3 | test key + 2 | key2-1 | test key + 2 | key2-2 | test key + 2 | key2-3 | test key +(6 rows) + +-- key2 in T_pkey2 should have upper case only +select * from T_pkey2; + key1 | key2 | txt +------+----------------------+------------------------------------------ + 1 | KEY1-1 | test key + 1 | KEY1-2 | test key + 1 | KEY1-3 | test key + 2 | KEY2-1 | test key + 2 | KEY2-2 | test key + 2 | KEY2-3 | test key +(6 rows) + +insert into T_pkey1 values (1, 'KEY1-3', 'should work'); +-- Due to the upper case translation in trigger this must fail +insert into T_pkey2 values (1, 'KEY1-3', 'should fail'); +ERROR: duplicate key '1', 'KEY1-3' for T_pkey2 +insert into T_dta1 values ('trec 1', 1, 'key1-1'); +insert into T_dta1 values ('trec 2', 1, 'key1-2'); +insert into T_dta1 values ('trec 3', 1, 'key1-3'); +-- Must fail due to unknown key in T_pkey1 +insert into T_dta1 values ('trec 4', 1, 'key1-4'); +ERROR: key for t_dta1 not in t_pkey1 +insert into T_dta2 values ('trec 1', 1, 'KEY1-1'); +insert into T_dta2 values ('trec 2', 1, 'KEY1-2'); +insert into T_dta2 values ('trec 3', 1, 'KEY1-3'); +-- Must fail due to unknown key in T_pkey2 +insert into T_dta2 values ('trec 4', 1, 'KEY1-4'); +ERROR: key for t_dta2 not in t_pkey2 +select * from T_dta1; + tkey | ref1 | ref2 +------------+------+---------------------- + trec 1 | 1 | key1-1 + trec 2 | 1 | key1-2 + trec 3 | 1 | key1-3 +(3 rows) + +select * from T_dta2; + tkey | ref1 | ref2 +------------+------+---------------------- + trec 1 | 1 | KEY1-1 + trec 2 | 1 | KEY1-2 + trec 3 | 1 | KEY1-3 +(3 rows) + +update T_pkey1 set key2 = 'key2-9' where key1 = 2 and key2 = 'key2-1'; +update T_pkey1 set key2 = 'key1-9' where key1 = 1 and key2 = 'key1-1'; +ERROR: key '1', 'key1-1 ' referenced by T_dta1 +delete from T_pkey1 where key1 = 2 and key2 = 'key2-2'; +delete from T_pkey1 where key1 = 1 and key2 = 'key1-2'; +ERROR: key '1', 'key1-2 ' referenced by T_dta1 +update T_pkey2 set key2 = 'KEY2-9' where key1 = 2 and key2 = 'KEY2-1'; +update T_pkey2 set key2 = 'KEY1-9' where key1 = 1 and key2 = 'KEY1-1'; +NOTICE: updated 1 entries in T_dta2 for new key in T_pkey2 +delete from T_pkey2 where key1 = 2 and key2 = 'KEY2-2'; +delete from T_pkey2 where key1 = 1 and key2 = 'KEY1-2'; +NOTICE: deleted 1 entries from T_dta2 +select * from T_pkey1; + key1 | key2 | txt +------+----------------------+------------------------------------------ + 1 | key1-1 | test key + 1 | key1-2 | test key + 1 | key1-3 | test key + 2 | key2-3 | test key + 1 | KEY1-3 | should work + 2 | key2-9 | test key +(6 rows) + +select * from T_pkey2; + key1 | key2 | txt +------+----------------------+------------------------------------------ + 1 | KEY1-3 | test key + 2 | KEY2-3 | test key + 2 | KEY2-9 | test key + 1 | KEY1-9 | test key +(4 rows) + +select * from T_dta1; + tkey | ref1 | ref2 +------------+------+---------------------- + trec 1 | 1 | key1-1 + trec 2 | 1 | key1-2 + trec 3 | 1 | key1-3 +(3 rows) + +select * from T_dta2; + tkey | ref1 | ref2 +------------+------+---------------------- + trec 3 | 1 | KEY1-3 + trec 1 | 1 | KEY1-9 +(2 rows) + +select tcl_avg(key1) from T_pkey1; + tcl_avg +--------- + 1 +(1 row) + +select tcl_sum(key1) from T_pkey1; + tcl_sum +--------- + 8 +(1 row) + +select tcl_avg(key1) from T_pkey2; + tcl_avg +--------- + 1 +(1 row) + +select tcl_sum(key1) from T_pkey2; + tcl_sum +--------- + 6 +(1 row) + +-- The following should return NULL instead of 0 +select tcl_avg(key1) from T_pkey1 where key1 = 99; + tcl_avg +--------- + +(1 row) + +select tcl_sum(key1) from T_pkey1 where key1 = 99; + tcl_sum +--------- + 0 +(1 row) + +select 1 @< 2; + ?column? +---------- + t +(1 row) + +select 100 @< 4; + ?column? +---------- + f +(1 row) + +select * from T_pkey1 order by key1 using @<, key2; + key1 | key2 | txt +------+----------------------+------------------------------------------ + 1 | KEY1-3 | should work + 1 | key1-1 | test key + 1 | key1-2 | test key + 1 | key1-3 | test key + 2 | key2-3 | test key + 2 | key2-9 | test key +(6 rows) + +select * from T_pkey2 order by key1 using @<, key2; + key1 | key2 | txt +------+----------------------+------------------------------------------ + 1 | KEY1-3 | test key + 1 | KEY1-9 | test key + 2 | KEY2-3 | test key + 2 | KEY2-9 | test key +(4 rows) + diff --git a/src/pl/tcl/expected/pltcl_setup.out b/src/pl/tcl/expected/pltcl_setup.out new file mode 100644 index 00000000000..e168b121b86 --- /dev/null +++ b/src/pl/tcl/expected/pltcl_setup.out @@ -0,0 +1,404 @@ +-- +-- checkpoint so that if we have a crash in the tests, replay of the +-- just-completed CREATE DATABASE won't discard the core dump file +-- +checkpoint; +-- +-- Create the tables used in the test queries +-- +-- T_pkey1 is the primary key table for T_dta1. Entries from T_pkey1 +-- Cannot be changed or deleted if they are referenced from T_dta1. +-- +-- T_pkey2 is the primary key table for T_dta2. If the key values in +-- T_pkey2 are changed, the references in T_dta2 follow. If entries +-- are deleted, the referencing entries from T_dta2 are deleted too. +-- The values for field key2 in T_pkey2 are silently converted to +-- upper case on insert/update. +-- +create table T_pkey1 ( + key1 int4, + key2 char(20), + txt char(40) +); +create table T_pkey2 ( + key1 int4, + key2 char(20), + txt char(40) +); +create table T_dta1 ( + tkey char(10), + ref1 int4, + ref2 char(20) +); +create table T_dta2 ( + tkey char(10), + ref1 int4, + ref2 char(20) +); +-- +-- Function to check key existance in T_pkey1 +-- +create function check_pkey1_exists(int4, bpchar) returns bool as ' + if {![info exists GD]} { + set GD(plan) [spi_prepare \\ + "select 1 from T_pkey1 \\ + where key1 = \\$1 and key2 = \\$2" \\ + {int4 bpchar}] + } + + set n [spi_execp -count 1 $GD(plan) [list $1 $2]] + + if {$n > 0} { + return "t" + } + return "f" +' language 'pltcl'; +-- +-- Trigger function on every change to T_pkey1 +-- +create function trig_pkey1_before() returns trigger as ' + # + # Create prepared plans on the first call + # + if {![info exists GD]} { + # + # Plan to check for duplicate key in T_pkey1 + # + set GD(plan_pkey1) [spi_prepare \\ + "select check_pkey1_exists(\\$1, \\$2) as ret" \\ + {int4 bpchar}] + # + # Plan to check for references from T_dta1 + # + set GD(plan_dta1) [spi_prepare \\ + "select 1 from T_dta1 \\ + where ref1 = \\$1 and ref2 = \\$2" \\ + {int4 bpchar}] + } + + # + # Initialize flags + # + set check_old_ref 0 + set check_new_dup 0 + + switch $TG_op { + INSERT { + # + # Must check for duplicate key on INSERT + # + set check_new_dup 1 + } + UPDATE { + # + # Must check for duplicate key on UPDATE only if + # the key changes. In that case we must check for + # references to OLD values too. + # + if {[string compare $NEW(key1) $OLD(key1)] != 0} { + set check_old_ref 1 + set check_new_dup 1 + } + if {[string compare $NEW(key2) $OLD(key2)] != 0} { + set check_old_ref 1 + set check_new_dup 1 + } + } + DELETE { + # + # Must only check for references to OLD on DELETE + # + set check_old_ref 1 + } + } + + if {$check_new_dup} { + # + # Check for duplicate key + # + spi_execp -count 1 $GD(plan_pkey1) [list $NEW(key1) $NEW(key2)] + if {$ret == "t"} { + elog ERROR \\ + "duplicate key ''$NEW(key1)'', ''$NEW(key2)'' for T_pkey1" + } + } + + if {$check_old_ref} { + # + # Check for references to OLD + # + set n [spi_execp -count 1 $GD(plan_dta1) [list $OLD(key1) $OLD(key2)]] + if {$n > 0} { + elog ERROR \\ + "key ''$OLD(key1)'', ''$OLD(key2)'' referenced by T_dta1" + } + } + + # + # Anything is fine - let operation pass through + # + return OK +' language 'pltcl'; +create trigger pkey1_before before insert or update or delete on T_pkey1 + for each row execute procedure + trig_pkey1_before(); +-- +-- Trigger function to check for duplicate keys in T_pkey2 +-- and to force key2 to be upper case only without leading whitespaces +-- +create function trig_pkey2_before() returns trigger as ' + # + # Prepare plan on first call + # + if {![info exists GD]} { + set GD(plan_pkey2) [spi_prepare \\ + "select 1 from T_pkey2 \\ + where key1 = \\$1 and key2 = \\$2" \\ + {int4 bpchar}] + } + + # + # Convert key2 value + # + set NEW(key2) [string toupper [string trim $NEW(key2)]] + + # + # Check for duplicate key + # + set n [spi_execp -count 1 $GD(plan_pkey2) [list $NEW(key1) $NEW(key2)]] + if {$n > 0} { + elog ERROR \\ + "duplicate key ''$NEW(key1)'', ''$NEW(key2)'' for T_pkey2" + } + + # + # Return modified tuple in NEW + # + return [array get NEW] +' language 'pltcl'; +create trigger pkey2_before before insert or update on T_pkey2 + for each row execute procedure + trig_pkey2_before(); +-- +-- Trigger function to force references from T_dta2 follow changes +-- in T_pkey2 or be deleted too. This must be done AFTER the changes +-- in T_pkey2 are done so the trigger for primkey check on T_dta2 +-- fired on our updates will see the new key values in T_pkey2. +-- +create function trig_pkey2_after() returns trigger as ' + # + # Prepare plans on first call + # + if {![info exists GD]} { + # + # Plan to update references from T_dta2 + # + set GD(plan_dta2_upd) [spi_prepare \\ + "update T_dta2 set ref1 = \\$3, ref2 = \\$4 \\ + where ref1 = \\$1 and ref2 = \\$2" \\ + {int4 bpchar int4 bpchar}] + # + # Plan to delete references from T_dta2 + # + set GD(plan_dta2_del) [spi_prepare \\ + "delete from T_dta2 \\ + where ref1 = \\$1 and ref2 = \\$2" \\ + {int4 bpchar}] + } + + # + # Initialize flags + # + set old_ref_follow 0 + set old_ref_delete 0 + + switch $TG_op { + UPDATE { + # + # On update we must let old references follow + # + set NEW(key2) [string toupper $NEW(key2)] + + if {[string compare $NEW(key1) $OLD(key1)] != 0} { + set old_ref_follow 1 + } + if {[string compare $NEW(key2) $OLD(key2)] != 0} { + set old_ref_follow 1 + } + } + DELETE { + # + # On delete we must delete references too + # + set old_ref_delete 1 + } + } + + if {$old_ref_follow} { + # + # Let old references follow and fire NOTICE message if + # there where some + # + set n [spi_execp $GD(plan_dta2_upd) \\ + [list $OLD(key1) $OLD(key2) $NEW(key1) $NEW(key2)]] + if {$n > 0} { + elog NOTICE \\ + "updated $n entries in T_dta2 for new key in T_pkey2" + } + } + + if {$old_ref_delete} { + # + # delete references and fire NOTICE message if + # there where some + # + set n [spi_execp $GD(plan_dta2_del) \\ + [list $OLD(key1) $OLD(key2)]] + if {$n > 0} { + elog NOTICE \\ + "deleted $n entries from T_dta2" + } + } + + return OK +' language 'pltcl'; +create trigger pkey2_after after update or delete on T_pkey2 + for each row execute procedure + trig_pkey2_after(); +-- +-- Generic trigger function to check references in T_dta1 and T_dta2 +-- +create function check_primkey() returns trigger as ' + # + # For every trigger/relation pair we create + # a saved plan and hold them in GD + # + set plankey [list "plan" $TG_name $TG_relid] + set planrel [list "relname" $TG_relid] + + # + # Extract the pkey relation name + # + set keyidx [expr [llength $args] / 2] + set keyrel [string tolower [lindex $args $keyidx]] + + if {![info exists GD($plankey)]} { + # + # We must prepare a new plan. Build up a query string + # for the primary key check. + # + set keylist [lrange $args [expr $keyidx + 1] end] + + set query "select 1 from $keyrel" + set qual " where" + set typlist "" + set idx 1 + foreach key $keylist { + set key [string tolower $key] + # + # Add the qual part to the query string + # + append query "$qual $key = \\$$idx" + set qual " and" + + # + # Lookup the fields type in pg_attribute + # + set n [spi_exec "select T.typname \\ + from pg_catalog.pg_type T, pg_catalog.pg_attribute A, pg_catalog.pg_class C \\ + where C.relname = ''[quote $keyrel]'' \\ + and C.oid = A.attrelid \\ + and A.attname = ''[quote $key]'' \\ + and A.atttypid = T.oid"] + if {$n != 1} { + elog ERROR "table $keyrel doesn''t have a field named $key" + } + + # + # Append the fields type to the argument type list + # + lappend typlist $typname + incr idx + } + + # + # Prepare the plan + # + set GD($plankey) [spi_prepare $query $typlist] + + # + # Lookup and remember the table name for later error messages + # + spi_exec "select relname from pg_catalog.pg_class \\ + where oid = ''$TG_relid''::oid" + set GD($planrel) $relname + } + + # + # Build the argument list from the NEW row + # + incr keyidx -1 + set arglist "" + foreach arg [lrange $args 0 $keyidx] { + lappend arglist $NEW($arg) + } + + # + # Check for the primary key + # + set n [spi_execp -count 1 $GD($plankey) $arglist] + if {$n <= 0} { + elog ERROR "key for $GD($planrel) not in $keyrel" + } + + # + # Anything is fine + # + return OK +' language 'pltcl'; +create trigger dta1_before before insert or update on T_dta1 + for each row execute procedure + check_primkey('ref1', 'ref2', 'T_pkey1', 'key1', 'key2'); +create trigger dta2_before before insert or update on T_dta2 + for each row execute procedure + check_primkey('ref1', 'ref2', 'T_pkey2', 'key1', 'key2'); +create function tcl_int4add(int4,int4) returns int4 as ' + return [expr $1 + $2] +' language 'pltcl'; +-- We use split(n) as a quick-and-dirty way of parsing the input array +-- value, which comes in as a string like '{1,2}'. There are better ways... +create function tcl_int4_accum(int4[], int4) returns int4[] as ' + set state [split $1 "{,}"] + set newsum [expr {[lindex $state 1] + $2}] + set newcnt [expr {[lindex $state 2] + 1}] + return "{$newsum,$newcnt}" +' language 'pltcl'; +create function tcl_int4_avg(int4[]) returns int4 as ' + set state [split $1 "{,}"] + if {[lindex $state 2] == 0} { return_null } + return [expr {[lindex $state 1] / [lindex $state 2]}] +' language 'pltcl'; +create aggregate tcl_avg ( + sfunc = tcl_int4_accum, + basetype = int4, + stype = int4[], + finalfunc = tcl_int4_avg, + initcond = '{0,0}' + ); +create aggregate tcl_sum ( + sfunc = tcl_int4add, + basetype = int4, + stype = int4, + initcond1 = 0 + ); +create function tcl_int4lt(int4,int4) returns bool as ' + if {$1 < $2} { + return t + } + return f +' language 'pltcl'; +create operator @< ( + leftarg = int4, + rightarg = int4, + procedure = tcl_int4lt + ); diff --git a/src/pl/tcl/sql/pltcl_queries.sql b/src/pl/tcl/sql/pltcl_queries.sql new file mode 100644 index 00000000000..9cb059ed15f --- /dev/null +++ b/src/pl/tcl/sql/pltcl_queries.sql @@ -0,0 +1,75 @@ +-- suppress CONTEXT so that function OIDs aren't in output +\set VERBOSITY terse + +insert into T_pkey1 values (1, 'key1-1', 'test key'); +insert into T_pkey1 values (1, 'key1-2', 'test key'); +insert into T_pkey1 values (1, 'key1-3', 'test key'); +insert into T_pkey1 values (2, 'key2-1', 'test key'); +insert into T_pkey1 values (2, 'key2-2', 'test key'); +insert into T_pkey1 values (2, 'key2-3', 'test key'); + +insert into T_pkey2 values (1, 'key1-1', 'test key'); +insert into T_pkey2 values (1, 'key1-2', 'test key'); +insert into T_pkey2 values (1, 'key1-3', 'test key'); +insert into T_pkey2 values (2, 'key2-1', 'test key'); +insert into T_pkey2 values (2, 'key2-2', 'test key'); +insert into T_pkey2 values (2, 'key2-3', 'test key'); + +select * from T_pkey1; + +-- key2 in T_pkey2 should have upper case only +select * from T_pkey2; + +insert into T_pkey1 values (1, 'KEY1-3', 'should work'); + +-- Due to the upper case translation in trigger this must fail +insert into T_pkey2 values (1, 'KEY1-3', 'should fail'); + +insert into T_dta1 values ('trec 1', 1, 'key1-1'); +insert into T_dta1 values ('trec 2', 1, 'key1-2'); +insert into T_dta1 values ('trec 3', 1, 'key1-3'); + +-- Must fail due to unknown key in T_pkey1 +insert into T_dta1 values ('trec 4', 1, 'key1-4'); + +insert into T_dta2 values ('trec 1', 1, 'KEY1-1'); +insert into T_dta2 values ('trec 2', 1, 'KEY1-2'); +insert into T_dta2 values ('trec 3', 1, 'KEY1-3'); + +-- Must fail due to unknown key in T_pkey2 +insert into T_dta2 values ('trec 4', 1, 'KEY1-4'); + +select * from T_dta1; + +select * from T_dta2; + +update T_pkey1 set key2 = 'key2-9' where key1 = 2 and key2 = 'key2-1'; +update T_pkey1 set key2 = 'key1-9' where key1 = 1 and key2 = 'key1-1'; +delete from T_pkey1 where key1 = 2 and key2 = 'key2-2'; +delete from T_pkey1 where key1 = 1 and key2 = 'key1-2'; + +update T_pkey2 set key2 = 'KEY2-9' where key1 = 2 and key2 = 'KEY2-1'; +update T_pkey2 set key2 = 'KEY1-9' where key1 = 1 and key2 = 'KEY1-1'; +delete from T_pkey2 where key1 = 2 and key2 = 'KEY2-2'; +delete from T_pkey2 where key1 = 1 and key2 = 'KEY1-2'; + +select * from T_pkey1; +select * from T_pkey2; +select * from T_dta1; +select * from T_dta2; + +select tcl_avg(key1) from T_pkey1; +select tcl_sum(key1) from T_pkey1; +select tcl_avg(key1) from T_pkey2; +select tcl_sum(key1) from T_pkey2; + +-- The following should return NULL instead of 0 +select tcl_avg(key1) from T_pkey1 where key1 = 99; +select tcl_sum(key1) from T_pkey1 where key1 = 99; + +select 1 @< 2; +select 100 @< 4; + +select * from T_pkey1 order by key1 using @<, key2; +select * from T_pkey2 order by key1 using @<, key2; + diff --git a/src/pl/tcl/sql/pltcl_setup.sql b/src/pl/tcl/sql/pltcl_setup.sql new file mode 100644 index 00000000000..78ddd867eb4 --- /dev/null +++ b/src/pl/tcl/sql/pltcl_setup.sql @@ -0,0 +1,438 @@ +-- +-- checkpoint so that if we have a crash in the tests, replay of the +-- just-completed CREATE DATABASE won't discard the core dump file +-- +checkpoint; + +-- +-- Create the tables used in the test queries +-- +-- T_pkey1 is the primary key table for T_dta1. Entries from T_pkey1 +-- Cannot be changed or deleted if they are referenced from T_dta1. +-- +-- T_pkey2 is the primary key table for T_dta2. If the key values in +-- T_pkey2 are changed, the references in T_dta2 follow. If entries +-- are deleted, the referencing entries from T_dta2 are deleted too. +-- The values for field key2 in T_pkey2 are silently converted to +-- upper case on insert/update. +-- +create table T_pkey1 ( + key1 int4, + key2 char(20), + txt char(40) +); + +create table T_pkey2 ( + key1 int4, + key2 char(20), + txt char(40) +); + +create table T_dta1 ( + tkey char(10), + ref1 int4, + ref2 char(20) +); + +create table T_dta2 ( + tkey char(10), + ref1 int4, + ref2 char(20) +); + + +-- +-- Function to check key existance in T_pkey1 +-- +create function check_pkey1_exists(int4, bpchar) returns bool as ' + if {![info exists GD]} { + set GD(plan) [spi_prepare \\ + "select 1 from T_pkey1 \\ + where key1 = \\$1 and key2 = \\$2" \\ + {int4 bpchar}] + } + + set n [spi_execp -count 1 $GD(plan) [list $1 $2]] + + if {$n > 0} { + return "t" + } + return "f" +' language 'pltcl'; + + +-- +-- Trigger function on every change to T_pkey1 +-- +create function trig_pkey1_before() returns trigger as ' + # + # Create prepared plans on the first call + # + if {![info exists GD]} { + # + # Plan to check for duplicate key in T_pkey1 + # + set GD(plan_pkey1) [spi_prepare \\ + "select check_pkey1_exists(\\$1, \\$2) as ret" \\ + {int4 bpchar}] + # + # Plan to check for references from T_dta1 + # + set GD(plan_dta1) [spi_prepare \\ + "select 1 from T_dta1 \\ + where ref1 = \\$1 and ref2 = \\$2" \\ + {int4 bpchar}] + } + + # + # Initialize flags + # + set check_old_ref 0 + set check_new_dup 0 + + switch $TG_op { + INSERT { + # + # Must check for duplicate key on INSERT + # + set check_new_dup 1 + } + UPDATE { + # + # Must check for duplicate key on UPDATE only if + # the key changes. In that case we must check for + # references to OLD values too. + # + if {[string compare $NEW(key1) $OLD(key1)] != 0} { + set check_old_ref 1 + set check_new_dup 1 + } + if {[string compare $NEW(key2) $OLD(key2)] != 0} { + set check_old_ref 1 + set check_new_dup 1 + } + } + DELETE { + # + # Must only check for references to OLD on DELETE + # + set check_old_ref 1 + } + } + + if {$check_new_dup} { + # + # Check for duplicate key + # + spi_execp -count 1 $GD(plan_pkey1) [list $NEW(key1) $NEW(key2)] + if {$ret == "t"} { + elog ERROR \\ + "duplicate key ''$NEW(key1)'', ''$NEW(key2)'' for T_pkey1" + } + } + + if {$check_old_ref} { + # + # Check for references to OLD + # + set n [spi_execp -count 1 $GD(plan_dta1) [list $OLD(key1) $OLD(key2)]] + if {$n > 0} { + elog ERROR \\ + "key ''$OLD(key1)'', ''$OLD(key2)'' referenced by T_dta1" + } + } + + # + # Anything is fine - let operation pass through + # + return OK +' language 'pltcl'; + + +create trigger pkey1_before before insert or update or delete on T_pkey1 + for each row execute procedure + trig_pkey1_before(); + + +-- +-- Trigger function to check for duplicate keys in T_pkey2 +-- and to force key2 to be upper case only without leading whitespaces +-- +create function trig_pkey2_before() returns trigger as ' + # + # Prepare plan on first call + # + if {![info exists GD]} { + set GD(plan_pkey2) [spi_prepare \\ + "select 1 from T_pkey2 \\ + where key1 = \\$1 and key2 = \\$2" \\ + {int4 bpchar}] + } + + # + # Convert key2 value + # + set NEW(key2) [string toupper [string trim $NEW(key2)]] + + # + # Check for duplicate key + # + set n [spi_execp -count 1 $GD(plan_pkey2) [list $NEW(key1) $NEW(key2)]] + if {$n > 0} { + elog ERROR \\ + "duplicate key ''$NEW(key1)'', ''$NEW(key2)'' for T_pkey2" + } + + # + # Return modified tuple in NEW + # + return [array get NEW] +' language 'pltcl'; + + +create trigger pkey2_before before insert or update on T_pkey2 + for each row execute procedure + trig_pkey2_before(); + + +-- +-- Trigger function to force references from T_dta2 follow changes +-- in T_pkey2 or be deleted too. This must be done AFTER the changes +-- in T_pkey2 are done so the trigger for primkey check on T_dta2 +-- fired on our updates will see the new key values in T_pkey2. +-- +create function trig_pkey2_after() returns trigger as ' + # + # Prepare plans on first call + # + if {![info exists GD]} { + # + # Plan to update references from T_dta2 + # + set GD(plan_dta2_upd) [spi_prepare \\ + "update T_dta2 set ref1 = \\$3, ref2 = \\$4 \\ + where ref1 = \\$1 and ref2 = \\$2" \\ + {int4 bpchar int4 bpchar}] + # + # Plan to delete references from T_dta2 + # + set GD(plan_dta2_del) [spi_prepare \\ + "delete from T_dta2 \\ + where ref1 = \\$1 and ref2 = \\$2" \\ + {int4 bpchar}] + } + + # + # Initialize flags + # + set old_ref_follow 0 + set old_ref_delete 0 + + switch $TG_op { + UPDATE { + # + # On update we must let old references follow + # + set NEW(key2) [string toupper $NEW(key2)] + + if {[string compare $NEW(key1) $OLD(key1)] != 0} { + set old_ref_follow 1 + } + if {[string compare $NEW(key2) $OLD(key2)] != 0} { + set old_ref_follow 1 + } + } + DELETE { + # + # On delete we must delete references too + # + set old_ref_delete 1 + } + } + + if {$old_ref_follow} { + # + # Let old references follow and fire NOTICE message if + # there where some + # + set n [spi_execp $GD(plan_dta2_upd) \\ + [list $OLD(key1) $OLD(key2) $NEW(key1) $NEW(key2)]] + if {$n > 0} { + elog NOTICE \\ + "updated $n entries in T_dta2 for new key in T_pkey2" + } + } + + if {$old_ref_delete} { + # + # delete references and fire NOTICE message if + # there where some + # + set n [spi_execp $GD(plan_dta2_del) \\ + [list $OLD(key1) $OLD(key2)]] + if {$n > 0} { + elog NOTICE \\ + "deleted $n entries from T_dta2" + } + } + + return OK +' language 'pltcl'; + + +create trigger pkey2_after after update or delete on T_pkey2 + for each row execute procedure + trig_pkey2_after(); + + +-- +-- Generic trigger function to check references in T_dta1 and T_dta2 +-- +create function check_primkey() returns trigger as ' + # + # For every trigger/relation pair we create + # a saved plan and hold them in GD + # + set plankey [list "plan" $TG_name $TG_relid] + set planrel [list "relname" $TG_relid] + + # + # Extract the pkey relation name + # + set keyidx [expr [llength $args] / 2] + set keyrel [string tolower [lindex $args $keyidx]] + + if {![info exists GD($plankey)]} { + # + # We must prepare a new plan. Build up a query string + # for the primary key check. + # + set keylist [lrange $args [expr $keyidx + 1] end] + + set query "select 1 from $keyrel" + set qual " where" + set typlist "" + set idx 1 + foreach key $keylist { + set key [string tolower $key] + # + # Add the qual part to the query string + # + append query "$qual $key = \\$$idx" + set qual " and" + + # + # Lookup the fields type in pg_attribute + # + set n [spi_exec "select T.typname \\ + from pg_catalog.pg_type T, pg_catalog.pg_attribute A, pg_catalog.pg_class C \\ + where C.relname = ''[quote $keyrel]'' \\ + and C.oid = A.attrelid \\ + and A.attname = ''[quote $key]'' \\ + and A.atttypid = T.oid"] + if {$n != 1} { + elog ERROR "table $keyrel doesn''t have a field named $key" + } + + # + # Append the fields type to the argument type list + # + lappend typlist $typname + incr idx + } + + # + # Prepare the plan + # + set GD($plankey) [spi_prepare $query $typlist] + + # + # Lookup and remember the table name for later error messages + # + spi_exec "select relname from pg_catalog.pg_class \\ + where oid = ''$TG_relid''::oid" + set GD($planrel) $relname + } + + # + # Build the argument list from the NEW row + # + incr keyidx -1 + set arglist "" + foreach arg [lrange $args 0 $keyidx] { + lappend arglist $NEW($arg) + } + + # + # Check for the primary key + # + set n [spi_execp -count 1 $GD($plankey) $arglist] + if {$n <= 0} { + elog ERROR "key for $GD($planrel) not in $keyrel" + } + + # + # Anything is fine + # + return OK +' language 'pltcl'; + + +create trigger dta1_before before insert or update on T_dta1 + for each row execute procedure + check_primkey('ref1', 'ref2', 'T_pkey1', 'key1', 'key2'); + + +create trigger dta2_before before insert or update on T_dta2 + for each row execute procedure + check_primkey('ref1', 'ref2', 'T_pkey2', 'key1', 'key2'); + + +create function tcl_int4add(int4,int4) returns int4 as ' + return [expr $1 + $2] +' language 'pltcl'; + +-- We use split(n) as a quick-and-dirty way of parsing the input array +-- value, which comes in as a string like '{1,2}'. There are better ways... + +create function tcl_int4_accum(int4[], int4) returns int4[] as ' + set state [split $1 "{,}"] + set newsum [expr {[lindex $state 1] + $2}] + set newcnt [expr {[lindex $state 2] + 1}] + return "{$newsum,$newcnt}" +' language 'pltcl'; + +create function tcl_int4_avg(int4[]) returns int4 as ' + set state [split $1 "{,}"] + if {[lindex $state 2] == 0} { return_null } + return [expr {[lindex $state 1] / [lindex $state 2]}] +' language 'pltcl'; + +create aggregate tcl_avg ( + sfunc = tcl_int4_accum, + basetype = int4, + stype = int4[], + finalfunc = tcl_int4_avg, + initcond = '{0,0}' + ); + +create aggregate tcl_sum ( + sfunc = tcl_int4add, + basetype = int4, + stype = int4, + initcond1 = 0 + ); + +create function tcl_int4lt(int4,int4) returns bool as ' + if {$1 < $2} { + return t + } + return f +' language 'pltcl'; + +create operator @< ( + leftarg = int4, + rightarg = int4, + procedure = tcl_int4lt + ); + diff --git a/src/pl/tcl/test/README b/src/pl/tcl/test/README deleted file mode 100644 index ed931420fa8..00000000000 --- a/src/pl/tcl/test/README +++ /dev/null @@ -1,8 +0,0 @@ - - This is a small test suite for PL/Tcl. - - Just run the script runtest and compare the files - test.expected against test.out after. - - - Jan diff --git a/src/pl/tcl/test/runtest b/src/pl/tcl/test/runtest deleted file mode 100755 index b46604bd3f2..00000000000 --- a/src/pl/tcl/test/runtest +++ /dev/null @@ -1,30 +0,0 @@ -#!/bin/sh - -DBNAME=pltcl_test -export DBNAME - -echo "**** Destroy old database $DBNAME ****" -dropdb $DBNAME - -sleep 1 - -echo "**** Create test database $DBNAME ****" -createdb $DBNAME || exit 1 - -echo "**** Create procedural language pltcl ****" -createlang pltcl $DBNAME || exit 1 - -echo "**** Create tables, functions and triggers ****" -psql -q -n $DBNAME test.out 2>&1 - -if diff test.expected test.out >/dev/null 2>&1 ; then - echo " Tests passed O.K." - rm test.out -else - echo " Tests failed - look at diffs between" - echo " test.expected and test.out" -fi - diff --git a/src/pl/tcl/test/test.expected b/src/pl/tcl/test/test.expected deleted file mode 100644 index cefeaf4418d..00000000000 --- a/src/pl/tcl/test/test.expected +++ /dev/null @@ -1,178 +0,0 @@ -insert into T_pkey1 values (1, 'key1-1', 'test key'); -insert into T_pkey1 values (1, 'key1-2', 'test key'); -insert into T_pkey1 values (1, 'key1-3', 'test key'); -insert into T_pkey1 values (2, 'key2-1', 'test key'); -insert into T_pkey1 values (2, 'key2-2', 'test key'); -insert into T_pkey1 values (2, 'key2-3', 'test key'); -insert into T_pkey2 values (1, 'key1-1', 'test key'); -insert into T_pkey2 values (1, 'key1-2', 'test key'); -insert into T_pkey2 values (1, 'key1-3', 'test key'); -insert into T_pkey2 values (2, 'key2-1', 'test key'); -insert into T_pkey2 values (2, 'key2-2', 'test key'); -insert into T_pkey2 values (2, 'key2-3', 'test key'); -select * from T_pkey1; - key1 | key2 | txt -------+----------------------+------------------------------------------ - 1 | key1-1 | test key - 1 | key1-2 | test key - 1 | key1-3 | test key - 2 | key2-1 | test key - 2 | key2-2 | test key - 2 | key2-3 | test key -(6 rows) - -select * from T_pkey2; - key1 | key2 | txt -------+----------------------+------------------------------------------ - 1 | KEY1-1 | test key - 1 | KEY1-2 | test key - 1 | KEY1-3 | test key - 2 | KEY2-1 | test key - 2 | KEY2-2 | test key - 2 | KEY2-3 | test key -(6 rows) - -insert into T_pkey1 values (1, 'KEY1-3', 'should work'); -insert into T_pkey2 values (1, 'KEY1-3', 'should fail'); -ERROR: duplicate key '1', 'KEY1-3' for T_pkey2 -insert into T_dta1 values ('trec 1', 1, 'key1-1'); -insert into T_dta1 values ('trec 2', 1, 'key1-2'); -insert into T_dta1 values ('trec 3', 1, 'key1-3'); -insert into T_dta1 values ('trec 4', 1, 'key1-4'); -ERROR: key for t_dta1 not in t_pkey1 -insert into T_dta2 values ('trec 1', 1, 'KEY1-1'); -insert into T_dta2 values ('trec 2', 1, 'KEY1-2'); -insert into T_dta2 values ('trec 3', 1, 'KEY1-3'); -insert into T_dta2 values ('trec 4', 1, 'KEY1-4'); -ERROR: key for t_dta2 not in t_pkey2 -select * from T_dta1; - tkey | ref1 | ref2 -------------+------+---------------------- - trec 1 | 1 | key1-1 - trec 2 | 1 | key1-2 - trec 3 | 1 | key1-3 -(3 rows) - -select * from T_dta2; - tkey | ref1 | ref2 -------------+------+---------------------- - trec 1 | 1 | KEY1-1 - trec 2 | 1 | KEY1-2 - trec 3 | 1 | KEY1-3 -(3 rows) - -update T_pkey1 set key2 = 'key2-9' where key1 = 2 and key2 = 'key2-1'; -update T_pkey1 set key2 = 'key1-9' where key1 = 1 and key2 = 'key1-1'; -ERROR: key '1', 'key1-1 ' referenced by T_dta1 -delete from T_pkey1 where key1 = 2 and key2 = 'key2-2'; -delete from T_pkey1 where key1 = 1 and key2 = 'key1-2'; -ERROR: key '1', 'key1-2 ' referenced by T_dta1 -update T_pkey2 set key2 = 'KEY2-9' where key1 = 2 and key2 = 'KEY2-1'; -update T_pkey2 set key2 = 'KEY1-9' where key1 = 1 and key2 = 'KEY1-1'; -NOTICE: updated 1 entries in T_dta2 for new key in T_pkey2 -delete from T_pkey2 where key1 = 2 and key2 = 'KEY2-2'; -delete from T_pkey2 where key1 = 1 and key2 = 'KEY1-2'; -NOTICE: deleted 1 entries from T_dta2 -select * from T_pkey1; - key1 | key2 | txt -------+----------------------+------------------------------------------ - 1 | key1-1 | test key - 1 | key1-2 | test key - 1 | key1-3 | test key - 2 | key2-3 | test key - 1 | KEY1-3 | should work - 2 | key2-9 | test key -(6 rows) - -select * from T_pkey2; - key1 | key2 | txt -------+----------------------+------------------------------------------ - 1 | KEY1-3 | test key - 2 | KEY2-3 | test key - 2 | KEY2-9 | test key - 1 | KEY1-9 | test key -(4 rows) - -select * from T_dta1; - tkey | ref1 | ref2 -------------+------+---------------------- - trec 1 | 1 | key1-1 - trec 2 | 1 | key1-2 - trec 3 | 1 | key1-3 -(3 rows) - -select * from T_dta2; - tkey | ref1 | ref2 -------------+------+---------------------- - trec 3 | 1 | KEY1-3 - trec 1 | 1 | KEY1-9 -(2 rows) - -select tcl_avg(key1) from T_pkey1; - tcl_avg ---------- - 1 -(1 row) - -select tcl_sum(key1) from T_pkey1; - tcl_sum ---------- - 8 -(1 row) - -select tcl_avg(key1) from T_pkey2; - tcl_avg ---------- - 1 -(1 row) - -select tcl_sum(key1) from T_pkey2; - tcl_sum ---------- - 6 -(1 row) - -select tcl_avg(key1) from T_pkey1 where key1 = 99; - tcl_avg ---------- - -(1 row) - -select tcl_sum(key1) from T_pkey1 where key1 = 99; - tcl_sum ---------- - 0 -(1 row) - -select 1 @< 2; - ?column? ----------- - t -(1 row) - -select 100 @< 4; - ?column? ----------- - f -(1 row) - -select * from T_pkey1 order by key1 using @<, key2; - key1 | key2 | txt -------+----------------------+------------------------------------------ - 1 | KEY1-3 | should work - 1 | key1-1 | test key - 1 | key1-2 | test key - 1 | key1-3 | test key - 2 | key2-3 | test key - 2 | key2-9 | test key -(6 rows) - -select * from T_pkey2 order by key1 using @<, key2; - key1 | key2 | txt -------+----------------------+------------------------------------------ - 1 | KEY1-3 | test key - 1 | KEY1-9 | test key - 2 | KEY2-3 | test key - 2 | KEY2-9 | test key -(4 rows) - diff --git a/src/pl/tcl/test/test_queries.sql b/src/pl/tcl/test/test_queries.sql deleted file mode 100644 index 9cb059ed15f..00000000000 --- a/src/pl/tcl/test/test_queries.sql +++ /dev/null @@ -1,75 +0,0 @@ --- suppress CONTEXT so that function OIDs aren't in output -\set VERBOSITY terse - -insert into T_pkey1 values (1, 'key1-1', 'test key'); -insert into T_pkey1 values (1, 'key1-2', 'test key'); -insert into T_pkey1 values (1, 'key1-3', 'test key'); -insert into T_pkey1 values (2, 'key2-1', 'test key'); -insert into T_pkey1 values (2, 'key2-2', 'test key'); -insert into T_pkey1 values (2, 'key2-3', 'test key'); - -insert into T_pkey2 values (1, 'key1-1', 'test key'); -insert into T_pkey2 values (1, 'key1-2', 'test key'); -insert into T_pkey2 values (1, 'key1-3', 'test key'); -insert into T_pkey2 values (2, 'key2-1', 'test key'); -insert into T_pkey2 values (2, 'key2-2', 'test key'); -insert into T_pkey2 values (2, 'key2-3', 'test key'); - -select * from T_pkey1; - --- key2 in T_pkey2 should have upper case only -select * from T_pkey2; - -insert into T_pkey1 values (1, 'KEY1-3', 'should work'); - --- Due to the upper case translation in trigger this must fail -insert into T_pkey2 values (1, 'KEY1-3', 'should fail'); - -insert into T_dta1 values ('trec 1', 1, 'key1-1'); -insert into T_dta1 values ('trec 2', 1, 'key1-2'); -insert into T_dta1 values ('trec 3', 1, 'key1-3'); - --- Must fail due to unknown key in T_pkey1 -insert into T_dta1 values ('trec 4', 1, 'key1-4'); - -insert into T_dta2 values ('trec 1', 1, 'KEY1-1'); -insert into T_dta2 values ('trec 2', 1, 'KEY1-2'); -insert into T_dta2 values ('trec 3', 1, 'KEY1-3'); - --- Must fail due to unknown key in T_pkey2 -insert into T_dta2 values ('trec 4', 1, 'KEY1-4'); - -select * from T_dta1; - -select * from T_dta2; - -update T_pkey1 set key2 = 'key2-9' where key1 = 2 and key2 = 'key2-1'; -update T_pkey1 set key2 = 'key1-9' where key1 = 1 and key2 = 'key1-1'; -delete from T_pkey1 where key1 = 2 and key2 = 'key2-2'; -delete from T_pkey1 where key1 = 1 and key2 = 'key1-2'; - -update T_pkey2 set key2 = 'KEY2-9' where key1 = 2 and key2 = 'KEY2-1'; -update T_pkey2 set key2 = 'KEY1-9' where key1 = 1 and key2 = 'KEY1-1'; -delete from T_pkey2 where key1 = 2 and key2 = 'KEY2-2'; -delete from T_pkey2 where key1 = 1 and key2 = 'KEY1-2'; - -select * from T_pkey1; -select * from T_pkey2; -select * from T_dta1; -select * from T_dta2; - -select tcl_avg(key1) from T_pkey1; -select tcl_sum(key1) from T_pkey1; -select tcl_avg(key1) from T_pkey2; -select tcl_sum(key1) from T_pkey2; - --- The following should return NULL instead of 0 -select tcl_avg(key1) from T_pkey1 where key1 = 99; -select tcl_sum(key1) from T_pkey1 where key1 = 99; - -select 1 @< 2; -select 100 @< 4; - -select * from T_pkey1 order by key1 using @<, key2; -select * from T_pkey2 order by key1 using @<, key2; - diff --git a/src/pl/tcl/test/test_setup.sql b/src/pl/tcl/test/test_setup.sql deleted file mode 100644 index 78ddd867eb4..00000000000 --- a/src/pl/tcl/test/test_setup.sql +++ /dev/null @@ -1,438 +0,0 @@ --- --- checkpoint so that if we have a crash in the tests, replay of the --- just-completed CREATE DATABASE won't discard the core dump file --- -checkpoint; - --- --- Create the tables used in the test queries --- --- T_pkey1 is the primary key table for T_dta1. Entries from T_pkey1 --- Cannot be changed or deleted if they are referenced from T_dta1. --- --- T_pkey2 is the primary key table for T_dta2. If the key values in --- T_pkey2 are changed, the references in T_dta2 follow. If entries --- are deleted, the referencing entries from T_dta2 are deleted too. --- The values for field key2 in T_pkey2 are silently converted to --- upper case on insert/update. --- -create table T_pkey1 ( - key1 int4, - key2 char(20), - txt char(40) -); - -create table T_pkey2 ( - key1 int4, - key2 char(20), - txt char(40) -); - -create table T_dta1 ( - tkey char(10), - ref1 int4, - ref2 char(20) -); - -create table T_dta2 ( - tkey char(10), - ref1 int4, - ref2 char(20) -); - - --- --- Function to check key existance in T_pkey1 --- -create function check_pkey1_exists(int4, bpchar) returns bool as ' - if {![info exists GD]} { - set GD(plan) [spi_prepare \\ - "select 1 from T_pkey1 \\ - where key1 = \\$1 and key2 = \\$2" \\ - {int4 bpchar}] - } - - set n [spi_execp -count 1 $GD(plan) [list $1 $2]] - - if {$n > 0} { - return "t" - } - return "f" -' language 'pltcl'; - - --- --- Trigger function on every change to T_pkey1 --- -create function trig_pkey1_before() returns trigger as ' - # - # Create prepared plans on the first call - # - if {![info exists GD]} { - # - # Plan to check for duplicate key in T_pkey1 - # - set GD(plan_pkey1) [spi_prepare \\ - "select check_pkey1_exists(\\$1, \\$2) as ret" \\ - {int4 bpchar}] - # - # Plan to check for references from T_dta1 - # - set GD(plan_dta1) [spi_prepare \\ - "select 1 from T_dta1 \\ - where ref1 = \\$1 and ref2 = \\$2" \\ - {int4 bpchar}] - } - - # - # Initialize flags - # - set check_old_ref 0 - set check_new_dup 0 - - switch $TG_op { - INSERT { - # - # Must check for duplicate key on INSERT - # - set check_new_dup 1 - } - UPDATE { - # - # Must check for duplicate key on UPDATE only if - # the key changes. In that case we must check for - # references to OLD values too. - # - if {[string compare $NEW(key1) $OLD(key1)] != 0} { - set check_old_ref 1 - set check_new_dup 1 - } - if {[string compare $NEW(key2) $OLD(key2)] != 0} { - set check_old_ref 1 - set check_new_dup 1 - } - } - DELETE { - # - # Must only check for references to OLD on DELETE - # - set check_old_ref 1 - } - } - - if {$check_new_dup} { - # - # Check for duplicate key - # - spi_execp -count 1 $GD(plan_pkey1) [list $NEW(key1) $NEW(key2)] - if {$ret == "t"} { - elog ERROR \\ - "duplicate key ''$NEW(key1)'', ''$NEW(key2)'' for T_pkey1" - } - } - - if {$check_old_ref} { - # - # Check for references to OLD - # - set n [spi_execp -count 1 $GD(plan_dta1) [list $OLD(key1) $OLD(key2)]] - if {$n > 0} { - elog ERROR \\ - "key ''$OLD(key1)'', ''$OLD(key2)'' referenced by T_dta1" - } - } - - # - # Anything is fine - let operation pass through - # - return OK -' language 'pltcl'; - - -create trigger pkey1_before before insert or update or delete on T_pkey1 - for each row execute procedure - trig_pkey1_before(); - - --- --- Trigger function to check for duplicate keys in T_pkey2 --- and to force key2 to be upper case only without leading whitespaces --- -create function trig_pkey2_before() returns trigger as ' - # - # Prepare plan on first call - # - if {![info exists GD]} { - set GD(plan_pkey2) [spi_prepare \\ - "select 1 from T_pkey2 \\ - where key1 = \\$1 and key2 = \\$2" \\ - {int4 bpchar}] - } - - # - # Convert key2 value - # - set NEW(key2) [string toupper [string trim $NEW(key2)]] - - # - # Check for duplicate key - # - set n [spi_execp -count 1 $GD(plan_pkey2) [list $NEW(key1) $NEW(key2)]] - if {$n > 0} { - elog ERROR \\ - "duplicate key ''$NEW(key1)'', ''$NEW(key2)'' for T_pkey2" - } - - # - # Return modified tuple in NEW - # - return [array get NEW] -' language 'pltcl'; - - -create trigger pkey2_before before insert or update on T_pkey2 - for each row execute procedure - trig_pkey2_before(); - - --- --- Trigger function to force references from T_dta2 follow changes --- in T_pkey2 or be deleted too. This must be done AFTER the changes --- in T_pkey2 are done so the trigger for primkey check on T_dta2 --- fired on our updates will see the new key values in T_pkey2. --- -create function trig_pkey2_after() returns trigger as ' - # - # Prepare plans on first call - # - if {![info exists GD]} { - # - # Plan to update references from T_dta2 - # - set GD(plan_dta2_upd) [spi_prepare \\ - "update T_dta2 set ref1 = \\$3, ref2 = \\$4 \\ - where ref1 = \\$1 and ref2 = \\$2" \\ - {int4 bpchar int4 bpchar}] - # - # Plan to delete references from T_dta2 - # - set GD(plan_dta2_del) [spi_prepare \\ - "delete from T_dta2 \\ - where ref1 = \\$1 and ref2 = \\$2" \\ - {int4 bpchar}] - } - - # - # Initialize flags - # - set old_ref_follow 0 - set old_ref_delete 0 - - switch $TG_op { - UPDATE { - # - # On update we must let old references follow - # - set NEW(key2) [string toupper $NEW(key2)] - - if {[string compare $NEW(key1) $OLD(key1)] != 0} { - set old_ref_follow 1 - } - if {[string compare $NEW(key2) $OLD(key2)] != 0} { - set old_ref_follow 1 - } - } - DELETE { - # - # On delete we must delete references too - # - set old_ref_delete 1 - } - } - - if {$old_ref_follow} { - # - # Let old references follow and fire NOTICE message if - # there where some - # - set n [spi_execp $GD(plan_dta2_upd) \\ - [list $OLD(key1) $OLD(key2) $NEW(key1) $NEW(key2)]] - if {$n > 0} { - elog NOTICE \\ - "updated $n entries in T_dta2 for new key in T_pkey2" - } - } - - if {$old_ref_delete} { - # - # delete references and fire NOTICE message if - # there where some - # - set n [spi_execp $GD(plan_dta2_del) \\ - [list $OLD(key1) $OLD(key2)]] - if {$n > 0} { - elog NOTICE \\ - "deleted $n entries from T_dta2" - } - } - - return OK -' language 'pltcl'; - - -create trigger pkey2_after after update or delete on T_pkey2 - for each row execute procedure - trig_pkey2_after(); - - --- --- Generic trigger function to check references in T_dta1 and T_dta2 --- -create function check_primkey() returns trigger as ' - # - # For every trigger/relation pair we create - # a saved plan and hold them in GD - # - set plankey [list "plan" $TG_name $TG_relid] - set planrel [list "relname" $TG_relid] - - # - # Extract the pkey relation name - # - set keyidx [expr [llength $args] / 2] - set keyrel [string tolower [lindex $args $keyidx]] - - if {![info exists GD($plankey)]} { - # - # We must prepare a new plan. Build up a query string - # for the primary key check. - # - set keylist [lrange $args [expr $keyidx + 1] end] - - set query "select 1 from $keyrel" - set qual " where" - set typlist "" - set idx 1 - foreach key $keylist { - set key [string tolower $key] - # - # Add the qual part to the query string - # - append query "$qual $key = \\$$idx" - set qual " and" - - # - # Lookup the fields type in pg_attribute - # - set n [spi_exec "select T.typname \\ - from pg_catalog.pg_type T, pg_catalog.pg_attribute A, pg_catalog.pg_class C \\ - where C.relname = ''[quote $keyrel]'' \\ - and C.oid = A.attrelid \\ - and A.attname = ''[quote $key]'' \\ - and A.atttypid = T.oid"] - if {$n != 1} { - elog ERROR "table $keyrel doesn''t have a field named $key" - } - - # - # Append the fields type to the argument type list - # - lappend typlist $typname - incr idx - } - - # - # Prepare the plan - # - set GD($plankey) [spi_prepare $query $typlist] - - # - # Lookup and remember the table name for later error messages - # - spi_exec "select relname from pg_catalog.pg_class \\ - where oid = ''$TG_relid''::oid" - set GD($planrel) $relname - } - - # - # Build the argument list from the NEW row - # - incr keyidx -1 - set arglist "" - foreach arg [lrange $args 0 $keyidx] { - lappend arglist $NEW($arg) - } - - # - # Check for the primary key - # - set n [spi_execp -count 1 $GD($plankey) $arglist] - if {$n <= 0} { - elog ERROR "key for $GD($planrel) not in $keyrel" - } - - # - # Anything is fine - # - return OK -' language 'pltcl'; - - -create trigger dta1_before before insert or update on T_dta1 - for each row execute procedure - check_primkey('ref1', 'ref2', 'T_pkey1', 'key1', 'key2'); - - -create trigger dta2_before before insert or update on T_dta2 - for each row execute procedure - check_primkey('ref1', 'ref2', 'T_pkey2', 'key1', 'key2'); - - -create function tcl_int4add(int4,int4) returns int4 as ' - return [expr $1 + $2] -' language 'pltcl'; - --- We use split(n) as a quick-and-dirty way of parsing the input array --- value, which comes in as a string like '{1,2}'. There are better ways... - -create function tcl_int4_accum(int4[], int4) returns int4[] as ' - set state [split $1 "{,}"] - set newsum [expr {[lindex $state 1] + $2}] - set newcnt [expr {[lindex $state 2] + 1}] - return "{$newsum,$newcnt}" -' language 'pltcl'; - -create function tcl_int4_avg(int4[]) returns int4 as ' - set state [split $1 "{,}"] - if {[lindex $state 2] == 0} { return_null } - return [expr {[lindex $state 1] / [lindex $state 2]}] -' language 'pltcl'; - -create aggregate tcl_avg ( - sfunc = tcl_int4_accum, - basetype = int4, - stype = int4[], - finalfunc = tcl_int4_avg, - initcond = '{0,0}' - ); - -create aggregate tcl_sum ( - sfunc = tcl_int4add, - basetype = int4, - stype = int4, - initcond1 = 0 - ); - -create function tcl_int4lt(int4,int4) returns bool as ' - if {$1 < $2} { - return t - } - return f -' language 'pltcl'; - -create operator @< ( - leftarg = int4, - rightarg = int4, - procedure = tcl_int4lt - ); - -- cgit v1.2.3