Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger

[Tony Caduto]

That is true in 8.0 but not anymore in 8.1.  The reason we invented the
"pg_pltemplate" catalog is to ensure that old dumps of PL language
definitions will track desired changes without such manual heroics ...



Tom,

Thanks for the info, I will update my article to reflect that.

--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql
http://www.amsoftwaredesign.com

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger

[Ben Trewern]

PG Lightning does Code Completion.  I don't think there is a frontend 
tool that can step through a PL/pgSQL function.
 
Ben

  ""Ken Winter"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]...
  
  Is a better PL/pgSQL editor / 
  debugger than pgAdmin III or phpPgAdmin available anywhere?
   
  I ask because I was stuck for 
  two days on the following error message:
   
  ERROR:  syntax error at or 
  near "LOOP"
  CONTEXT:  compile of 
  PL/pgSQL function "gen_history" near line 126
   
  …and neither of those tools 
  offered any more help than that.  It turned out the error was a missing 
  “;” way back in line 53, and it took two days of trial and error and staring 
  at the code to find it.  (On the positive side, pgAdmin III’s use of 
  different colors to distinguish different program elements [variables, 
  keywords, string constants, comments] made the staring part easier to 
  do.)
   
  I’ve learned that pgAdmin 
  “syntax error” can mean anything from a missing “;” to a faulty block 
  structure to an undeclared variable to… I don’t know what else, and as witness 
  the example that error may be nowhere near the line that is flagged.  So 
  I’m looking for a PL/pgSQL tool that would at least provide more diagnostic 
  error messages.  Preferably, it would also offer some sort of 
  “breakpoint” function to let the developer see the values of variables at 
  specified points in the code.  
   
  I looked on the pgAdmin web 
  site.  The only place a PL/pgSQL debugger was mentioned was on the “to 
  do” page, under “major projects” (http://www.pgadmin.org/development/todo.php), 
  which I guess means don’t hold your breath. 
   
  As editors, the two pgAdmin 
  tools apparently don’t offer elementary functions such as find and replace, 
  which means I have to slurp my code out into a text editor when I really need 
  these things.
   
  So I guess my questions 
  are:
   
  
Is it the case that the 
pgAdmin tools actually do offer these features, but I just haven’t found 
them yet?  If so, can you show me where they are? 
Are these functions available 
through add-ons to either pgAdmin tool?  If so, where can I get these 
add-ons? 
Are there other PL/pgSQL 
editors that provide these functions?  If so, what?  Obviously, 
I’d prefer a free one, but would pay for one if necessary. 

   
  ~ TIA
  ~ 
Ken

[GENERAL] help

[Paolo Ditto]

Hi.
I would want do a php script to access my postgres database from remote host. I 
would like to know how I can do. Particularly, I would to know the default 
directory where my database is located so that to be able to call it with my 
php script.


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Viewing Database Scheme

[Rich Shepard]

On Sat, 28 Jan 2006, Roderick A. Anderson wrote:


Hi Rich.  Seems once again we're meeting in the same places.  The Internet
sure seems small sometimes.


  Hello, again, Rod.


I saw all the other posts but you might look at dbwrench (a Java
application). Does a lot more than what you need (plus after 30 days costs
approx. $150) but our lead programmer is using it to reverse engineer a
MySQL database he shoe-horned into PostgreSQL. He loves it.


  There has been someone else working on this effort to move XRMS from
strictly MySQL to PostgreSQL, and he's been at it longer. But, it's almost
there now.

  The install script generated an error message with every create statement,
but the tables were created anyway. But, the indices are wrong and the dates
have screwy values. I printed the web page as a .ps file and sent that to the
developers. They told me what the most likely cause was, so I'll go through
the install.xml file and make their recommended changes, then re-run the
install script. So this is very close to working and it's a one-shot deal for
me.

  At OSCON last summer I spoke with the SugarCRM sales folks. They said that
they received many requiests to add PostgreSQL support, but management wants
to stick with MySQL. You'll recall from the other mail list that I had a time
getting MySQL properly installed and configured just to run this one
application. After trying it for a while I discovered that I really didn't
like it. I think XRMS will do much better.

  On the other side of this thread, the command line pg_dump worked with the
proper syntax. I think it was the two original replies (one with a command
line solution, the other with a psql solution) that confused me. Why none of
the psql commands are working for me I don't know. But, pg_dump works just
fine. Thanks to everyone.

Rich

--
Richard B. Shepard, Ph.D.   |   Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic"
 Voice: 503-667-4517 Fax: 503-667-8863

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] Viewing Database Scheme

[Rich Shepard]

On Sat, 28 Jan 2006, Eric B. Ridge wrote:


That's because you've used the wrong syntax.

$ psql contacts -c "\dt" > xrms.tables

This is why I suggested you read the psql man page.


  Well, the man page installed shows the -c option is to specify one command,
but when I try that:

[EMAIL PROTECTED] ~]$ psql -c "\dt" > xrms.tables
psql: FATAL:  database "rshepard" does not exist

  That's why I tried specifying the database name.

Likely, the output is buffered.  Did you try quitting psql (via \q) before 
checking the contents of the file.


  Yes.

Dude, "pg_dump" is not a psql command, nor is it a SQL command.  It's a 
command-line program.  You run it from your shell:


$ pg_dump --schema-only pg_dump > xrms-schema.dmp


[EMAIL PROTECTED] ~]$ pg_dump --schema-only pg_dump > xrms-schema.dmp
pg_dump: [archiver (db)] connection to database "pg_dump" failed: FATAL:
database "pg_dump" does not exist

  However, as the pg_dump man page specifies, the database name needs to be
listed on the coammand line. So,

$ pg_dump -s contacts > xrms.tables

works just fine.

  I'm still curious why I cannot do this within psql.

Rich

--
Richard B. Shepard, Ph.D.   |   Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic"
 Voice: 503-667-4517 Fax: 503-667-8863

---(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: [GENERAL] Viewing Database Scheme

[Roderick A. Anderson]

Rich Shepard wrote:
  I'm trying to help the XRMS developers add postgres support ('cause 
that's

what I use). They've done well so far with help from other postgres users,
but now I've been asked to help getting the indices correct.


Hi Rich.  Seems once again we're meeting in the same places.  The 
Internet sure seems small sometimes.


I saw all the other posts but you might look at dbwrench ( a Java 
application ).  Does a lot more than what you need ( plus after 30 days 
costs approx. $150 ) but our lead programmer is using it to reverse 
engineer a MySQL database he shoe-horned into PostgreSQL.  He loves it.


It has several output formats.




Rod
--

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger

[Tom Lane]

Tony Caduto <[EMAIL PROTECTED]> writes:
> Another thing to keep in mind is if you restore a dump from 7.x to 8.x 
> it will not do any checking unless you make a tweak, see this article:
> http://www.milwaukeesoft.com/forums/viewtopic.php?t=83

> As far as I know this affects 8.0 and 8.1, I am sure someone will 
> correct me if I am wrong :-)

That is true in 8.0 but not anymore in 8.1.  The reason we invented the
"pg_pltemplate" catalog is to ensure that old dumps of PL language
definitions will track desired changes without such manual heroics ...

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] creating users per database

[Tom Lane]

Karsten Hilbert <[EMAIL PROTECTED]> writes:
> I am using a create_user() SP created by "postgres" with
> "security definer" (gasp). This works just fine, however, it
> transfers createuser rights to *anyone* allowed to connect
> to the database the function is in.

Not if you restrict who's allowed to execute the function.  Revoke
the default public EXECUTE right on it, and grant to just who you
want.  A good way to manage this is to grant the EXECUTE right to
a group (say "wheel") and then be careful who you grant membership
in "wheel" to.

> But perhaps I shouldn't count on the db_user_namespace
> feature to exist in the future ?

It'll go away as soon as we think of a better way ;-).  I wouldn't
expect to lose functionality, but the syntax will likely change.

> It isn't a given that all database users will have system
> level equivalents hence samegroup won't work in all cases.

You do not understand: samegroup means PG users who are members
of the PG group named the same as the database can connect to the
database.  This has *nothing* to do with any OS-level notions.

regards, tom lane

---(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: [GENERAL] creating users per database

[Karsten Hilbert]

On Sat, Jan 28, 2006 at 11:04:09AM -0500, Tom Lane wrote:

> > I have a faint memory of it being possible to create users
> > "inside" *one* given database by way of a particular "create
> > user" syntax along the lines of:
> >  create user [EMAIL PROTECTED] ...;
> There is the db_user_namespace kluge^H^H^H^H^Hparameter, but it's
> pretty ugly.
Ah, I see. So my memory didn't fail that much.

> Do you really need DB-specific user names, or just
> a more convenient way to limit which DBs a user can connect to?
Well, yes and no. The situation is rather complex. Basically
I am using a create_user() SP created by "postgres" with
"security definer" (gasp). This works just fine, however, it
transfers createuser rights to *anyone* allowed to connect
to the database the function is in. I wanted to limit any
potential damage that could be done by users created that
way by restricting where they can possibly be used. I
thought that if a user only exists in the context of my
database it cannot be used to attack any other database no
matter which way pg_hba.conf and friends might be
misconfigured. Compartmentalization.

But perhaps I shouldn't count on the db_user_namespace
feature to exist in the future ?

> If the latter, the "samegroup" technique Philippe mentions seems like
> a good bet.
It isn't a given that all database users will have system
level equivalents hence samegroup won't work in all cases.
Also, will samegroup work predictably across remote
connections ?

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Viewing Database Scheme

[Doug McNaught]

Rich Shepard <[EMAIL PROTECTED]> writes:

> On Sat, 28 Jan 2006, Eric B. Ridge wrote:
>
>> Again, you can't use redirection via the psql prompt.  But you can do it
>> via your shell command line:
>> $ psql -c "\dt" > xrms.tables
>
>Well, that doesn't seem to be working here, either:
>
> [EMAIL PROTECTED] ~]$ psql -c contacts "\dt" > xrms.tables
> psql: FATAL:  database "\dt" does not exist
>
> [EMAIL PROTECTED] ~]$ psql "-c contacts \dt" > xrms.tables
> psql: FATAL:  database "rshepard" does not exist
>
> [EMAIL PROTECTED] ~]$ psql -c contacts
> psql: FATAL:  database "rshepard" does not exist

Eric left off the database argument (which defaults to your user
name), which was a little misleading, but his syntax does work:

[EMAIL PROTECTED]:~$ psql -c '\dt' gateway
List of relations
 Schema | Name | Type  |  Owner   
+--+---+--
...

Redirecting to a file is left as an exercise to the reader.

>> Alternatively, you can use psql's "\o [FILE]" command to redirect
>> query results to a file:
>> contacts=# \o /tmp/xrms.tables
>> contacts=# \dt
>> contacts=#
>> That'll send all output to /tmp/xrms.tables.
>
>This creates the file, but it's empty.

When I do this, after exiting 'psql' the file is populated.  It may be
a buffering issue, as another poster has said.  What you can also do
is close the file by setting output back to the terminal:

gateway=# \o /tmp/foo
gateway=# \dt
gateway=# \!cat /tmp/foo   <--- empty at this point
gateway=# \o   <--- switch output to the terminal
gateway=# \!cat /tmp/foo   <--- now it's there
List of relations
 Schema | Name | Type  |  Owner   
+--+---+--


I'm really surprised that you managed to think 'pg_dump --schema_only'
is an SQL command.  It's listed nowhere in the SQL syntax reference,
and is listed in the manual as one of the utility commands that are
run from the shell.

Your flailing about, randomly trying different argument combinations,
suggests that you aren't understanding what you read, and aren't
bothering to try to understand the error messages you get.  You could
have figured out the first issue, certainly, by reading manpages and
error messages.  The second one is a bit tricky unless you understand
Unix stdio buffering, which I wouldn't necessarily expect.  So I'll
give you that one.  :)

-Doug

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger

[Ken Winter]

Tom ~

Thanks for the news.  I'm on PostgreSQL 7.4.7, alas, and there's nothing I
can do about it because it resides on a host that I don't control.  I
suppose if I don't find an alternative, I could move my development work to
a local installation of PostgreSQL 8.1.

~ Ken

> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Saturday, January 28, 2006 1:26 PM
> To: Ken Winter
> Cc: PostgreSQL pg-general List
> Subject: Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger
> 
> "Ken Winter" <[EMAIL PROTECTED]> writes:
> > I ask because I was stuck for two days on the following error message:
> > ERROR:  syntax error at or near "LOOP"
> > CONTEXT:  compile of PL/pgSQL function "gen_history" near line 126
> > and neither of those tools offered any more help than that.  It turned
> out
> > the error was a missing ";" way back in line 53, and it took two days of
> > trial and error and staring at the code to find it.
> 
> What Postgres version are you using?
> 
> I would blame the backend more than the client tools for the failure to
> localize this syntax error.  We've made significant progress in 8.0
> and again in 8.1 on improving plpgsql's error messages --- if you are
> not on 8.1 the first thing to try is a backend upgrade.
> 
>   regards, tom lane






---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Viewing Database Scheme

[Eric B . Ridge]

On Jan 28, 2006, at 4:20 PM, Eric B. Ridge wrote:

Dude, "pg_dump" is not a psql command, nor is it a SQL command.   
It's a command-line program.  You run it from your shell:


$ pg_dump --schema-only pg_dump > xrms-schema.dmp


pardon my type-o.  This should read:

$ pg_dump --schema-only contacts > xrms-schema.dmp

eric

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Viewing Database Scheme

[Eric B. Ridge]

On Jan 28, 2006, at 4:12 PM, Rich Shepard wrote:

Please keep replies on the mailing list.



Again, you can't use redirection via the psql prompt.  But you can  
do it

via your shell command line:

$ psql -c "\dt" > xrms.tables


  Well, that doesn't seem to be working here, either:

[EMAIL PROTECTED] ~]$ psql -c contacts "\dt" > xrms.tables
psql: FATAL:  database "\dt" does not exist

[EMAIL PROTECTED] ~]$ psql "-c contacts \dt" > xrms.tables
psql: FATAL:  database "rshepard" does not exist

[EMAIL PROTECTED] ~]$ psql -c contacts
psql: FATAL:  database "rshepard" does not exist


That's because you've used the wrong syntax.

$ psql contacts -c "\dt" > xrms.tables

This is why I suggested you read the psql man page.

Alternatively, you can use psql's "\o [FILE]" command to redirect  
query results to a file:

  This creates the file, but it's empty.

  I'm curious what's gone wrong here. Nothing seems to be working  
as it

should.


Likely, the output is buffered.  Did you try quitting psql (via \q)  
before checking the contents of the file.


You should also read the psql man page and the output of psql's  
"\h" command.


  I've done both and tried various combinations of syntax. For  
example:


[EMAIL PROTECTED] ~]$ psql -d contacts -c pg_dump  -o xrms.tables
ERROR:  syntax error at or near "pg_dump" at character 1
LINE 1: pg_dump


Dude, "pg_dump" is not a psql command, nor is it a SQL command.  It's  
a command-line program.  You run it from your shell:


$ pg_dump --schema-only pg_dump > xrms-schema.dmp


  All I get are error messages.


You continually do the wrong things.  Read the man pages.  Seriously.

eric

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Viewing Database Scheme

[Rich Shepard]

On Sat, 28 Jan 2006, Eric B. Ridge wrote:


You can't do this via the psql prompt.  A simple "\d" will output to the
screen, automatically using your $PAGER if the output is too long to fit on
your screen.


Eric,

  That's what I assumed; perhaps I misunderstood Tom Lane's "what do you mean
'ofcourse'?".


Again, you can't use redirection via the psql prompt.  But you can do it
via your shell command line:

$ psql -c "\dt" > xrms.tables


  Well, that doesn't seem to be working here, either:

[EMAIL PROTECTED] ~]$ psql -c contacts "\dt" > xrms.tables
psql: FATAL:  database "\dt" does not exist

[EMAIL PROTECTED] ~]$ psql "-c contacts \dt" > xrms.tables
psql: FATAL:  database "rshepard" does not exist

[EMAIL PROTECTED] ~]$ psql -c contacts
psql: FATAL:  database "rshepard" does not exist

Alternatively, you can use psql's "\o [FILE]" command to redirect query 
results to a file:


contacts=# \o /tmp/xrms.tables
contacts=# \dt
contacts=#

That'll send all output to /tmp/xrms.tables.


  This creates the file, but it's empty.

  I'm curious what's gone wrong here. Nothing seems to be working as it
should.


You should also read the psql man page and the output of psql's "\h" command.


  I've done both and tried various combinations of syntax. For example:

[EMAIL PROTECTED] ~]$ psql -d contacts -c pg_dump  -o xrms.tables
ERROR:  syntax error at or near "pg_dump" at character 1
LINE 1: pg_dump

  All I get are error messages.

Thanks,

Rich

--
Richard B. Shepard, Ph.D.   |   Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic"
 Voice: 503-667-4517 Fax: 503-667-8863

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] My very first PL/pgSQL procedure...

[Philippe Ferreira]

why not have server a start at 1 and server b start at max int8/2.  that will give each server alot of orders before 
you have an issue.  They will will not have to play games with the sequence value.


Jim
 


Hi,

Your method is different from mine, but I think that both methods will 
face the same constraints in case of failover...


Anyway, my setup is working fine, so I won't change anything !
The only point is that it could have been useful to be able to lock a 
sequence...

It seems that it was possible with PostgreSQL 7.0, but was removed in 7.1 :
   http://archives.postgresql.org/pgsql-hackers/2001-10/msg00928.php

Best regards,
Philippe Ferreira.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Viewing Database Scheme

[Eric B. Ridge]

On Jan 28, 2006, at 3:20 PM, Rich Shepard wrote:

contacts=# \d | less
\d: extra argument "less" ignored


You can't do this via the psql prompt.  A simple "\d" will output to  
the screen, automatically using your $PAGER if the output is too long  
to fit on your screen.


  I can, however, run '\dt' and have it page normally. But, I  
cannot write

that output to a file using redirection or the tee command:

contacts=# \dt > xrms.tables
No matching relations found.
\dt: extra argument "xrms.tables" ignored


Again, you can't use redirection via the psql prompt.  But you can do  
it via your shell command line:


$ psql -c "\dt" > xrms.tables

Alternatively, you can use psql's "\o [FILE]" command to redirect  
query results to a file:


contacts=# \o /tmp/xrms.tables
contacts=# \dt
contacts=#

That'll send all output to /tmp/xrms.tables.

  I'll be sure to read that section. The \dt and \di commands show  
me what I

want, but I cannot redirect output to a file. What am I still missing,
please?


You should also read the psql man page and the output of psql's "\h"  
command.


eric

---(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: [GENERAL] Viewing Database Scheme

[Rich Shepard]

On Sat, 28 Jan 2006, Ezra Taylor wrote:


Can you use redirection.


  No. I get an error message.

contacts=# \dt > xrms.tables
No matching relations found.
\dt: extra argument "xrms.tables" ignored

Rich

--
Richard B. Shepard, Ph.D.   |   Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic"
 Voice: 503-667-4517 Fax: 503-667-8863

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Viewing Database Scheme

[Rich Shepard]

On Sat, 28 Jan 2006, Doug McNaught wrote:


You can either use 'pg_dump --schema-only' as another poster
suggested, or use the '\o' command in psql.


  When I try 'pg_dump --schema-only' I get a continuation prompt, even with a
semicolon at the end of the command line. A second semicolon produces this:

contacts=# pg_dump --schema-only;
contacts-# ;
ERROR:  syntax error at or near "pg_dump" at character 1
LINE 1: pg_dump
^
  If I use the \o command (with or without a file name) I get the command
prompt and no results.

Rich

--
Richard B. Shepard, Ph.D.   |   Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic"
 Voice: 503-667-4517 Fax: 503-667-8863

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Viewing Database Scheme

[Rich Shepard]

On Sat, 28 Jan 2006, Tom Lane wrote:


Not sure why you say "of course" there.  \d output is properly paginated
for me, and I believe for most people.  What platform are you on, and
what do you have environment variable PAGER set to?  Is the output of
plain old SELECT commands paginated for you?


Tom,

  Running Slackware-10.2 with 'less' as the pager.

  Here's what I'm seeing:

contacts=# \d | less
\d: extra argument "less" ignored

  I can, however, run '\dt' and have it page normally. But, I cannot write
that output to a file using redirection or the tee command:

contacts=# \dt > xrms.tables
No matching relations found.
\dt: extra argument "xrms.tables" ignored

and if I enter
contacts=# \dt | tee > xrms.tables

I see
 ...
 public | user_preference_type_options   | table | rshepard
 public | users  | table | rshepard
(57 rows)

\dt: extra argument "tee" ignored
\dt: extra argument ">" ignored
\dt: extra argument "xrms.tables" ignored

  Now, quitting postgres and reinvoking psql does fix the scroll-too-far
problem. But, something's not quite correct here; to wit:

contacts=# pg_dump --schema-only > xrms.txt;
contacts-# ;
ERROR:  syntax error at or near "pg_dump" at character 1
LINE 1: pg_dump


There is not a single command; you use queries against the system catalogs
for purposes like this. The "system catalogs" chapter of the manual gives
the details, but you can get a leg up by looking at the queries psql uses
for whatever form of \d seems closest to what you want. Start psql with -E
option to make it echo the queries it's using.


  I'll be sure to read that section. The \dt and \di commands show me what I
want, but I cannot redirect output to a file. What am I still missing,
please?

Thanks,

Rich
--
Richard B. Shepard, Ph.D.   |   Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic"
 Voice: 503-667-4517 Fax: 503-667-8863

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger

[Tony Caduto]

There is a better editor available:
http://www.amsoftwaredesign.com
http://www.amsoftwaredesign.com/lightning_admin.php

I invite you to try it out :-)

It provides all the find/replace goto line number etc, many of the 
features of a IDE such as Delphi 2006 or VS, it also has super nice 
tabbed based MDI.
We also offer code completion for schemas, user functions and built in 
functions.


I will probably get flamed but PG Admin III's function editing is not 
much better than notepad.


The server provides the error messages, and on versions before 8.0 they 
did not do much checking at all.
Version 8.1 does the best checking and I advise you to upgrade your 
server, it's not that difficult.


Another thing to keep in mind is if you restore a dump from 7.x to 8.x 
it will not do any checking unless you make a tweak, see this article:

http://www.milwaukeesoft.com/forums/viewtopic.php?t=83

As far as I know this affects 8.0 and 8.1, I am sure someone will 
correct me if I am wrong :-)


Hope this helps,

--
Tony Caduto
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql 8.x 






Ken Winter wrote:


Is a better PL/pgSQL editor / debugger than pgAdmin III or phpPgAdmin 
available anywhere?



As editors, the two pgAdmin tools apparently don’t offer elementary 
functions such as find and replace, which means I have to slurp my 
code out into a text editor when I really need these things.


So I guess my questions are:

* Is it the case that the pgAdmin tools actually do offer these
  features, but I just haven’t found them yet? If so, can you show
  me where they are?
* Are these functions available through add-ons to either pgAdmin
  tool? If so, where can I get these add-ons?
* Are there other PL/pgSQL editors that provide these functions?
  If so, what? Obviously, I’d prefer a free one, but would pay for
  one if necessary.

~ TIA

~ Ken




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger

[Raymond O'Donnell]

On 28 Jan 2006 at 13:16, Ken Winter wrote:

> I've learned that pgAdmin "syntax error" can mean anything from a
> missing ";" to a faulty block structure to an undeclared variable to. I

AFAIK, the error messages you're seeing are generated by the database 
backend and not by pgAdmin - pgAdmin simply passes on to you what it 
receives from the backend.

--Ray.

-
Raymond O'Donnell http://www.galwaycathedral.org/recitals
[EMAIL PROTECTED]  Galway Cathedral Recitals
-


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Basic questions about PQprepare()

[Alexander Farber]

Thank you Tom,

I also should have reread the docs on 1) and 2) -
it is mentioned there too that you have to call
PQfinish and PQclear even on failed operations.

Could you explain a bit more, where to get the OIDs?
There are really not many PQprepare examples
around (I wonder why, isn't it the fastest method?)

Regards
Alex

On 1/28/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> Alexander Farber <[EMAIL PROTECTED]> writes:
> > 4) How do I set the last PQprepare argument, the const Oid *paramTypes?
>
> You'd need to look up the OIDs of the parameter types.  In practice it's
> usally a lot easier to write the queries so that the parameter types can
> be inferred by the backend.

---(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: [GENERAL] Viewing Database Scheme

[Tom Lane]

Rich Shepard <[EMAIL PROTECTED]> writes:
> When I open the database
> with 'psql contacts' and ask to have the tables dumped (with \d), they go
> streaming by on the display. Of course, the bash 'tee' or 'less' commands
> don't work to allow me to capture the stream to a file or page through the
> output.

Not sure why you say "of course" there.  \d output is properly paginated
for me, and I believe for most people.  What platform are you on, and
what do you have environment variable PAGER set to?  Is the output of
plain old SELECT commands paginated for you?

>I've looked in the 8.1 pdf manual and the Douglas*2 book without spotting
> the command I need to get a list of all tables and their fields.

There is not a single command; you use queries against the system
catalogs for purposes like this.  The "system catalogs" chapter of
the manual gives the details, but you can get a leg up by looking
at the queries psql uses for whatever form of \d seems closest to
what you want.  Start psql with -E option to make it echo the
queries it's using.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger

[Tom Lane]

"Ken Winter" <[EMAIL PROTECTED]> writes:
> I ask because I was stuck for two days on the following error message:
> ERROR:  syntax error at or near "LOOP"
> CONTEXT:  compile of PL/pgSQL function "gen_history" near line 126
> and neither of those tools offered any more help than that.  It turned out
> the error was a missing ";" way back in line 53, and it took two days of
> trial and error and staring at the code to find it.

What Postgres version are you using?

I would blame the backend more than the client tools for the failure to
localize this syntax error.  We've made significant progress in 8.0
and again in 8.1 on improving plpgsql's error messages --- if you are
not on 8.1 the first thing to try is a backend upgrade.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Viewing Database Scheme

[Doug McNaught]

Rich Shepard <[EMAIL PROTECTED]> writes:

>When installing the application I specified the database name as 'contacts'
> (not very innovative or clever, but descriptive). When I open the database
> with 'psql contacts' and ask to have the tables dumped (with \d), they go
> streaming by on the display. Of course, the bash 'tee' or 'less' commands
> don't work to allow me to capture the stream to a file or page through the
> output.

You can either use 'pg_dump --schema-only' as another poster
suggested, or use the '\o' command in psql.

-Doug

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Viewing Database Scheme

[Ezra Taylor]

Can you use redirection.

On 1/28/06, Rich Shepard <[EMAIL PROTECTED]> wrote:
>   I'm trying to help the XRMS developers add postgres support ('cause that's
> what I use). They've done well so far with help from other postgres users,
> but now I've been asked to help getting the indices correct.
>
>   When installing the application I specified the database name as 'contacts'
> (not very innovative or clever, but descriptive). When I open the database
> with 'psql contacts' and ask to have the tables dumped (with \d), they go
> streaming by on the display. Of course, the bash 'tee' or 'less' commands
> don't work to allow me to capture the stream to a file or page through the
> output.
>
>   I've looked in the 8.1 pdf manual and the Douglas*2 book without spotting
> the command I need to get a list of all tables and their fields. When I've
> developed postgres applications I know the schema so this has not been an
> issue before.
>
>   Please pass me a clue stick on how to view all the tables in this
> application.
>
> TIA,
>
> Rich
>
> --
> Richard B. Shepard, Ph.D.   |   Author of "Quantifying 
> Environmental
> Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy 
> Logic"
>  Voice: 503-667-4517 Fax: 503-667-8863
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>


--
Ezra Taylor

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Viewing Database Scheme

[Jim Buttafuoco]

use pg_dump --schema-only 




-- Original Message ---
From: Rich Shepard <[EMAIL PROTECTED]>
To: pgsql-general@postgresql.org
Sent: Sat, 28 Jan 2006 10:14:05 -0800 (PST)
Subject: [GENERAL] Viewing Database Scheme

> I'm trying to help the XRMS developers add postgres support ('cause that's
> what I use). They've done well so far with help from other postgres users,
> but now I've been asked to help getting the indices correct.
> 
>When installing the application I specified the database name as 'contacts'
> (not very innovative or clever, but descriptive). When I open the database
> with 'psql contacts' and ask to have the tables dumped (with \d), they go
> streaming by on the display. Of course, the bash 'tee' or 'less' commands
> don't work to allow me to capture the stream to a file or page through the
> output.
> 
>I've looked in the 8.1 pdf manual and the Douglas*2 book without spotting
> the command I need to get a list of all tables and their fields. When I've
> developed postgres applications I know the schema so this has not been an
> issue before.
> 
>Please pass me a clue stick on how to view all the tables in this
> application.
> 
> TIA,
> 
> Rich
> 
> -- 
> Richard B. Shepard, Ph.D.   |   Author of "Quantifying 
> Environmental
> Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy 
> Logic"
>  Voice: 503-667-4517 Fax: 503-667-8863
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
--- End of Original Message ---


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Seeking a better PL/pgSQL editor-debugger

[Ken Winter]

Is a better PL/pgSQL editor / debugger than pgAdmin III or
phpPgAdmin available anywhere?

 

I ask because I was stuck for two days on the following
error message:

 

ERROR:  syntax error at or near "LOOP"

CONTEXT:  compile of PL/pgSQL function "gen_history"
near line 126

 

…and neither of those tools offered any more help
than that.  It turned out the error was a missing “;” way back
in line 53, and it took two days of trial and error and staring at the code to
find it.  (On the positive side, pgAdmin III’s use of different
colors to distinguish different program elements [variables, keywords, string
constants, comments] made the staring part easier to do.)

 

I’ve learned that pgAdmin “syntax error”
can mean anything from a missing “;” to a faulty block structure to
an undeclared variable to… I don’t know what else, and as witness
the example that error may be nowhere near the line that is flagged.  So I’m
looking for a PL/pgSQL tool that would at least provide more diagnostic error messages.
 Preferably, it would also offer some sort of “breakpoint” function
to let the developer see the values of variables at specified points in the
code.  

 

I looked on the pgAdmin web site.  The only place a PL/pgSQL
debugger was mentioned was on the “to do” page, under “major projects”
(http://www.pgadmin.org/development/todo.php),
which I guess means don’t hold your breath. 

 

As editors, the two pgAdmin tools apparently don’t offer
elementary functions such as find and replace, which means I have to slurp my
code out into a text editor when I really need these things.

 

So I guess my questions are:

 


 Is it the case that the pgAdmin tools actually do
 offer these features, but I just haven’t found them yet?  If
 so, can you show me where they are?
 Are these functions available through add-ons to either
 pgAdmin tool?  If so, where can I get these add-ons?
 Are there other PL/pgSQL editors that provide these functions? 
 If so, what?  Obviously, I’d prefer a free one, but would pay
 for one if necessary.


 

~ TIA

~ Ken

Re: [GENERAL] My very first PL/pgSQL procedure...

[Jim Buttafuoco]

why not have server a start at 1 and server b start at max int8/2.  that will 
give each server alot of orders before 
you have an issue.  They will will not have to play games with the sequence 
value.

Jim


-- Original Message ---
From: Philippe Ferreira <[EMAIL PROTECTED]>
To: Martijn van Oosterhout 
Cc: [EMAIL PROTECTED], pgsql-general@postgresql.org
Sent: Sat, 28 Jan 2006 17:46:55 +0100
Subject: Re: [GENERAL] My very first PL/pgSQL procedure...

> >I think you need to provide a rationale why you want to control the
> >value of the sequence in such a way anyway, because you're trying to do
> >something that the system isn't likely to support. The numbers are
> >supposed to be "opaque", the actual values are not supposed to be
> >relevent.
> >
> >Have a nice day,
> >  
> >
> Hi,
> 
> Actually, I'm building a switchover/failover system (based on WAL) for 
> my own needs.
> 
> There is no problem for the switchover, because the data is properly 
> synched during
> this procedure.
> 
> However, when I have to start the failover on a secondary server, some 
> changes can
> be "uncommited" at the time of the failover.
> So, as soon as the primary PostgreSQL server is up again, I have to resync
> (automatically or manually) these "uncommited" changes to the failover 
> node, before
> switching properly to the primary.
> 
> Actually, I've got a sequence which generates order numbers. These codes 
> will be
> always unique, even in case of failover, since I alter the definition of the
> sequence depending on the server (primary or secondary), with a suffix 
> of 'A' or 'B'.
> For example, imagine the following scenario :
> 
> 
> 
> Server A : Server B :
> == ==
> 1000A
> 1001A
> 1002A
> ---[SWITCHOVER]-->
>  1003B
>  1004B
>  1005B
>  1006B
> <--[SWITCHOVER]---
> 1007A
> 1008A
> 1009A
> 1010A  [FAILOVER]--> 1010B
> 1011A1011B
> 1012A  -
> 1013A  -
> [RESYNC]-->  1010A
> [RESYNC]-->  1011A
> [RESYNC]-->  1012A
> [RESYNC]-->  1013A
> <--[SWITCHOVER]---
> 1014A
> 1015A
> 
> 
> 
> So, in this example, if I don't increment the last value of my
> sequence from 1011 to 1013 (before my last "SWITCHOVER"), it
> will output "1012A" (instead of 1014A), and my new record will
> conflict with an existing one !!
> 
> Thank you for your interest,
> Philippe Ferreira.
> 
> ---(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
--- End of Original Message ---


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Viewing Database Scheme

[Rich Shepard]

  I'm trying to help the XRMS developers add postgres support ('cause that's
what I use). They've done well so far with help from other postgres users,
but now I've been asked to help getting the indices correct.

  When installing the application I specified the database name as 'contacts'
(not very innovative or clever, but descriptive). When I open the database
with 'psql contacts' and ask to have the tables dumped (with \d), they go
streaming by on the display. Of course, the bash 'tee' or 'less' commands
don't work to allow me to capture the stream to a file or page through the
output.

  I've looked in the 8.1 pdf manual and the Douglas*2 book without spotting
the command I need to get a list of all tables and their fields. When I've
developed postgres applications I know the schema so this has not been an
issue before.

  Please pass me a clue stick on how to view all the tables in this
application.

TIA,

Rich

--
Richard B. Shepard, Ph.D.   |   Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic"
 Voice: 503-667-4517 Fax: 503-667-8863

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] My very first PL/pgSQL procedure...

[Philippe Ferreira]

I think you need to provide a rationale why you want to control the
value of the sequence in such a way anyway, because you're trying to do
something that the system isn't likely to support. The numbers are
supposed to be "opaque", the actual values are not supposed to be
relevent.

Have a nice day,
 


Hi,

Actually, I'm building a switchover/failover system (based on WAL) for 
my own needs.


There is no problem for the switchover, because the data is properly 
synched during

this procedure.

However, when I have to start the failover on a secondary server, some 
changes can

be "uncommited" at the time of the failover.
So, as soon as the primary PostgreSQL server is up again, I have to resync
(automatically or manually) these "uncommited" changes to the failover 
node, before

switching properly to the primary.

Actually, I've got a sequence which generates order numbers. These codes 
will be

always unique, even in case of failover, since I alter the definition of the
sequence depending on the server (primary or secondary), with a suffix 
of 'A' or 'B'.

For example, imagine the following scenario :



Server A : Server B :
== ==
1000A
1001A
1002A
   ---[SWITCHOVER]-->
1003B
1004B
1005B
1006B
   <--[SWITCHOVER]---
1007A
1008A
1009A
1010A  [FAILOVER]--> 1010B
1011A1011B
1012A  -
1013A  -
   [RESYNC]-->  1010A
   [RESYNC]-->  1011A
   [RESYNC]-->  1012A
   [RESYNC]-->  1013A
   <--[SWITCHOVER]---
1014A
1015A



So, in this example, if I don't increment the last value of my
sequence from 1011 to 1013 (before my last "SWITCHOVER"), it
will output "1012A" (instead of 1014A), and my new record will
conflict with an existing one !!

Thank you for your interest,
Philippe Ferreira.

---(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: [GENERAL] creating users per database

[Tom Lane]

Karsten Hilbert <[EMAIL PROTECTED]> writes:
> I have a faint memory of it being possible to create users
> "inside" *one* given database by way of a particular "create
> user" syntax along the lines of:

>  create user [EMAIL PROTECTED] ...;

There is the db_user_namespace kluge^H^H^H^H^Hparameter, but it's
pretty ugly.  Do you really need DB-specific user names, or just
a more convenient way to limit which DBs a user can connect to?  If
the latter, the "samegroup" technique Philippe mentions seems like
a good bet.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Basic questions about PQprepare()

[Tom Lane]

Alexander Farber <[EMAIL PROTECTED]> writes:
> 1) If PQconnectdb fails, do I still need to PQfinish the returned pointer?

Yes, if you don't want to leak memory.

> 2) Similar, if PQprepare fails, do I still need to PQclear its result?

Yes, if you don't want to leak memory.

> 3) Do I have to PQclear(res) inbetween if I want to prepare another query?

They are not comparable actions --- you can do them in either order.  In
practice though I don't see why you wouldn't PQclear the result of a
PREPARE as soon as you'd checked that it wasn't conveying an error.

> 4) How do I set the last PQprepare argument, the const Oid *paramTypes?

You'd need to look up the OIDs of the parameter types.  In practice it's
usally a lot easier to write the queries so that the parameter types can
be inferred by the backend.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] My very first PL/pgSQL procedure...

[Martijn van Oosterhout]

On Thu, Jan 26, 2006 at 09:04:25PM +0100, Philippe Ferreira wrote:
> But because the sequence could not be locked, some concurrent
> transactions could have already raised it's current value in the
> meantime to, say, "1002", before the effective execution of setval().

Umm, locking sequences won't ever happen. The *whole point* of
sequences is that you get a unique number and you don't have to wait
for it. By locking you'd be telling other people they have to wait.

> So, instead of raising the value to 1000, my function could have
> done the opposite (from 1002 to 1000) ! And the two next "INSERT"
> using this sequence would then break !!

I think you need to provide a rationale why you want to control the
value of the sequence in such a way anyway, because you're trying to do
something that the system isn't likely to support. The numbers are
supposed to be "opaque", the actual values are not supposed to be
relevent.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

Re: [GENERAL] Encoding errors when upgrading from 7.4 to 8.1

[Martijn van Oosterhout]

On Thu, Jan 26, 2006 at 06:07:45PM +, Adam Witney wrote:
> Hi,
> 
> I am upgrading from 7.4.8 -> 8.1.2 on Linux 2.6.14.3 #1 SMP
> 
> I have installed 8.1.2 and created the database (with encoding 'UNICODE', as
> I had done in 7.4.8) and am trying to load a 7.4.8 dump file but I am
> getting a few errors like this:
> 
> psql:bugasbase2-backup:45880: ERROR:  invalid UTF-8 byte sequence detected
> near byte 0xb5
> CONTEXT:  COPY array_scheme, line 17560, column gene_identifier: "[EMAIL 
> PROTECTED]
> (0G11)"

There were some changes in the checking. PostgreSQL used to allow
invalid utf-8 sequences in that it no longer accepts. You basically
need to clean up the data. Something like what's suggested here:

http://archives.postgresql.org/pgsql-hackers/2005-12/msg00511.php

may be helpful. This whole thread is useful actually...
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

Re: [GENERAL] Postgres 8.1.2, Java, JDO, and case sensitivity woes

[Marko Kreen]

On 1/28/06, Matthew Hixson <[EMAIL PROTECTED]> wrote:
> So, it would seem that table names are case insensitive in select
> statements, but case sensitive in prepare statements.
>Can someone confirm or refute that?

http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

--
marko

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] mac os x compile failure

[Michael Glaesemann]

On Jan 24, 2006, at 9:21 , Michael Glaesemann wrote:



On Jan 22, 2006, at 3:10 , Tom Lane wrote:


That's odd --- AFAIR I've never had trouble building bison on my OS X
laptop.  What happens when you try?


I neglected to take any notes. I remember it was complaining about  
"muscle" something-or-other during make.


I have mixed emotions about it, but bison 1.875 built with no  
problems the very first time I tried when I went in to service the  
machine. No evidence at all of the problems I had building  
bison-1.875 and bison-2.1 last week. I can't imagine what I did  
differently. It was a straightforward ./configure --prefix=/usr/ 
local, make, make install. Kinda feel silly that I can't replicate  
the error, but glad that it went fine.


One workaround would be to check out from CVS and then drop in the  
bison

output files from the 8.1.2 release tarball.


I'll give that a shot when I get back to that machine, probably  
this coming weekend.


Install from CVS when just fine. No error due to F_FULLSYNC, and  
configure properly displayed

checking whether F_FULLFSYNC is declared... no

Mac OS X 10.2.8
./configure --prefix=/usr/local/pgsql-cvs --with-pgport=5431 -- 
mandir=/usr/local/share/man/ --with-includes=/sw/include/ --with- 
libraries=/sw/lib/


Full output from configure, make, make install available if desired.  
make check failed because it wasn't able to initdb (not enough memory  
available) and I didn't want to mess around with changing shmem*. (I  
don't even remember how to do that on an OS X 10.2 box.) Is there a  
way to set the number of connections lower for the regression tests?


Thanks again, Tom. Looks like this fixed it.

Michael Glaesemann
grzm myrealbox com


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] creating users per database

[Philippe Ferreira]

Hello,

I don't think you can create users *inside* a database, but for each 
database,

you can create global users and make them members of a group having the same
name than your database.

Then, in pg_hba.conf, the folllowing line will give to your users the 
permission to

connect to the database they belong :

   hostsamegroup   all 0.0.0.0 0.0.0.0 md5

Bye,
Philippe Ferreira.


Hi all,

I have a faint memory of it being possible to create users
"inside" *one* given database by way of a particular "create
user" syntax along the lines of:

create user [EMAIL PROTECTED] ...;

or similar.

Was this ever possible in PostgreSQL or is my memory playing
tricks on me ? I went back through the manuals all the way
to 6.5 and searched archives.postgresql.com/Google, alas, to
no avail.

I am aware that I can (and should) restrict users to
databases by way of pg_hba and friends.

Karsten
 




---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] SQL Lint

[Andreas Kretschmer]

Roderick A. Anderson <[EMAIL PROTECTED]> schrieb:
> Anyone aware of a lint for SQL ( 92, 99, PostgreSQL )?  We want to look 
> quickly at the code we're porting for any MySQL-isms so we can figure out 
> what it is suppose to be so the final post will go smoother.

Nice idea. I found http://dbs.informatik.uni-halle.de/sqllint/, but it
is very stricly.


HTH, Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] creating users per database

[Karsten Hilbert]

Hi all,

I have a faint memory of it being possible to create users
"inside" *one* given database by way of a particular "create
user" syntax along the lines of:

 create user [EMAIL PROTECTED] ...;

or similar.

Was this ever possible in PostgreSQL or is my memory playing
tricks on me ? I went back through the manuals all the way
to 6.5 and searched archives.postgresql.com/Google, alas, to
no avail.

I am aware that I can (and should) restrict users to
databases by way of pg_hba and friends.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 6: explain analyze is your friend