Thread: Use UNKNOWN with PL/Perl spi_prepare()?
Hackers,
I tried this:
try=# create or replace function try() returns void language plperl as $$ spi_prepare('select length($1)',
'unknown'); $$; CREATE FUNCTION try=# select try(); ERROR: error from Perl function "try": failed to find
conversionfunction from unknown to text at line 2.
I could figure out no way to specify an that a data type is unknown (as opposed to known to be "unknown"). Specifying 0
doesn'twork, either:
try=# create or replace function try() returns void language plperl as $$ spi_prepare('select length($1)',
0); $$; CREATE FUNCTION try=# select try(); ERROR: error from Perl function "try": syntax error at or near "0"
atline 2.
Is there a way to do this? If not, should there be?
Thanks,
David
"David E. Wheeler" <david@kineticode.com> writes:
> I tried this:
> try=# create or replace function try() returns void language plperl as $$
> spi_prepare('select length($1)', 'unknown');
> $$;
> CREATE FUNCTION
> try=# select try();
> ERROR: error from Perl function "try": failed to find conversion function from unknown to text at line 2.
Why would you think this is useful, considering that plperl has no
concept of SQL data types? Everything you could pass to
spi_exec_prepared is effectively text, no?
regards, tom lane
On Apr 6, 2010, at 4:40 PM, Tom Lane wrote:
>> I tried this:
>> try=# create or replace function try() returns void language plperl as $$
>> spi_prepare('select length($1)', 'unknown');
>> $$;
>> CREATE FUNCTION
>> try=# select try();
>> ERROR: error from Perl function "try": failed to find conversion function from unknown to text at line 2.
>
> Why would you think this is useful, considering that plperl has no
> concept of SQL data types? Everything you could pass to
> spi_exec_prepared is effectively text, no?
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
notexist at line 2.
Best,
David
"David E. Wheeler" <david@kineticode.com> writes:
> On Apr 6, 2010, at 4:40 PM, Tom Lane wrote:
>> Why would you think this is useful, considering that plperl has no
>> concept of SQL data types? Everything you could pass to
>> spi_exec_prepared is effectively text, no?
> 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?
regards, tom lane
David E. Wheeler wrote:
> On Apr 6, 2010, at 4:40 PM, Tom Lane wrote:
>
>
>>> I tried this:
>>> try=# create or replace function try() returns void language plperl as $$
>>> spi_prepare('select length($1)', 'unknown');
>>> $$;
>>> CREATE FUNCTION
>>> try=# select try();
>>> ERROR: error from Perl function "try": failed to find conversion function from unknown to text at line 2.
>>>
>> Why would you think this is useful, considering that plperl has no
>> concept of SQL data types? Everything you could pass to
>> spi_exec_prepared is effectively text, no?
>>
>
> 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.
>
>
>
Indeed it doesn't. But (as documented) the argument will be passed
*from* *perl* as text and converted to the specified type in the glue
code. See plperl.c for details.
cheers
andrew
On Apr 6, 2010, at 4:55 PM, Andrew Dunstan wrote: > Indeed it doesn't. But (as documented) the argument will be passed *from* *perl* as text and converted to the specifiedtype in the glue code. See plperl.c for details. Hrm. If the type is unknown, I think it should just be passed as a string and left to the back end to figure out. Best, David
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
whatwill 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
"David E. Wheeler" <david@kineticode.com> writes:
> 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 = ?';
Somehow, emulating the above doesn't attract me nearly as much as it
evidently does you. What happens in cases where the parser is unable
to infer a data type, or infers the wrong one? Or even if it does
infer a type, how do you find out what it inferred? I'm not excited
about building a capability that fails to address any except the simplest
use case.
regards, tom lane
David E. Wheeler wrote: > 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. > It has been suggested in the past that we should have a full DBD interface in plperl, and indeed there was such an animal at one stage, although it was not compatibly licensed, and it is probably now way out of date. The current plperl interface back into the database is a *much* thinner veneer over SPI than a DBD interface would be. Someone could start a project for a DBD interface for plperl, but it would be a non-trivial amount of work, and it should probably not be done piecemeal. cheers andrew
On Apr 6, 2010, at 5:06 PM, Tom Lane wrote: > Somehow, emulating the above doesn't attract me nearly as much as it > evidently does you. What happens in cases where the parser is unable > to infer a data type, or infers the wrong one? An exception should be thrown. > Or even if it does > infer a type, how do you find out what it inferred? Since I'm fetching the data from PL/Perl, I don't really care. > I'm not excited > about building a capability that fails to address any except the simplest > use case. It will save me a ton of duplicate code. Best, David
On Apr 6, 2010, at 5:23 PM, Andrew Dunstan wrote: > It has been suggested in the past that we should have a full DBD interface in plperl, and indeed there was such an animalat one stage, although it was not compatibly licensed, and it is probably now way out of date. > > The current plperl interface back into the database is a *much* thinner veneer over SPI than a DBD interface would be.Someone could start a project for a DBD interface for plperl, but it would be a non-trivial amount of work, and it shouldprobably not be done piecemeal. That would be great, but my current need is far simpler than that. Best, David