Using More Perl in PostgreSQL
by Andrew Dunstan01/19/2006
The first article in this series examined the use of PL/Perl to create triggers. The trigger inserted a row into a database table for audit purposes using a new PL/Perl method called spi_exec_query(). This article looks in more detail at uses of that function and its new cousin, as well as other features for handling bulk data and composite types.
Getting Data out of the Database and Issuing Database Commands
Pl/Perl now allows the programmer to run an arbitrary query against the database. The query can be any statement that PostgreSQL will recognize as legal SQL. The mechanism used is PostgreSQL's Server Programming Interface, an API that makes it relatively easy for modules to run database commands. While SPI can do more than connect to programming language modules, this is probably its principal use.
Most of the SPI is at a level too low to make it appropriate to expose to the user in PL/Perl. Instead, the glue code does most of the work for you. PostgreSQL 8.0 therefore provides only one function to allow you to talk to the database: spi_exec_query().
This function takes either one or two arguments. The first is a string containing the SQL that you want the database to run. The second is a limit on the number of rows returned. This argument is always optional and makes sense only if the SQL statement is a SELECT. You can always put a LIMIT clause on the SQL statement anyway, so I always just leave it off.
A common request is for the ability to grant select on all tables. That's easy enough in PL/Perl. The function attached to this article will do it all nicely for you. The function takes three arguments: a schema name, a grantee name, and a set of privileges as a string. Inside, it runs a query that gets a list of all the table, view, and sequence names in the named schema that belong to the person running the function. It then iterates over that list, granting the set of specified privileges on each object to the grantee.
In case you think making up such a function was hard, because the query to get the list of objects is a bit complex, I have a confession to make: I cheated. The PostgreSQL command-line client psql has an extremely nifty mode in which it will tell you the SQL that it runs to execute its slash commands. I asked it to show me the SQL that corresponded to the \d command, cut and pasted the result into my query, and adjusted it slightly.
Having created the function, you can use it any time you like. Just call it in a select statement:
select grantall('public','Fred','select');and the user Fred will get select privileges on all tables, views, and sequences in the public schema that you own.
Note: version 8.1 of PostgreSQL has an enhanced privileges regime, and you will probably need a slight modification of the function. This version works with version 8.0 of PostgreSQL.
Now dig a little deeper inside the function. Each query to run is a plain Perl string with some SQL inside. Pass that as the sole argument to spi_exec_query(), and it returns a hashref. The hashref has three possible members. The one called status is always present. It's a string, corresponding to the names of the SPI return code, as set out in the SPI section of the PostgreSQL documentation. Usually, if the status starts with SPI_OK_, the command worked. If not, it failed. There are several SPI_OK_ return codes. You can either look them up, or do what I did and do a regular expression match against the beginning of the string.
If the SQL statement processed any table rows, then an additional member, processed, is present with a count of the rows. Finally, if the query was a SELECT, then there is an additional member called rows, which is an arrayref. The contents of the array are hashrefs, one per row returned by the query. The keys of each hash are the column names, and the values are the column values for the row.
No matter the data type of the column returned by the query, the value is set in the row hash as a string. This is true if it's a date, a record, an array, or any other type--even numeric types. This can be inconvenient. Either you need to get the data from PostgreSQL in a form that Perl can digest, or you might need to get Perl to parse the strings. Luckily, Perl is good at parsing strings. One of the items on the TODO list for version 8.2 of PostgreSQL is to have row values of composite types returned to PL/Perl as arrayrefs or hashrefs, corresponding to PostgreSQL's row and array types.
Pages: 1, 2 |
