From: Paul Lambert <paul.lambert@autoledgers.com.au>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Select into with dynamic criteria in a plpgsql function
Date: Mon, 29 Oct 2007 08:11:00 +0900
Message-ID: <47251704.7050508@autoledgers.com.au> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: paul.lambert@autoledgers.com.au
Subject: Re: Select into with dynamic criteria in a plpgsql function
In-Reply-To: <47251704.7050508@autoledgers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox