Re: Select into with dynamic criteria in a plpgsql function

[Paul Lambert <paul(dot)lambert(at)autoledgers(dot)com(dot)au>]

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