pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpassing values into .sql scripts
3+ messages / 2 participants
[nested] [flat]
* passing values into .sql scripts
@ 2005-09-15 21:27 Geoffrey Knauth <geoff@knauth.org>
2005-09-15 22:43 ` Re: passing values into .sql scripts Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 3+ messages in thread
From: Geoffrey Knauth @ 2005-09-15 21:27 UTC (permalink / raw)
To: pgsql-sql
I want to do something like this:
\set tmp :acct 'a value'
\i query.sql
where query.sql looks like this:
select sum(amount), dr_acct from ledger where dr_acct = :acct
group by dr_acct;
select sum(amount), cr_acct from ledger where cr_acct = :acct
group by cr_acct;
select
(select sum(amount) from ledger where dr_acct = :acct )
- (select sum(amount) from ledger where cr_acct = :acct );
However, this is what I get:
psql:pnc.sql:1: ERROR: column "a value" does not exist
Is there a different way I should be doing this? (I'm used to
Oracle's &1...&n parameters.)
Geoffrey
--
Geoffrey S. Knauth | http://knauth.org/gsk
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: passing values into .sql scripts
2005-09-15 21:27 passing values into .sql scripts Geoffrey Knauth <geoff@knauth.org>
@ 2005-09-15 22:43 ` Michael Fuhr <mike@fuhr.org>
2005-09-15 22:49 ` Re: passing values into .sql scripts Geoffrey Knauth <geoff@knauth.org>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Fuhr @ 2005-09-15 22:43 UTC (permalink / raw)
To: Geoffrey Knauth <geoff@knauth.org>; +Cc: pgsql-sql
On Thu, Sep 15, 2005 at 05:27:52PM -0400, Geoffrey Knauth wrote:
> I want to do something like this:
>
> \set tmp :acct 'a value'
What's your intention here? The above sets the variable tmp to the
value of the variable acct concatenated with 'a value', but you
don't show acct being set anywhere. Did you mean to set acct?
If so then try this:
\set acct '\'a value\''
> \i query.sql
>
> where query.sql looks like this:
>
> select sum(amount), dr_acct from ledger where dr_acct = :acct
> group by dr_acct;
> select sum(amount), cr_acct from ledger where cr_acct = :acct
> group by cr_acct;
> select
> (select sum(amount) from ledger where dr_acct = :acct )
> - (select sum(amount) from ledger where cr_acct = :acct );
>
> However, this is what I get:
>
> psql:pnc.sql:1: ERROR: column "a value" does not exist
What's pnc.sql? Is that the real name of the file you referred
to as query.sql?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: passing values into .sql scripts
2005-09-15 21:27 passing values into .sql scripts Geoffrey Knauth <geoff@knauth.org>
2005-09-15 22:43 ` Re: passing values into .sql scripts Michael Fuhr <mike@fuhr.org>
@ 2005-09-15 22:49 ` Geoffrey Knauth <geoff@knauth.org>
0 siblings, 0 replies; 3+ messages in thread
From: Geoffrey Knauth @ 2005-09-15 22:49 UTC (permalink / raw)
To: pgsql-sql
That did exactly what I wanted. Thank you! (I had tried \set
acct ... before, but it was your quoting that fixed my problem.)
Geoffrey
--
Geoffrey S. Knauth | http://knauth.org/gsk
On Sep 15, 2005, at 18:43, Michael Fuhr wrote:
> What's your intention here? The above sets the variable tmp to the
> value of the variable acct concatenated with 'a value', but you
> don't show acct being set anywhere. Did you mean to set acct?
> If so then try this:
>
> \set acct '\'a value\''
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-09-15 22:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-15 21:27 passing values into .sql scripts Geoffrey Knauth <geoff@knauth.org>
2005-09-15 22:43 ` Michael Fuhr <mike@fuhr.org>
2005-09-15 22:49 ` Geoffrey Knauth <geoff@knauth.org>
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