diff options
| -rw-r--r-- | doc/src/sgml/plperl.sgml | 322 |
1 files changed, 258 insertions, 64 deletions
diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml index 8c77b65e31e..cc24755615a 100644 --- a/doc/src/sgml/plperl.sgml +++ b/doc/src/sgml/plperl.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.25 2004/07/21 20:34:43 momjian Exp $ +$PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.26 2004/07/21 20:44:52 momjian Exp $ --> <chapter id="plperl"> @@ -34,9 +34,10 @@ $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.25 2004/07/21 20:34:43 momjian E <note> <para> 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 <xref + linkend="install-short"> for more information.) Users of + binary packages might find PL/Perl in a separate subpackage. + </para> </note> @@ -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 <productname>PostgreSQL</productname>, 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 <xref linkend="sql-syntax-strings"> for more information). </para> @@ -79,19 +80,22 @@ $$ LANGUAGE plperl; </para> <para> - If an SQL null value<indexterm><primary>null value</><secondary - sortas="PL/Perl">in PL/Perl</></indexterm> is passed to a function, - the argument value will appear as <quote>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 <literal>STRICT</> to the function definition to make - <productname>PostgreSQL</productname> 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 <function>perl_max</function> with - one null and one non-null argument to return the non-null argument, - rather than a null value: + If an SQL <literal>NULL</literal> value<indexterm><primary>null + value</><secondary sortas="PL/Perl">in PL/Perl</></indexterm> is + passed to a function, the argument value will appear as + <quote>undefined</> in Perl. The above function definition will not + behave very nicely with <literal>NULL</literal> inputs (in fact, it + will act as though they are zeroes). We could add <literal>STRICT</> + to the function definition to make + <productname>PostgreSQL</productname> do something more reasonable: if + a <literal>NULL</literal> value is passed, the function will not be + called at all, but will just return a <literal>NULL</literal> result + automatically. Alternatively, we could check for undefined inputs in + the function body. For example, suppose that we wanted + <function>perl_max</function> with one <literal>NULL</literal> and one + non-<literal>NULL</literal> argument to return the + non-<literal>NULL</literal> argument, rather than a + <literal>NULL</literal> value: <programlisting> CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$ @@ -108,9 +112,9 @@ $$ LANGUAGE plperl; </para> <para> - 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 <literal>NULL</literal> value from + a PL/Perl function, return an undefined value. This can be done + whether the function is strict or not. </para> <para> @@ -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; </para> <para> - There is currently no support for returning a composite-type result - value. + There is now support for returning a composite-type result value. </para> - <tip> - <para> - Because the function body is passed as an SQL string literal to - <command>CREATE FUNCTION</command>, 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 - (<literal>q[]</literal>, <literal>qq[]</literal>, - <literal>qw[]</literal>). - </para> - </tip> - </sect1> - - <sect1 id="plperl-data"> - <title>Data Values in PL/Perl</title> - - <para> - 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 <command>SELECT</command> statement). - Conversely, the <literal>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. - </para> </sect1> <sect1 id="plperl-database"> @@ -171,25 +149,77 @@ SELECT name, empcomp(employee) FROM employee; <para> Access to the database itself from your Perl function can be done via - an experimental module <ulink + spi_exec_query, or via an experimental module <ulink url="http://www.cpan.org/modules/by-module/DBD/APILOS/"><literal>DBD::PgSPI</literal></ulink> (also available at <ulink url="http://www.cpan.org/SITES.html"><acronym>CPAN</> - mirror sites</ulink>). This module makes available a + mirror sites</ulink>). This module makes available a <acronym>DBI</>-compliant database-handle named <varname>$pg_dbh</varname> that can be used to perform queries with normal <acronym>DBI</> syntax.<indexterm><primary>DBI</></indexterm> + </para> <para> - PL/Perl itself presently provides only one additional Perl command: + PL/Perl itself presently provides two additional Perl commands: <variablelist> <varlistentry> <indexterm> + <primary>spi_exec_query</primary> + <secondary>in PL/Perl</secondary> + </indexterm> + <indexterm> <primary>elog</primary> <secondary>in PL/Perl</secondary> </indexterm> + <term><function>spi_exec_query(</> [ <replaceable>SELECT query</replaceable> [, <replaceable>max_rows</replaceable>]] | [<replaceable>non-SELECT query</replaceable>] ) </term> + <listitem> + <para> + Here is an example of a SELECT query with the optional maximum +number of rows. +<programlisting> +$rv = spi_exec_query('SELECT * from my_table', 5); +</programlisting> + +This returns up to 5 rows from my_table. + </para> + <para> +If my_table has a column my_column, it would be accessed as +<programlisting> +$foo = $rv->{rows}[$i]->{my_column}; +</programlisting> + </para> + <para> +The number of rows actually returned would be: +<programlisting> +$nrows = @{$rv->{rows}}; +</programlisting> + </para> + <para> +Here is an example using a non-SELECT statement. +<programlisting> +$query = "INSERT INTO my_table VALUES (1, 'test')"; +$rv = spi_exec_query($query); +</programlisting> + +You can then access status (SPI_OK_INSERT, e.g.) like this. +<programlisting> +$res = $rv->{status}; +</programlisting> + + </para> + <para> +To get the rows affected, do: +<programlisting> +$nrows = $rv->{rows}; +</programlisting> + </para> + + </listitem> + + </varlistentry> + <varlistentry> <term><function>elog</> <replaceable>level</replaceable>, <replaceable>msg</replaceable></term> <listitem> <para> @@ -206,6 +236,111 @@ SELECT name, empcomp(employee) FROM employee; </para> </sect1> + <sect1 id="plperl-data"> + <title>Data Values in PL/Perl</title> + + <para> + 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 <command>SELECT</command> statement). + Conversely, the <literal>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. + </para> + + <para> + PL/Perl can now return rowsets and composite types, and rowsets of +composite types. + </para> + + <para> + Here is an example of a PL/Perl function returning a rowset of a row type: +<programlisting> +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(); +</programlisting> + </para> + + <para> + Here is an example of a PL/Perl function returning a composite type: + <programlisting> +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; + </programlisting> + </para> + + <para> + Here is an example of a PL/Perl function returning a rowset of a composite type. + <programlisting> +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; + </programlisting> + </para> + </sect1> + <sect1 id="plperl-global"> + <title>Global Values in PL/Perl</title> + <para> + You can use the %_SHARED to store data between function calls. WHY +IS THIS A HASH, AND NOT A HASH REF? + </para> + <para> +For example: +<programlisting> +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(); +</programlisting> + + </para> + + + </sect1> + <sect1 id="plperl-trusted"> <title>Trusted and Untrusted PL/Perl</title> @@ -266,9 +401,69 @@ $$ LANGUAGE plperl; <literal>plperlu</>, execution would succeed. </para> </sect1> + <sect1 id="plperl-triggers"> + <title>PL/Perl Triggers</title> + + <para> + PL/Perl can now be used to write trigger functions using the +<varname>$_TD</varname> hash reference. + </para> + + <para> + Some useful parts of the $_TD hash reference are: + +<programlisting> +$_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? +</programlisting> + + </para> + + <para> + Triggers can return one of the following: +<programlisting> +return; -- Executes the statement +SKIP; -- Doesn't execute the statement +MODIFY; -- Says it modified a NEW row +</programlisting> + </para> + + <para> +Here is an example of a trigger function, illustrating some of the +above. +<programlisting> +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"(); +</programlisting> + </para> + </sect1> <sect1 id="plperl-missing"> - <title>Missing Features</title> + <title>Limitations and Missing Features</title> <para> The following features are currently missing from PL/Perl, but they @@ -278,26 +473,25 @@ $$ LANGUAGE plperl; <listitem> <para> 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). </para> </listitem> <listitem> <para> - PL/Perl cannot be used to write trigger - functions.<indexterm><primary>trigger</><secondary>in - PL/Perl</></indexterm> + <application>Full SPI</application> is not yet implemented. </para> </listitem> - <listitem> - <para> - <application>DBD::PgSPI</applicatioN> or similar capability - should be integrated into the standard - <productname>PostgreSQL</productname> distribution. - </para> + <para> + 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. + </para> </listitem> + </itemizedlist> </para> </sect1> |