aboutsummaryrefslogtreecommitdiff
path: root/src/pl/plpython/expected
diff options
context:
space:
mode:
Diffstat (limited to 'src/pl/plpython/expected')
-rw-r--r--src/pl/plpython/expected/plpython_drop.out5
-rw-r--r--src/pl/plpython/expected/plpython_error.out34
-rw-r--r--src/pl/plpython/expected/plpython_function.out276
-rw-r--r--src/pl/plpython/expected/plpython_populate.out22
-rw-r--r--src/pl/plpython/expected/plpython_schema.out43
-rw-r--r--src/pl/plpython/expected/plpython_test.out184
6 files changed, 564 insertions, 0 deletions
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)
+
generated by cgit v1.2.3 (git 2.25.1) at 2025-08-07 13:30:01 +0000