AS makes your pl/perlu functions easier to use

Related Ads

I write a number of functions over my database using functions that begin something like the following:

CREATE OR REPLACE FUNCTION foo (varchar(1024)) RETURNS varchar(1024) AS $$

This is all well and good, except it makes it annoying to call them from other scripts. I end up writing a SELECT statement like the following:

SELECT * FROM foo('string');

But, what's not obvious at first, but which makes this annoying is that the column returned has no name! Sure, I could just reference the 0th column in the returned row, but that's not exceptionally clear to a reader of my code. Instead, if I change this, ever-so-slightly, I can improve readability dramatically. I simply add an AS to the end of my SELECT, instructing Postgres to name the column whatever follows. Here's the modified example:

SELECT * FROM foo('string') AS column_name;

Now, in Perl, I can just reference that column using its name. It's very convenient, as the pl/perl code snippet below demonstrates:

my $query =
  "SELECT * FROM foo('string') AS column_name";
my $result = spi_exec_query($query);

if(defined $result->{rows}[0]) {
  elog(INFO, $result->{rows}[$i]->{column_name});
}