Database Reference
In-Depth Information
$a= $a + @_[0] + @_[1] + @_[2];
}
return $a;
$$ LANGUAGE plperl;
The result can be seen using the following statement:
warehouse_db=# SELECT perl_arguments(2,3,4);
perl_arguments
----------------
28
(1 row)
Accessing the database with PL/Perl
We will access the database using PL/Perl's
spi_exec_query
function. Suppose we
want to retrieve one row from the
warehouse_tbl
table. Let's dig the code for this
problem statement.
PL/Perl functions can return the result set of scalar and
composite types.
The
getRecords_withperl()
function returns
SETOF
warehouse_tbl
using the
spi_exec_query()
function. The
rownumber
variable is used to reference the irst
row from corresponding rows in the result. The
return_next
query is used to return
the rows one by one, though only one in the following case:
warehouse_db=# CREATE OR REPLACE FUNCTION getRecords_withperl()
RETURNS SETOF warehouse_tbl as $$
$tblrow = spi_exec_query('SELECT warehouse_id, warehouse_name,
state FROM warehouse_tbl ;');
$rownumber = 0;
$row = $tblrow->{rows}[$rownumber];
return_next( $row );
$$ LANGUAGE plperl;
Let's execute the function in
psql
in the following manner:
warehouse_db=# SELECT warehouse_id, warehouse_name, state FROM
getRecords_withperl();
warehouse_id | warehouse_name | state
--------------+----------------+------
1 | Mark Corp | CT
(1 row)