From 8eeae3e11c8899ce307931a39cf9bce3dfe2da5c Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Wed, 21 Jul 2004 20:44:52 +0000 Subject: [PATCH] Please find enclosed a patch that matches the PL/Perl documentation (fairly closely, I hope) to the current PL/Perl implementation. David Fetter --- doc/src/sgml/plperl.sgml | 322 +++++++++++++++++++++++++++++++-------- 1 file changed, 258 insertions(+), 64 deletions(-) diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml index 8c77b65e31..cc24755615 100644 --- a/doc/src/sgml/plperl.sgml +++ b/doc/src/sgml/plperl.sgml @@ -1,5 +1,5 @@ @@ -34,9 +34,10 @@ $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.25 2004/07/21 20:34:43 momjian E Users of source packages must specially enable the build of - PL/Perl during the installation process. (Refer to the installation - instructions for more information.) Users of binary packages - might find PL/Perl in a separate subpackage. + PL/Perl during the installation process. (Refer to for more information.) Users of + binary packages might find PL/Perl in a separate subpackage. + @@ -54,7 +55,7 @@ $$ LANGUAGE plperl; The body of the function is ordinary Perl code. Since the body of the function is treated as a string by PostgreSQL, it can be specified using - dollar quoting (as shown above), or via the usual single quote + dollar quoting (as shown above), or via the legacy single quote syntax (see for more information). @@ -79,19 +80,22 @@ $$ LANGUAGE plperl; - If an SQL null valuenull valuein PL/Perl is passed to a function, - the argument value will appear as undefined in Perl. The - above function definition will not behave very nicely with null - inputs (in fact, it will act as though they are zeroes). We could - add STRICT to the function definition to make - PostgreSQL do something more reasonable: - if a null value is passed, the function will not be called at all, - but will just return a null result automatically. Alternatively, - we could check for undefined inputs in the function body. For - example, suppose that we wanted perl_max with - one null and one non-null argument to return the non-null argument, - rather than a null value: + If an SQL NULL valuenull + valuein PL/Perl is + passed to a function, the argument value will appear as + undefined in Perl. The above function definition will not + behave very nicely with NULL inputs (in fact, it + will act as though they are zeroes). We could add STRICT + to the function definition to make + PostgreSQL do something more reasonable: if + a NULL value is passed, the function will not be + called at all, but will just return a NULL result + automatically. Alternatively, we could check for undefined inputs in + the function body. For example, suppose that we wanted + perl_max with one NULL and one + non-NULL argument to return the + non-NULL argument, rather than a + NULL value: CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$ @@ -108,9 +112,9 @@ $$ LANGUAGE plperl; - As shown above, to return an SQL null value from a PL/Perl - function, return an undefined value. This can be done whether the - function is strict or not. + As shown above, to return an SQL NULL value from + a PL/Perl function, return an undefined value. This can be done + whether the function is strict or not. @@ -127,7 +131,7 @@ CREATE TABLE employee ( CREATE FUNCTION empcomp(employee) RETURNS integer AS $$ my ($emp) = @_; - return $emp->{'basesalary'} + $emp->{'bonus'}; + return $emp->{basesalary} + $emp->{bonus}; $$ LANGUAGE plperl; SELECT name, empcomp(employee) FROM employee; @@ -135,35 +139,9 @@ SELECT name, empcomp(employee) FROM employee; - There is currently no support for returning a composite-type result - value. + There is now support for returning a composite-type result value. - - - Because the function body is passed as an SQL string literal to - CREATE FUNCTION, you have to use dollar quoting - or escape single quotes and backslashes within your Perl source, - typically by doubling them. Another possible approach is to avoid - writing single quotes by using Perl's extended quoting operators - (q[], qq[], - qw[]). - - - - - - Data Values in PL/Perl - - - The argument values supplied to a PL/Perl function's code are - simply the input arguments converted to text form (just as if they - had been displayed by a SELECT statement). - Conversely, the return command will accept any string - that is acceptable input format for the function's declared return - type. So, the PL/Perl programmer can manipulate data values as if - they were just text. - @@ -171,25 +149,77 @@ SELECT name, empcomp(employee) FROM employee; Access to the database itself from your Perl function can be done via - an experimental module DBD::PgSPI (also available at CPAN - mirror sites). This module makes available a + mirror sites). This module makes available a DBI-compliant database-handle named $pg_dbh that can be used to perform queries with normal DBI syntax.DBI + - PL/Perl itself presently provides only one additional Perl command: + PL/Perl itself presently provides two additional Perl commands: + + spi_exec_query + in PL/Perl + elog in PL/Perl + spi_exec_query( [ SELECT query [, max_rows]] | [non-SELECT query] ) + + + Here is an example of a SELECT query with the optional maximum +number of rows. + +$rv = spi_exec_query('SELECT * from my_table', 5); + + +This returns up to 5 rows from my_table. + + +If my_table has a column my_column, it would be accessed as + +$foo = $rv->{rows}[$i]->{my_column}; + + + +The number of rows actually returned would be: + +$nrows = @{$rv->{rows}}; + + + +Here is an example using a non-SELECT statement. + +$query = "INSERT INTO my_table VALUES (1, 'test')"; +$rv = spi_exec_query($query); + + +You can then access status (SPI_OK_INSERT, e.g.) like this. + +$res = $rv->{status}; + + + + +To get the rows affected, do: + +$nrows = $rv->{rows}; + + + + + + + elog level, msg @@ -206,6 +236,111 @@ SELECT name, empcomp(employee) FROM employee; + + Data Values in PL/Perl + + + The argument values supplied to a PL/Perl function's code are + simply the input arguments converted to text form (just as if they + had been displayed by a SELECT statement). + Conversely, the return command will accept any string + that is acceptable input format for the function's declared return + type. So, the PL/Perl programmer can manipulate data values as if + they were just text. + + + + PL/Perl can now return rowsets and composite types, and rowsets of +composite types. + + + + Here is an example of a PL/Perl function returning a rowset of a row type: + +CREATE TABLE test ( + i int, + v varchar +); + +INSERT INTO test (i, v) VALUES (1,'first line'); +INSERT INTO test (i, v) VALUES (2,'second line'); +INSERT INTO test (i, v) VALUES (3,'third line'); +INSERT INTO test (i, v) VALUES (4,'immortal'); + +create function test_munge() returns setof test language plperl as $$ + my $res = []; + my $rv = spi_exec_query('select i,v from test;'); + my $status = $rv->{status}; + my $rows = @{$rv->{rows}}; + my $processed = $rv->{processed}; + foreach my $rn (0..$rows-1) { + my $row = $rv->{rows}[$rn]; + $row->{i} += 200 if defined($row->{i}); + $row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v})); + push @$res,$row; + } + return $res; +$$; + +select * from test_munge(); + + + + + Here is an example of a PL/Perl function returning a composite type: + +CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text); + +CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$ + + return {f2 => 'hello', f1 => 1, f3 => 'world'}; + +$$ LANGUAGE plperl; + + + + + Here is an example of a PL/Perl function returning a rowset of a composite type. + +CREATE TYPE testsetperl AS (f1 integer, f2 text, f3 text); + +CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testsetperl AS $$ + return[ + {f1 => 1, f2 => 'hello', f3 => 'world'}, + {f1 => 2, f2 => 'hello', f3 => 'postgres'}, + {f1 => 3, f2 => 'hello', f3 => 'plperl'} + ]; +$$ LANGUAGE plperl; + + + + + Global Values in PL/Perl + + You can use the %_SHARED to store data between function calls. WHY +IS THIS A HASH, AND NOT A HASH REF? + + +For example: + +CREATE OR REPLACE FUNCTION set_var(TEXT) RETURNS TEXT AS $$ + $_SHARED{first} = 'Hello, PL/Perl!'; + return 'ok'; +$$ LANGUAGE plperl; + +CREATE OR REPLACE FUNCTION get_var() RETURNS text AS $$ + return $_SHARED{first}; +$$ LANGUAGE plperl; + +SELECT set_var('hello plperl'); +SELECT get_var(); + + + + + + + Trusted and Untrusted PL/Perl @@ -266,9 +401,69 @@ $$ LANGUAGE plperl; plperlu, execution would succeed. + + PL/Perl Triggers + + + PL/Perl can now be used to write trigger functions using the +$_TD hash reference. + + + + Some useful parts of the $_TD hash reference are: + + +$_TD->{new}{foo} # NEW value of column foo +$_TD->{old}{bar} # OLD value of column bar +$_TD{name} # Name of the trigger being called +$_TD{event} # INSERT, UPDATE, DELETE or UNKNOWN +$_TD{when} # BEFORE, AFTER or UNKNOWN +$_TD{level} # ROW, STATEMENT or UNKNOWN +$_TD{relid} # Relation ID of the table on which the trigger occurred. +$_TD{relname} # Name of the table on which the trigger occurred. +@{$_TD{argv}} # Array of arguments to the trigger function. May be empty. +$_TD{argc} # Number of arguments to the trigger. Why is this here? + + + + + + Triggers can return one of the following: + +return; -- Executes the statement +SKIP; -- Doesn't execute the statement +MODIFY; -- Says it modified a NEW row + + + + +Here is an example of a trigger function, illustrating some of the +above. + +CREATE TABLE test ( + i int, + v varchar +); + +CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$ + if (($_TD->{new}{i}>=100) || ($_TD->{new}{i}<=0)) { + return "SKIP"; # Skip INSERT/UPDATE command + } elsif ($_TD->{new}{v} ne "immortal") { + $_TD->{new}{v} .= "(modified by trigger)"; + return "MODIFY"; # Modify tuple and proceed INSERT/UPDATE command + } else { + return; # Proceed INSERT/UPDATE command + } +$$ LANGUAGE plperl; + +CREATE TRIGGER "test_valid_id_trig" BEFORE INSERT OR UPDATE ON test +FOR EACH ROW EXECUTE PROCEDURE "valid_id"(); + + + - Missing Features + Limitations and Missing Features The following features are currently missing from PL/Perl, but they @@ -278,26 +473,25 @@ $$ LANGUAGE plperl; PL/Perl functions cannot call each other directly (because they - are anonymous subroutines inside Perl). There's presently no - way for them to share global variables, either. + are anonymous subroutines inside Perl). - PL/Perl cannot be used to write trigger - functions.triggerin - PL/Perl + Full SPI is not yet implemented. - - - DBD::PgSPI or similar capability - should be integrated into the standard - PostgreSQL distribution. - + + In the current implementation, if you are fetching or + returning very large datasets, you should be aware that these + will all go into memory. Future features will help with this. + In the meantime, we suggest that you not use pl/perl if you + will fetch or return very large result sets. + + -- 2.40.0