Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id C93974761CD
	for <pgsql-interfaces@postgresql.org>;
	Mon, 14 Oct 2002 10:31:13 -0400 (EDT)
Received: from relay.flex.ro (ns.flex.ro [193.230.255.2])
	by postgresql.org (Postfix) with ESMTP id E04414760C9
	for <pgsql-interfaces@postgresql.org>;
	Mon, 14 Oct 2002 10:31:12 -0400 (EDT)
Received: from flex.ro (teo.flex.ro [193.230.255.11])
	by relay.flex.ro (Postfix) with ESMTP
	id A202D216F3; Mon, 14 Oct 2002 17:31:16 +0300 (EEST)
Message-ID: <3DAAD536.8030603@flex.ro>
Date: Mon, 14 Oct 2002 17:31:18 +0300
From: Constantin Teodorescu <teo@flex.ro>
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: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
Subject: Re: What's wrong in this pltcl function ?
References: <3DAA8ADB.6060208@flex.ro> <12147.1034605407@sss.pgh.pa.us>
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/35
X-Sequence-Number: 3197

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