diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/plperl.sgml | 322 |
1 files changed, 64 insertions, 258 deletions
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 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.24 2004/07/21 20:22:57 momjian Exp $ +$PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.25 2004/07/21 20:34:43 momjian Exp $ --> <chapter id="plperl"> @@ -34,10 +34,9 @@ $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.24 2004/07/21 20:22:57 momjian E <note> <para> Users of source packages must specially enable the build of - 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. - + 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. </para> </note> @@ -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 <productname>PostgreSQL</productname>, 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 <xref linkend="sql-syntax-strings"> for more information). </para> @@ -80,22 +79,19 @@ $$ LANGUAGE plperl; </para> <para> - 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: + 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: <programlisting> CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$ @@ -112,9 +108,9 @@ $$ LANGUAGE plperl; </para> <para> - 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. + 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. </para> <para> @@ -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; </para> <para> - There is now support for returning a composite-type result value. + There is currently no 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"> @@ -149,77 +171,25 @@ SELECT name, empcomp(employee) FROM employee; <para> Access to the database itself from your Perl function can be done via - spi_exec_query, or via an experimental module <ulink + 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 two additional Perl commands: + PL/Perl itself presently provides only one additional Perl command: <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> @@ -236,111 +206,6 @@ $nrows = $rv->{rows}; </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> @@ -401,69 +266,9 @@ $$ 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>Limitations and Missing Features</title> + <title>Missing Features</title> <para> The following features are currently missing from PL/Perl, but they @@ -473,25 +278,26 @@ FOR EACH ROW EXECUTE PROCEDURE "valid_id"(); <listitem> <para> 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. </para> </listitem> <listitem> <para> - <application>Full SPI</application> is not yet implemented. + PL/Perl cannot be used to write trigger + functions.<indexterm><primary>trigger</><secondary>in + PL/Perl</></indexterm> </para> </listitem> + <listitem> - <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> + <para> + <application>DBD::PgSPI</applicatioN> or similar capability + should be integrated into the standard + <productname>PostgreSQL</productname> distribution. + </para> </listitem> - </itemizedlist> </para> </sect1> |