From 7a55ba76151db6baf8760e035468f95b77b73153 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Wed, 21 Jul 2004 20:34:50 +0000 Subject: Back out pg_autovacuum commit after cvs clean failure causes commit. --- doc/src/sgml/plperl.sgml | 322 ++++++++++------------------------------------- 1 file changed, 64 insertions(+), 258 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml index 4d28bd2d984..8c77b65e31e 100644 --- a/doc/src/sgml/plperl.sgml +++ b/doc/src/sgml/plperl.sgml @@ -1,5 +1,5 @@ @@ -34,10 +34,9 @@ $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.24 2004/07/21 20:22:57 momjian E Users of source packages must specially enable the build of - PL/Perl during the installation process. (Refer to for more information.) Users of - binary packages might find PL/Perl in a separate subpackage. - + 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. @@ -55,7 +54,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 legacy single quote + dollar quoting (as shown above), or via the usual single quote syntax (see for more information). @@ -80,22 +79,19 @@ $$ 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 $$ @@ -112,9 +108,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. @@ -131,7 +127,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; @@ -139,9 +135,35 @@ SELECT name, empcomp(employee) FROM employee; - There is now support for returning a composite-type result value. + There is currently no 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. + @@ -149,77 +171,25 @@ SELECT name, empcomp(employee) FROM employee; Access to the database itself from your Perl function can be done via - spi_exec_query, or 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 two additional Perl commands: + PL/Perl itself presently provides only one additional Perl command: - - 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 @@ -236,111 +206,6 @@ $nrows = $rv->{rows}; - - 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 @@ -401,69 +266,9 @@ $$ 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"(); - - - - Limitations and Missing Features + Missing Features The following features are currently missing from PL/Perl, but they @@ -473,25 +278,26 @@ FOR EACH ROW EXECUTE PROCEDURE "valid_id"(); PL/Perl functions cannot call each other directly (because they - are anonymous subroutines inside Perl). + are anonymous subroutines inside Perl). There's presently no + way for them to share global variables, either. - Full SPI is not yet implemented. + PL/Perl cannot be used to write trigger + functions.triggerin + PL/Perl + - - 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. - + + DBD::PgSPI or similar capability + should be integrated into the standard + PostgreSQL distribution. + - -- cgit v1.2.3