pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
psql question
11+ messages / 9 participants
[nested] [flat]

* psql question
@ 2000-11-20 01:38  Joe Conway <joe@conway-family.com>
  0 siblings, 2 replies; 11+ messages in thread

From: Joe Conway @ 2000-11-20 01:38 UTC (permalink / raw)
  To: pgsql-sql

Hello,

I've been searching off-and-on for an answer to this question, but I haven't
found anything but fleeting references.

Here's the problem/question:

On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use
of the up arrow key for history and the escape/tab key for command
completion, but on my remote web host (webpipe.net) those keys don't work.
What do I need to do to get these features working on my remote web host?
I've seen reference to .psqlrc in the psql man file, but not much else (no
syntax, etc).

Thanks in advance!

Joe





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: psql question
@ 2000-11-23 18:31  Peter Eisentraut <peter_e@gmx.net>
  parent: Joe Conway <joe@conway-family.com>
  1 sibling, 2 replies; 11+ messages in thread

From: Peter Eisentraut @ 2000-11-23 18:31 UTC (permalink / raw)
  To: Joe Conway <joe@conway-family.com>; +Cc: pgsql-sql

Joe Conway writes:

> On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use
> of the up arrow key for history and the escape/tab key for command
> completion, but on my remote web host (webpipe.net) those keys don't work.
> What do I need to do to get these features working on my remote web host?
> I've seen reference to .psqlrc in the psql man file, but not much else (no
> syntax, etc).

If you compiled from source, make sure you had the readline-devel package
installed.

$ man psql

<snip>
       If you have the readline library installed but  psql  does
       not  seem  to  use  it, you must make sure that Postgres's
       top-level configure script finds it.  configure  needs  to
       find  both  the library libreadline.a (or a shared library
       equivalent) and the header files readline.h and  history.h
       (or  readline/readline.h and readline/history.h) in appro­
       priate directories. If you have  the  library  and  header
       files  installed in an obscure place you must tell config­
       ure about them, for example:
 
       $ ./configure --with-includes=/opt/gnu/include --with-libs=/opt/gnu/lib ...
 
       Then you have  to  recompile  psql  (not  necessarily  the
       entire code tree).
 
       The GNU readline library can be obtained from the GNU pro­
       ject's      FTP      server      at      ftp://ftp.gnu.org
       <URL:ftp://ftp.gnu.org;.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: psql question
@ 2000-11-23 19:50  Mathijs Brands <mathijs@ilse.nl>
  parent: Peter Eisentraut <peter_e@gmx.net>
  1 sibling, 0 replies; 11+ messages in thread

From: Mathijs Brands @ 2000-11-23 19:50 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Joe Conway <joe@conway-family.com>; pgsql-sql

On Thu, Nov 23, 2000 at 07:31:36PM +0100, Peter Eisentraut allegedly wrote:
> Joe Conway writes:
> 
> > On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use
> > of the up arrow key for history and the escape/tab key for command
> > completion, but on my remote web host (webpipe.net) those keys don't work.
> > What do I need to do to get these features working on my remote web host?
> > I've seen reference to .psqlrc in the psql man file, but not much else (no
> > syntax, etc).
> 
> If you compiled from source, make sure you had the readline-devel package
> installed.

Also, check the version of the psql shell. 6.x won't do a lot of those neat
little things that make life easier in 7.x.

Mathijs
-- 
It's not that perl programmers are idiots, it's that the language
rewards idiotic behavior in a way that no other language or tool has
ever done.
                                                    Erik Naggum



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: psql question
@ 2000-11-23 22:01  Joe Conway <joe@conway-family.com>
  parent: Peter Eisentraut <peter_e@gmx.net>
  1 sibling, 1 reply; 11+ messages in thread

From: Joe Conway @ 2000-11-23 22:01 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql

> > On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows
use
> > of the up arrow key for history and the escape/tab key for command
> > completion, but on my remote web host (webpipe.net) those keys don't
work.
> > What do I need to do to get these features working on my remote web
host?
> > I've seen reference to .psqlrc in the psql man file, but not much else
(no
> > syntax, etc).
>
> If you compiled from source, make sure you had the readline-devel package
> installed.
>

Thanks for your responses, Peter & Mathijs.

Actually, since the issue is on a hosted system, I don't have access to
recompile. I was hoping that maybe there would be something I could
configure from my own (non-root and non-postgres) account.

My question actaully took several days just to make it to the list, so in
the meantime I found my own work-around -- I copied local copies of psql and
libpq up to my account on the web host. Then I put my own account folder to
the front of the PATH and added a LD_LIBRARY_PATH. Now when I run 'psql' it
works just like on my own machine!

Thanks again for the response.

Joe





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: psql question
@ 2000-11-23 23:43  clayton cottingham <clayton@marketingchallenge.com>
  parent: Joe Conway <joe@conway-family.com>
  0 siblings, 1 reply; 11+ messages in thread

From: clayton cottingham @ 2000-11-23 23:43 UTC (permalink / raw)
  To: Joe Conway <joe@conway-family.com>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-sql

Joe Conway wrote:
> 
> > > On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows
> use
> > > of the up arrow key for history and the escape/tab key for command
> > > completion, but on my remote web host (webpipe.net) those keys don't
> work.
> > > What do I need to do to get these features working on my remote web
> host?
> > > I've seen reference to .psqlrc in the psql man file, but not much else
> (no
> > > syntax, etc).
> >
> > If you compiled from source, make sure you had the readline-devel package
> > installed.
> >
> 
> Thanks for your responses, Peter & Mathijs.
> 
> Actually, since the issue is on a hosted system, I don't have access to
> recompile. I was hoping that maybe there would be something I could
> configure from my own (non-root and non-postgres) account.
> 
> My question actaully took several days just to make it to the list, so in
> the meantime I found my own work-around -- I copied local copies of psql and
> libpq up to my account on the web host. Then I put my own account folder to
> the front of the PATH and added a LD_LIBRARY_PATH. Now when I run 'psql' it
> works just like on my own machine!
> 
> Thanks again for the response.
> 
> Joe


you could use a version of pgsql on your own machine and use the host
switch to connect to an extrenal db

of course your db isp will have to allow your ip to connect, not an easy
thing if your dial up!!


this way you could recompile your own ver of psql

hope that helps



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: psql question
@ 2000-11-24 04:06  bmccoy@chapelperilous.net
  parent: Joe Conway <joe@conway-family.com>
  1 sibling, 0 replies; 11+ messages in thread

From: bmccoy@chapelperilous.net @ 2000-11-24 04:06 UTC (permalink / raw)
  To: Joe Conway <joe@conway-family.com>; +Cc: pgsql-sql

On Sun, 19 Nov 2000, Joe Conway wrote:

> On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use
> of the up arrow key for history and the escape/tab key for command
> completion, but on my remote web host (webpipe.net) those keys don't work.
> What do I need to do to get these features working on my remote web host?
> I've seen reference to .psqlrc in the psql man file, but not much else (no
> syntax, etc).

*sigh* another candidate for the FAQ, I think.

The history keys rely on the GNU readline library.  If that library is not
available on your system, the history keys don't work.  If you compile
postgres from scratch, make sure you have also installed the
readline-devel rpm as well, so you get the header files, else the
configure phase of the compile will not properly detect and use the
library.

Brett W. McCoy
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Criticism comes easier than craftsmanship.
		-- Zeuxis




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: psql question
@ 2000-11-24 16:50  Adrian Phillips <adrianp@powertech.no>
  parent: clayton cottingham <clayton@marketingchallenge.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Adrian Phillips @ 2000-11-24 16:50 UTC (permalink / raw)
  To: clayton cottingham <clayton@marketingchallenge.com>; +Cc: Joe Conway <joe@conway-family.com>; pgsql-sql

>>>>> "Clayton" == clayton cottingham <clayton@marketingchallenge.com> writes:


    Clayton> you could use a version of pgsql on your own machine and
    Clayton> use the host switch to connect to an extrenal db

    Clayton> of course your db isp will have to allow your ip to
    Clayton> connect, not an easy thing if your dial up!!

Or, assuming you have ssh access, use ssh to setup a tunnel between
the two.

Sincerely,

Adrian Phillips

-- 
Your mouse has moved.
Windows NT must be restarted for the change to take effect.
Reboot now?  [OK]



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: psql question
@ 2002-03-07 20:30  Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Josh Berkus @ 2002-03-07 20:30 UTC (permalink / raw)
  To: Charles Hauser <chauser@acpub.duke.edu>; +Cc: pgsql-sql

CHarles,

> When I created TABLE clone I initially defined a column:
> 
> gb_accessions_id INTEGER REFERENCES gb_accessions(gb_accessions_id)
>  NULL
> 
> I have renamed TABLE gb_accessions to TABLE gb, and its column
>  gb_accessions_id to gb_id.
> 
> I next renamed the column 'gb_accessions_id' in TABLE clone to
>  'gb_id'.
> In renaming this column in TABLE clone I appear to have lost the
>  reference to TABLE gb (see below).
> 
> Questions:
> 
> 1. During which step in my renaming did this occur?
> 2. How should I have carried this out?
> 3. Can I add back REFERENCES gb(gb_id)?

I generally find that it is much easier, over all, to dump the database
 to a pgdump sql-text file, and do my editing there whenever I need to
 make significant exits to important database tables.  Otherwise, it is
 difficult to make sure that all of your constraints, rules, triggers,
 and FKs update correctly.

You can add back the REFERENCES constraints.  First, check the table
 definitions of the child tables to see if they still have orphaned
 constraints pointing to gb_accessions.  (do this through psql's \d
 table_name)  If so, drop those constraints.

Unfortunately, I do not know the syntax for ALTER CONSTRAINT ...
 FORIEGN KEY ....   Post it as a question to the list.

-Josh



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: psql question
@ 2002-03-08 02:31  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Christopher Kings-Lynne @ 2002-03-08 02:31 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; Charles Hauser <chauser@acpub.duke.edu>; +Cc: pgsql-sql

> Unfortunately, I do not know the syntax for ALTER CONSTRAINT ...
>  FORIEGN KEY ....   Post it as a question to the list.

ALTER TABLE foo ADD FOREIGN KEY (field) REFERENCES table(field);

Chris




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: psql: question
@ 2002-05-08 15:23  Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Josh Berkus @ 2002-05-08 15:23 UTC (permalink / raw)
  To: Charles Hauser <chauser@acpub.duke.edu>; +Cc: pgsql-sql

Chuck,

Please cc: one of the Postgres lists when you query me.  If you're
getting Q&A support just from me, I'll have to start charging you!

> Trying to work out details of how to let users page back and forth
> among a multi-page list of results.
> 
> Problem: I need to know the total number of tuples the query would
> return in order to decide if there are more to display.
> 
> 1) Use CURSOR and FETCH
> 
> $res= $con->exec(
>  "BEGIN WORK;
>  DECLARE gene_result CURSOR FOR
>  SELECT blah blah
>  ORDER BY blah;
>  FETCH $offset IN gene_result
>  ");
> 
> $result->ntuple: returns only the number set by $offset, not TOTAL
> for query.
> 
> 2) use LIMIT OFFSET
> 
> same problem,
> $result->ntuple: returns only the number set by LIMIT, OFFSET, not
> TOTAL for query.
> 
> So there has to be a way to glean both the TOTAL and the SUBSET
> returned?
> 
> Searching the postgresql archives obviously can deal with this
> http://archives.postgresql.org/pgsql-sql/
> 'Displaying documents 11-20 of total 243 found.'

Personally, I don't know any way to do this without running the query
twice.  

Least inefficient approach:  If your interface allows seperating the
SELECT clause from the rest of the query, then run first:

SELECT COUNT(*)
FROM rest-of-query ...

Check that value.  If it's 0, then return a "no records found" message
to the user.  Otherwise, run the query broken into LIMIT and OFFSET
blocks.

More inefficient approach:  if the way the query is being generated
does not allow you to break off the SELECT clause, then you need to
subselect a count:

SELECT COUNT(*) FROM (
  SELECT blah FROM rest_of_query) query_count;

This will also give you a count, buut be slightly slower than the
above.

If anybody knows a way to get a count *without* running the query
twice, I'd be glad to hear it!

-Josh Berkus







^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: psql: question
@ 2002-05-09 05:21  Rajesh Kumar Mallah. <mallah@trade-india.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Rajesh Kumar Mallah. @ 2002-05-09 05:21 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; Charles Hauser <chauser@acpub.duke.edu>; +Cc: pgsql-sql

Hi

I run the query twice only the first time the query is requested.

In a web context i can keep passing the total number of
record as a query parameter for displaying the subsequent
pages

for the first request is 50% efficient but the remaining are 100%

but the above works under the assumption  that you do not add 
record while the end user is browsing the pages which is mostly safe.

regds
mallah.

On Wednesday 08 May 2002 08:53 pm, Josh Berkus wrote:
> Chuck,
>
> Please cc: one of the Postgres lists when you query me.  If you're
> getting Q&A support just from me, I'll have to start charging you!
>
> > Trying to work out details of how to let users page back and forth
> > among a multi-page list of results.
> >
> > Problem: I need to know the total number of tuples the query would
> > return in order to decide if there are more to display.
> >
> > 1) Use CURSOR and FETCH
> >
> > $res= $con->exec(
> >  "BEGIN WORK;
> >  DECLARE gene_result CURSOR FOR
> >  SELECT blah blah
> >  ORDER BY blah;
> >  FETCH $offset IN gene_result
> >  ");
> >
> > $result->ntuple: returns only the number set by $offset, not TOTAL
> > for query.
> >
> > 2) use LIMIT OFFSET
> >
> > same problem,
> > $result->ntuple: returns only the number set by LIMIT, OFFSET, not
> > TOTAL for query.
> >
> > So there has to be a way to glean both the TOTAL and the SUBSET
> > returned?
> >
> > Searching the postgresql archives obviously can deal with this
> > http://archives.postgresql.org/pgsql-sql/
> > 'Displaying documents 11-20 of total 243 found.'
>
> Personally, I don't know any way to do this without running the query
> twice.
>
> Least inefficient approach:  If your interface allows seperating the
> SELECT clause from the rest of the query, then run first:
>
> SELECT COUNT(*)
> FROM rest-of-query ...
>
> Check that value.  If it's 0, then return a "no records found" message
> to the user.  Otherwise, run the query broken into LIMIT and OFFSET
> blocks.
>
> More inefficient approach:  if the way the query is being generated
> does not allow you to break off the SELECT clause, then you need to
> subselect a count:
>
> SELECT COUNT(*) FROM (
>   SELECT blah FROM rest_of_query) query_count;
>
> This will also give you a count, buut be slightly slower than the
> above.
>
> If anybody knows a way to get a count *without* running the query
> twice, I'd be glad to hear it!
>
> -Josh Berkus
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.





^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2002-05-09 05:21 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-20 01:38 psql question Joe Conway <joe@conway-family.com>
2000-11-23 18:31 ` Peter Eisentraut <peter_e@gmx.net>
2000-11-23 19:50   ` Mathijs Brands <mathijs@ilse.nl>
2000-11-23 22:01   ` Joe Conway <joe@conway-family.com>
2000-11-23 23:43     ` clayton cottingham <clayton@marketingchallenge.com>
2000-11-24 16:50       ` Adrian Phillips <adrianp@powertech.no>
2000-11-24 04:06 ` bmccoy@chapelperilous.net
2002-03-07 20:30 Re: psql question Josh Berkus <josh@agliodbs.com>
2002-03-08 02:31 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-05-08 15:23 Re: psql: question Josh Berkus <josh@agliodbs.com>
2002-05-09 05:21 ` Re: psql: question Rajesh Kumar Mallah. <mallah@trade-india.com>

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