pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat's wrong in this pltcl function ?
8+ messages / 3 participants
[nested] [flat]
* What's wrong in this pltcl function ?
@ 2002-10-14 09:14 Constantin Teodorescu <teo@flex.ro>
0 siblings, 2 replies; 8+ messages in thread
From: Constantin Teodorescu @ 2002-10-14 09:14 UTC (permalink / raw)
To: pgsql-interfaces
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:"
------------------
Please, could someone give me a clue?
thanks in advance,
Constantin Teodorescu
Braila, ROMANIA
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: What's wrong in this pltcl function ?
@ 2002-10-14 14:11 Nigel J. Andrews <nandrews@investsystems.co.uk>
parent: Constantin Teodorescu <teo@flex.ro>
1 sibling, 0 replies; 8+ messages in thread
From: Nigel J. Andrews @ 2002-10-14 14:11 UTC (permalink / raw)
To: Constantin Teodorescu <teo@flex.ro>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: What's wrong in this pltcl function?
@ 2002-10-14 14:19 Constantin Teodorescu <teo@flex.ro>
0 siblings, 0 replies; 8+ messages in thread
From: Constantin Teodorescu @ 2002-10-14 14:19 UTC (permalink / raw)
To: pgsql-interfaces
Nigel J. Andrews wrote:
> 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.
>
>
>
>
I just imagined that but the question is why!
Because the spi_exec on the original query without preparing anything
works just fine!
I mean:
spi_exec "select cheie,denumire from conturi where id=\'$cont\'"
seems that there is some problem at specifying the parameter list (or
backsplashing the $ sign)
teo
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: What's wrong in this pltcl function ?
@ 2002-10-14 14:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Constantin Teodorescu <teo@flex.ro>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2002-10-14 14:23 UTC (permalink / raw)
To: Constantin Teodorescu <teo@flex.ro>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: What's wrong in this pltcl function ?
@ 2002-10-14 14:31 Constantin Teodorescu <teo@flex.ro>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Constantin Teodorescu @ 2002-10-14 14:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: What's wrong in this pltcl function ?
@ 2002-10-14 14:34 Constantin Teodorescu <teo@flex.ro>
parent: Constantin Teodorescu <teo@flex.ro>
1 sibling, 0 replies; 8+ messages in thread
From: Constantin Teodorescu @ 2002-10-14 14:34 UTC (permalink / raw)
To: Constantin Teodorescu <teo@flex.ro>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: What's wrong in this pltcl function ?
@ 2002-10-14 14:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Constantin Teodorescu <teo@flex.ro>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2002-10-14 14:44 UTC (permalink / raw)
To: Constantin Teodorescu <teo@flex.ro>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: What's wrong in this pltcl function ?
@ 2002-10-14 14:50 Constantin Teodorescu <teo@flex.ro>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Constantin Teodorescu @ 2002-10-14 14:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2002-10-14 14:50 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-14 09:14 What's wrong in this pltcl function ? Constantin Teodorescu <teo@flex.ro>
2002-10-14 14:11 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
2002-10-14 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-14 14:31 ` Constantin Teodorescu <teo@flex.ro>
2002-10-14 14:34 ` Constantin Teodorescu <teo@flex.ro>
2002-10-14 14:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-14 14:50 ` Constantin Teodorescu <teo@flex.ro>
2002-10-14 14:19 Re: What's wrong in this pltcl function? Constantin Teodorescu <teo@flex.ro>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox