Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 21510476305 for ; Mon, 14 Oct 2002 10:34:43 -0400 (EDT) Received: from relay.flex.ro (ns.flex.ro [193.230.255.2]) by postgresql.org (Postfix) with ESMTP id 472884761ED for ; Mon, 14 Oct 2002 10:34:42 -0400 (EDT) Received: from flex.ro (teo.flex.ro [193.230.255.11]) by relay.flex.ro (Postfix) with ESMTP id EAF0F36619; Mon, 14 Oct 2002 17:34:45 +0300 (EEST) Message-ID: <3DAAD607.1040009@flex.ro> Date: Mon, 14 Oct 2002 17:34:47 +0300 From: Constantin Teodorescu User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.1) Gecko/20020826 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Constantin Teodorescu Cc: Tom Lane , PostgreSQL Interfaces Subject: Re: What's wrong in this pltcl function ? References: <3DAA8ADB.6060208@flex.ro> <12147.1034605407@sss.pgh.pa.us> <3DAAD536.8030603@flex.ro> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/36 X-Sequence-Number: 3198 Constantin Teodorescu wrote: > Tom Lane wrote: > >> Constantin Teodorescu 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