DBD::Pg - function declaration and using alias $n

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

I'm trying to create a function (stored procedure) on PostgreSQL using DBI, but I'm hitting a roadblock because DBD::Pg apparently recognizes $1, $2, $3 ... as placeholder variables.

For example, the following code produces an error:

  $dbh->do(q{
    CREATE FUNCTION poo (INTEGER) RETURNS INTEGER AS $func$
    DECLARE
      val ALIAS FOR $1;
    BEGIN
      RETURN val * 2;
    END
  });

  # Error:
  DBD::Pg::db do failed: execute called with an unbound placeholder [f
+or Statement "
    CREATE FUNCTION poo (INTEGER) RETURNS INTEGER AS $func$
    DECLARE
        val ALIAS FOR $1;
    BEGIN
        RETURN val * 2;
    END
"]
[download]

$1 is apparently being recognized as a placeholder, but it's an alias. Is there anyway to circumvent this ??

Comment on DBD::Pg - function declaration and using alias $n Download Code

Replies are listed 'Best First'.
Re: DBD::Pg - function declaration and using alias $n by Thilosophy (Curate) on Feb 15, 2005 at 08:19 UTC
Slightly off-topic and a shameless (?) plug, but once you have managed to create your stored procedure, please have a look at DBIx::ProcedureCall, which creates Perl wrappers for stored procedures, eliminating the need for writing SQL by hand. I just added PostgreSQL support yesterday, and am looking for testers. With it you could call your function like so `use DBIx::ProcedureCall qw( poo ); my $result = poo ($dbh, 12345);` [download] As for your problem, I can only offer a work-around: Create the function with the psql command line tool (not with Perl/DBI). You only have to do this once (when installing your program), right?	[reply] [d/l]
Re^2: DBD::Pg - function declaration and using alias $n by diotalevi (Canon) on Feb 15, 2005 at 21:01 UTC
Those functions might be nicer if they live in their own application specific namespace. Consider this. `$dbh->My::App::poo( 12345 ); package My::App; use DBIx::ProcedureCall qw( poo );` [download]	[reply] [d/l]
Re^3: DBD::Pg - function declaration and using alias $n by Thilosophy (Curate) on Feb 16, 2005 at 05:45 UTC
Those functions might be nicer if they live in their own application specific namespace. The code you gave above should work, actually. Thanks for pointing out the interesting syntax. `$dbh->My::App::poo( 12345 );` [download] I had no idea this works (calling an object with a fully qualified "method" name). I am going to mention this pattern in the module's docs.	[reply] [d/l]
Re^4: DBD::Pg - function declaration and using alias $n by diotalevi (Canon) on Feb 17, 2005 at 22:34 UTC
Re: DBD::Pg - function declaration and using alias $n by diotalevi (Canon) on Feb 15, 2005 at 21:04 UTC
See DBD::Pg and the `$dbh->func( ... 'putline' )` function. This will allow you to send arbitrary text to the PostgreSQL server. `$dbh->func( q{ CREATE FUNCTION poo (INTEGER) RETURNS INTEGER AS $func$ DECLARE val ALIAS FOR $1; BEGIN RETURN val * 2; END } => 'putline' )` [download]	[reply] [d/l] [select]