Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 2B2539FB9D6
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun, 28 Oct 2007 20:20:09 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 50590-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun, 28 Oct 2007 20:19:57 -0300 (ADT)
X-Greylist: whitelisted by SQLgrey-1.7.5
Received: from mail10.syd.optusnet.com.au (mail10.syd.optusnet.com.au
	[211.29.132.191])
	by postgresql.org (Postfix) with ESMTP id DE6CB9FB9B5
	for <pgsql-sql@postgresql.org>; Sun, 28 Oct 2007 20:20:02 -0300 (ADT)
Received: from [127.0.0.1] (online4-new.lnk.telstra.net [139.130.169.227])
	(authenticated sender plengada@optusnet.com.au)
	by mail10.syd.optusnet.com.au (8.13.1/8.13.1) with ESMTP id
	l9SNJvhs016922
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO)
	for <pgsql-sql@postgresql.org>; Mon, 29 Oct 2007 10:19:59 +1100
Message-ID: <4725191B.5030209@autoledgers.com.au>
Date: Mon, 29 Oct 2007 08:19:55 +0900
From: Paul Lambert <paul.lambert@autoledgers.com.au>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Select into with dynamic criteria in a plpgsql function
References: <47251704.7050508@autoledgers.com.au>
In-Reply-To: <47251704.7050508@autoledgers.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200710/141
X-Sequence-Number: 29513

Paul Lambert wrote:
> I've got a function defined in PL/PgSQL to update some fields in a 
> record where the criteria for pulling out some other values from a table 
> is dynamic.
> 
> I define a string called account_criteria to which I assign a normal SQL 
> WHERE clause based on some work done earlier in the function. I then 
> need to pull out some values from a table based on that where clause so 
> I'm doing something like:
> 
> SELECT amt_ytd INTO yesterday_amt_ytd FROM finbalance WHERE 
> account_criteria;
> 
> Which returns the following:
> 
> ERROR: argument of WHERE must be type boolean, not type text
> SQL state: 42804
> Context: SQL statement "SELECT amt_ytd FROM finbalance WHERE  $1 "
> PL/pgSQL function "fn_update_daily_balance" line 27 at SQL statement
> 
> I assume I would need something like EXECUTE to do this... but SELECT 
> INTO is not supported in EXECUTE so I have a bit of a conundrum. :(
> 
> Can anyone offer some suggestion(s) on how can I make my function 
> behave? Non-violent suggestions would be preferable.
> 

Ignore this - I didn't notice execute <blah> into <variable> - I've just 
re-read the manual and worked it out.

Thanks.

-- 
Paul Lambert
Database Administrator
AutoLedgers