Обсуждение: What's wrong in this pltcl function ?
create function ruldeb(bpchar) returns bpchar as ' set cont $1 set rulaj 0.0 spi_exec -array rec "select valoare
fromvalori where debitor LIKE
\'$cont%\'" { set rulaj [expr {$rulaj + $rec(valoare)}] } if {![info exists GD(conturi_lookup)]} { set
GD(conturi_lookup)[spi_prepare "select cheie,denumire from
conturi where id=\'\\$1\'" [list bpchar]] } spi_execp -count 1 $GD(conturi_lookup) [list $cont] return "{$cheie}
{$denumire}$rulaj"
' LANGUAGE 'pltcl';
is giving the following error:
ERROR: pltcl: can't read "cheie": no such variable
can't read "cheie": no such variable while executing
"return "{$cheie} {$denumire} $rulaj"" (procedure "__PLTcl_proc_1759991" line 12) invoked from within
"__PLTcl_proc_1759991 4:0:1:1:2:"
------------------
Please, could someone give me a clue?
thanks in advance,
Constantin Teodorescu
Braila, ROMANIA
On Mon, 14 Oct 2002, Constantin Teodorescu wrote:
> create function ruldeb(bpchar) returns bpchar as '
> set cont $1
> set rulaj 0.0
> spi_exec -array rec "select valoare from valori where debitor LIKE
> \'$cont%\'" {
> set rulaj [expr {$rulaj + $rec(valoare)}]
> }
> if {![info exists GD(conturi_lookup)]} {
> set GD(conturi_lookup) [spi_prepare "select cheie,denumire from
> conturi where id=\'\\$1\'" [list bpchar]]
> }
> spi_execp -count 1 $GD(conturi_lookup) [list $cont]
> return "{$cheie} {$denumire} $rulaj"
> ' LANGUAGE 'pltcl';
>
>
> is giving the following error:
>
> ERROR: pltcl: can't read "cheie": no such variable
> can't read "cheie": no such variable
> while executing
> "return "{$cheie} {$denumire} $rulaj""
> (procedure "__PLTcl_proc_1759991" line 12)
> invoked from within
> "__PLTcl_proc_1759991 4:0:1:1:2:"
Looks like your prepared query is not finding any matching tuples.
If a query returns 0 tuples then all column variables will be undefined.
Undefined because if you set them before then they will not be unset. This
differs from the case where a tuple has been returned by the query but a
column's value is NULL. In this case the TCL variable is unset.
--
Nigel J. Andrews
Constantin Teodorescu <teo@flex.ro> writes:
> create function ruldeb(bpchar) returns bpchar as '
> set cont $1
> set rulaj 0.0
> spi_exec -array rec "select valoare from valori where debitor LIKE
> \'$cont%\'" {
> set rulaj [expr {$rulaj + $rec(valoare)}]
> }
> if {![info exists GD(conturi_lookup)]} {
> set GD(conturi_lookup) [spi_prepare "select cheie,denumire from
> conturi where id=\'\\$1\'" [list bpchar]]
> }
> spi_execp -count 1 $GD(conturi_lookup) [list $cont]
> return "{$cheie} {$denumire} $rulaj"
> ' LANGUAGE 'pltcl';
> is giving the following error:
> ERROR: pltcl: can't read "cheie": no such variable
> can't read "cheie": no such variable
I think what is happening is that the select is returning zero rows, and
so none of the output variables get set. You should be checking that
spi_execp returns a value greater than 0 before trying to use the column
variables.
As for *why* the select returns zero rows, I think you want the query
to read like... where id=\\$1"
As is, it's always looking for the literal id value $1.
regards, tom lane
Tom Lane wrote:
>Constantin Teodorescu <teo@flex.ro> writes:
>
>
>>create function ruldeb(bpchar) returns bpchar as '
>> set cont $1
>> set rulaj 0.0
>> spi_exec -array rec "select valoare from valori where debitor LIKE
>>\'$cont%\'" {
>> set rulaj [expr {$rulaj + $rec(valoare)}]
>> }
>> if {![info exists GD(conturi_lookup)]} {
>> set GD(conturi_lookup) [spi_prepare "select cheie,denumire from
>>conturi where id=\'\\$1\'" [list bpchar]]
>> }
>> spi_execp -count 1 $GD(conturi_lookup) [list $cont]
>> return "{$cheie} {$denumire} $rulaj"
>>' LANGUAGE 'pltcl';
>>
>>
>
>
>
>
>>is giving the following error:
>>
>>
>
>
>
>>ERROR: pltcl: can't read "cheie": no such variable
>>can't read "cheie": no such variable
>>
>>
>
>I think what is happening is that the select is returning zero rows, and
>so none of the output variables get set. You should be checking that
>spi_execp returns a value greater than 0 before trying to use the column
>variables.
>
>As for *why* the select returns zero rows, I think you want the query
>to read like
> ... where id=\\$1"
>As is, it's always looking for the literal id value $1.
>
>
I have replaced:
... where id=\'\\$1\' with
... where id=\\$1
and the error is now:
ERROR: Unable to identify an operator '=$' for types 'character
varying' and 'integer' You will have to retype this query using an explicit cast
when I call the function like that:
select ruldeb('4:0:1:1:2:');
-------------
I have to say that that account '4:0:1:1:2:' exists in the "conturi" table!
As I said it previously, the query works fine directly (spi_exec)
without the preparing stuff!
teo
Constantin Teodorescu wrote:
> Tom Lane wrote:
>
>> Constantin Teodorescu <teo@flex.ro> writes:
>>
>>
>>> create function ruldeb(bpchar) returns bpchar as '
>>> set cont $1
>>> set rulaj 0.0
>>> spi_exec -array rec "select valoare from valori where debitor
>>> LIKE \'$cont%\'" {
>>> set rulaj [expr {$rulaj + $rec(valoare)}]
>>> }
>>> if {![info exists GD(conturi_lookup)]} {
>>> set GD(conturi_lookup) [spi_prepare "select cheie,denumire
>>> from conturi where id=\'\\$1\'" [list bpchar]]
>>> }
>>> spi_execp -count 1 $GD(conturi_lookup) [list $cont]
>>> return "{$cheie} {$denumire} $rulaj"
>>> ' LANGUAGE 'pltcl';
>>>
>>
>>
>>
>>
>>
>>> is giving the following error:
>>>
>>
>>
>>
>>
>>> ERROR: pltcl: can't read "cheie": no such variable
>>> can't read "cheie": no such variable
>>>
>>
>>
>> I think what is happening is that the select is returning zero rows, and
>> so none of the output variables get set. You should be checking that
>> spi_execp returns a value greater than 0 before trying to use the column
>> variables.
>>
>> As for *why* the select returns zero rows, I think you want the query
>> to read like
>> ... where id=\\$1"
>> As is, it's always looking for the literal id value $1.
>>
>>
>
> I have replaced:
> ... where id=\'\\$1\'
> with
> ... where id=\\$1
>
> and the error is now:
> ERROR: Unable to identify an operator '=$' for types 'character
> varying' and 'integer'
> You will have to retype this query using an explicit cast
>
> when I call the function like that:
>
> select ruldeb('4:0:1:1:2:');
>
> -------------
> I have to say that that account '4:0:1:1:2:' exists in the "conturi"
> table!
> As I said it previously, the query works fine directly (spi_exec)
> without the preparing stuff!
>
> teo
Trying different versions for backspashing I found that the formula that
works is:
... where id=\'\$1\'
So without double backslashing the $ sign (thought I copied that example
from a PostgreSQL documentation)
Thanks,
Teo
Constantin Teodorescu <teo@flex.ro> writes:
> I have replaced:
> ... where id=\'\\$1\'
> with
> ... where id=\\$1
> and the error is now:
> ERROR: Unable to identify an operator '=$' for types 'character
> varying' and 'integer'
Wup, you needed a space:
... where id = \\$1
BTW, if id is varchar it would be better to define the function as
accepting varchar not bpchar, no?
regards, tom lane
Tom Lane wrote: >Constantin Teodorescu <teo@flex.ro> writes: > > >>I have replaced: >>... where id=\'\\$1\' >> with >>... where id=\\$1 >> >> > > > >>and the error is now: >>ERROR: Unable to identify an operator '=$' for types 'character >>varying' and 'integer' >> >> > >Wup, you needed a space: > > ... where id = \\$1 > >BTW, if id is varchar it would be better to define the function as >accepting varchar not bpchar, no? > > Yes, I changed it as varchar the function parameter and the query parameter! Now it works even with 2 backslashes like this: ... from conturi where id = \\$1" [list varchar] teo