From: Josh Williams <drykath@runbox.com>
To: jeff@frostconsultingllc.com
Cc: rray@mstc.state.ms.us
Cc: pgsql-sql@postgresql.org
Subject: Re: How to query by column names
Date: Tue, 23 Jan 2007 00:03:38 -0500 (EST)
Message-ID: <E1H9Doo-0008U8-CO@fenris.runbox.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0701221727430.5992@discord.home.frostconsultingllc.com>
References: <Pine.LNX.4.64.0701221612220.10491@rray.drdc.mstc.ms.gov>
<Pine.LNX.4.64.0701221528250.5992@discord.home.frostconsultingllc.com>
<Pine.LNX.4.64.0701221756520.8787@rray.drdc.mstc.ms.gov>
<Pine.LNX.4.64.0701221619590.5992@discord.home.frostconsultingllc.com>
<Pine.LNX.4.64.0701221827420.8787@rray.drdc.mstc.ms.gov>
<Pine.LNX.4.64.0701221636130.5992@discord.home.frostconsultingllc.com>
<Pine.LNX.4.64.0701221914400.8787@rray.drdc.mstc.ms.gov>
<Pine.LNX.4.64.0701221727430.5992@discord.home.frostconsultingllc.com>
From: Jeff Frost <jeff@frostconsultingllc.com>
> On Mon, 22 Jan 2007, Richard Ray wrote:
...
> > #!/bin/bash
> > CMD="psql -d test \"select * from t1\""
> > echo $CMD >> my_log
> > eval $CMD |
> > while read x; do
> > do_something_with_x
> > done
> >
> > In this example * expands to all files in the current working directory.
> > I was attempting to get around this by enumerating the table attributes.
>
> Oh! Why didn't you just say that in the first place. You just need quotes.
...
That's definitely part of it. I'm assuming the above is an abridged example and the OP is doing something dynamic with the query. The real trouble is Bash likes to expand the asterisk into a list of every file in the current directory when you try to push the command through a variable. So it's just a matter of finding a way to escape the * character to keep Bash from globbing, which unfortunately right now is escaping me (no pun intended.)
Two reasonable workarounds come to mind:
1. Turn off Bash's pathname expansion: #!/bin/bash -f
This will of course disable it script-wide, and thus will break any place you actually are trying to use this feature, if at all.
2. Don't put an * in the variable.
If all you're really doing is replacing the table name then only stick that into a variable, say tablename, and directly execute the rest:
psql -d test -c "SELECT * FROM $tablename" | while etc
Worst case, you'll end up with a messy $leftside and $rightside variable set.
To answer the original question, the field must be hard coded either as a list or that perhaps over-used(?) asterisk. If you really need to pull and use that from the table definition you'll need two round trips to the server.
Best of luck,
- Josh Williams
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: drykath@runbox.com, jeff@frostconsultingllc.com, rray@mstc.state.ms.us
Subject: Re: How to query by column names
In-Reply-To: <E1H9Doo-0008U8-CO@fenris.runbox.com>
* 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