Message-ID: <47251704.7050508@autoledgers.com.au>
Date: Mon, 29 Oct 2007 08:11:00 +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: Select into with dynamic criteria in a plpgsql function
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.

-- 
Paul Lambert
Database Administrator
AutoLedgers