From: | "David E(dot) Wheeler" <david(at)kineticode(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL-development Hackers <pgsql-hackers(at)postgresql(dot)org>, Tim Bunce <Tim(dot)Bunce(at)pobox(dot)com> |
Subject: | Re: Use UNKNOWN with PL/Perl spi_prepare()? |
Date: | 2010-04-07 00:01:49 |
Message-ID: | E97E1E85-9F43-43D0-B81D-38EA2C6FACC0@kineticode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Apr 6, 2010, at 4:54 PM, Tom Lane wrote:
>> try=# create or replace function try() returns void language plperl as $$
>> spi_prepare('select abs($1)', 'text');
>> $$;
>> CREATE FUNCTION
>> try=# select try();
>> ERROR: error from Perl function "try": function abs(text) does not exist at line 2.
>
> Well, yes; what's your point? How would you actually *use* this if you
> had it? In particular what do you see yourself passing to the eventual
> exec call?
Yes, I would use unknown, because as you said, in Perl the types of values are unknown.
DBD::Pg makes extensive use of unknown for prepares. If I do
my $sth = $dbh->prepare('SELECT foo FROM bar WHERE baz = ?');
DBD::Pg effectively sends:
PREPARE dbdpg_1(unknown) AS SELECT from FROM bar WHERE baz = ?';
I'd love to be able to do the same from PL/Perl.
Specifically, I'm writing a utility function that will be used by other PL/Perl code, and that function doesn't know what will be passed to it. It looks like this:
$_SHARED{select_row} = sub {
my $query = shift;
if (@_) {
my $plan = spi_prepare($query, ('unknown') x @_ );
return spi_exec_prepared($plan, @_)->{rows}[0];
} else {
return spi_exec_query($query, 1)->{rows}[0];
}
};
It might be called without params:
my $time = $_SHARED{select_row}->('SELECT now()')->{now};
Or with text params:
my $len = $_SHARED{select_row}->(
'SELECT length($1)', 'foo'
)->{length};
Or with any other type of params:
my $abs = $_SHARED{select_row}->(
'SELECT abs($1)', -42
)->{abs};
It needs not to care.
Best,
David
From | Date | Subject | |
---|---|---|---|
Next Message | David E. Wheeler | 2010-04-07 00:02:25 | Re: Use UNKNOWN with PL/Perl spi_prepare()? |
Previous Message | Tom Lane | 2010-04-07 00:00:48 | Re: Proposal: Add JSON support |