Обсуждение: 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