Roland684 has asked for the wisdom of the Perl Monks concerning the following question:

I created a postgresql table with a trigger that fills a fields value during insert/update. Postgress has the neat possibility to return that value directly by appending 'RETURNING <fieldname>' to the query. So 
INSERT INTO table (name) VALUES ("Roland684");
SELECT id FROM table WHERE name = "Roland684";

[download]
can be done in one single (and safer) statement like this: 
INSERT INTO table (name) VALUES ("Roland684") RETURNING id;

[download]
Nice!

I've also written a general purpose subroutine that handles SQL-queries, passed to it as a string. And this combination is where some wisdom is required.

How can I check if there are any values returned?

When I execute a query without the RETURNING clause, $sth->fetch will produce the warning message "DBD::Pg::st fetchrow_hashref failed: no statement executing".

While the following code works for both insert/update statements with the returning clause and select statements, even when no records are returned, it fails for insert/update statement without the RETURNING clause. 

my $sth = $dbh->prepare($query);
my $insertedrecords = $sth->execute;
while (my $result = $sth->fetchrow_hashref) {
  ...
}

[download]
(and with 'fails' I mean that it works correctly, but also produces an warning.)

I could check the sql query for the existence of the sub-string " RETURNING ", but we can all imaging when this will fail. Properly parsing the query seems too far fetched, there has to be a simpler way.

Replies are listed 'Best First'.
Re: How to detect postgresql RETURNING usage
by ikegami (Patriarch) on Sep 06, 2010 at 17:18 UTC

    That doesn't make much sense.

    If you need values back, you'll use a query that returns the values you need (i.e. with RETURNING), and you'll collect them. 

    my $sth = $dbh->prepare($query);
$sth->execute;
while (my $row = $sth->fetchrow_hashref) {
  ...
}

    [download]

    If you don't need values back, you won't use a query that returns values you need (i.e. without RETURNING), and you won't collect them. 

    my $sth = $dbh->prepare($query);
$sth->execute;

    [download]

    I don't see the ambiguity unless you're writing yet another bad DBI wrapper. I hate to mention this since I fear it will get misused, but I think you can check for the presence of data by checking if the statement handle is Active.

[reply]
[d/l]
[select]
      I could build separate subroutines for every type of query/return value possible, and although probably the most correct thing to do, this also makes the most complex interface.

      E.G. The query entry interface of many database management systems, like phpmyadmin or PGadmin3, don't have separate input fields for separate query types either.

      I've made a single general purpose routine that send the query to the database and returns the most useful thing the database throws back at it, being a record-set (with 0..n elements), the autoinsertID (mysql only) or the amount of rows affected.

      And although I know this is not the most elegant thing, it is very, very simple to use and suits 100% of the use cases I have met last year.

      Anyway, you were right: I have used the Active attribute to fix my issue (for now), rather than take a different approach altogether. I thank you for mentioning it.

[reply]

        I've made a single general purpose routine that send the query to the database and returns the most useful thing the database throws back at it, being a record-set (with 0..n elements), the autoinsertID (mysql only) or the amount of rows affected.

        The function performs three very different tasks. You shouldn't have to find and mentally parse the SQL query to find out what the function returns. Contrary to what you said, that makes the interface more complex, not simpler.

        and suits 100% of the use cases I have met last year.

        As indicated by your reply, it's complexity being discussed, not functionality. Highly polymorphic functions are more complex and less elegant than something more straightforward.

        The query entry interface of many database management systems, like phpmyadmin or PGadmin3, don't have separate input fields for separate query types either.

        They provide a UI, not an API. Which are you building?

[reply]
Re: How to detect postgresql RETURNING usage
by moritz (Cardinal) on Sep 06, 2010 at 17:41 UTC
    How can I check if there are any values returned?

    The calling code needs to know anyway if it wants to do something the returned values, if anything. Why not make it also indicate whether it wants results or not?

    (and with 'fails' I mean that it works correctly, but also produces an warning.)

    warnings can be suppressed. If the warn builtin is used under the hood, a local $SIG{__WARN__} = sub { }; should help. If not, I'm sure the DBI documentation has something to say about error and warning reporting.

    Perl 6 - links to (nearly) everything that is Perl 6.
[reply]
[d/l]
      PrintWarn controls the issue of warnings from DBI.
[reply]
[d/l]
Re: How to detect postgresql RETURNING usage
by bart (Canon) on Sep 08, 2010 at 13:56 UTC
    I could check the sql query for the existence of the sub-string " RETURNING ", but we can all imaging when this will fail.
    You should not test whether the query contains the word "RETURNING", but if it contains the token "RETURNING".

    You can test that by properly tokenizing the string and see if one of the tokens is the word "RETURNING".

    One practical approach for tokenizing (or "lexing") SQL queries has been described by Ovid in his perl.com article "lexing your data".

    To speed it up when repeated often, you might reconsider caching ("memoizing") the results for the tokenization, just like DBIx::Simple does for caching SQL prepared statement handles: basically, you use a hash with the SQL statement string as the hash key.

[reply]
[d/l]

Back to Seekers of Perl Wisdom