Thread: passing values into .sql scripts

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)
fromledger 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

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

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\''