pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect into with dynamic criteria in a plpgsql function
2+ messages / 1 participants
[nested] [flat]
* Select into with dynamic criteria in a plpgsql function
@ 2007-10-28 23:11 Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-28 23:19 ` Re: Select into with dynamic criteria in a plpgsql function Paul Lambert <paul.lambert@autoledgers.com.au>
0 siblings, 1 reply; 2+ messages in thread
From: Paul Lambert @ 2007-10-28 23:11 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Select into with dynamic criteria in a plpgsql function
2007-10-28 23:11 Select into with dynamic criteria in a plpgsql function Paul Lambert <paul.lambert@autoledgers.com.au>
@ 2007-10-28 23:19 ` Paul Lambert <paul.lambert@autoledgers.com.au>
0 siblings, 0 replies; 2+ messages in thread
From: Paul Lambert @ 2007-10-28 23:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-10-28 23:19 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-28 23:11 Select into with dynamic criteria in a plpgsql function Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-28 23:19 ` Paul Lambert <paul.lambert@autoledgers.com.au>
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