Re: [SQL] How to query by column names
From: Jeff Frost <[EMAIL PROTECTED]> > 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] INSERT INTO
From: "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]>
> I have 2 tables. TABLE1 and TABLE2.
>
> TABLE1 has about 400 records with details (client code, name, surname,
> address, date of birth) of my clients.
> TABLE2 is filled with some of the client details and other extra details
> (client code, address, telephone, etc)
>
> So in my ASP page i have a select that gets the client details from TABLE1
> using the client code and inserts them in to TABLE2
>
> When i have a string that has a ' inside of it the record is not inserted in
> to TABLE2. I know that writing it twice will fix it but how can i ask my ASP
> code to do it..
I'm sadly tasked with maintaining a little ASP code from time to time. If you
need to do it through a script, what you're looking for is the Replace
function, as already mentioned in the thread:
Replace( InputString, "'", "''" )
However since we're already on pgsql-sql, assuming you're not doing a whole lot
more than pulling from one table and inserting into another, you may want to
consider seeing if you can roll it into a single SQL statement:
> INSERT INTO TABLE2 (TE_INDI) VALUES ('SANT'ANGELO LODIGIANO');
INSERT INTO TABLE2 (TE_INDI) VALUES SELECT TE_INDI FROM TABLE1 WHERE (...)
No quoting or server->client->server worries to deal with at all...
Best of luck,
- Josh Williams
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Get the max(value1, value2, value3) from a table
On Mon, 2008-01-07 at 17:03 -0500, Emi Lu wrote: > select ?max?(col1, col2, col3) as result; > will return > > result > --- > 5 > 8 > 12 > > (3 rows) 8.1 (I believe?) introduced GREATEST(), which does precisely what you're looking for. But if 8.0 is a must, you'll probably have to create your own function to do that. Which should be fairly easy to do if you're working with a static number of columns/data types/etc... - Josh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
