[GENERAL] What is statement ID of table?

[Jignesh Shah]

Hi,

Could any one please tell me what is statement ID of table? How to get it
and in which scenarios it can be helpful? Any documentation or example about
statement ID would also really helpful for me.
Thanks,
Jignesh

Re: [GENERAL] interface for "non-SQL people"

[Lew]

Martin Gainty wrote:

Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
 
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene 
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede 
unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. 
Diese Nachricht dient lediglich dem Austausch von Informationen und 
entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten 
Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt 
uebernehmen.


Ce message est confidentiel et peut être privilégié. 
Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté 
que pour satisfaire informez l'expéditeur. 
N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. 
Ce message sert à l'information seulement et n'aura pas n'importe quel 
effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune 
responsabilité pour le contenu fourni.


Confidentiality disclaimers on messages posted to a public forum are rather 
pointless, especially if in languages other than the /lingua franca/ of the forum.


--
Lew

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Building PG 8.4.1 with ossp-uuid on Centos 5.3

[Tom Lane]

Christophe Pettus  writes:
> On Oct 10, 2009, at 11:53 AM, Tom Lane wrote:
>> Whose uuid package are you using?
>   http://www.ossp.org/pkg/lib/uuid/
> Building from source.

> It is indeed installing it in /usr/local/lib, but the ldconfig was  
> set.  What's irritating is that (looking at config.log) it is finding  
> the library just fine, but not finding the uuid_export function inside  
> of it.

Hmph.  I don't know if there's more than one uuid package in the wild,
but I see from
http://cvs.fedoraproject.org/viewvc/rpms/uuid/devel/uuid.spec?revision=1.17&view=markup
that the package that's standard in recent Fedora is uuid 1.6.1 from
http://www.ossp.org/pkg/lib/uuid/
and I can attest that Postgres does build against that.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] interface for "non-SQL people"

[Martin Gainty]

oracle handles html output with packages ..i would assume pg would have similar 
sgml output capability?
crystal has been limited to ODBC dsn (datasources) althogh sap seem to be open 
to a more Opensource approach using 
JDBC based data sources

Martin Gainty 
__ 
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
 
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.




> Date: Sat, 10 Oct 2009 21:36:19 -0400
> From: jus...@emproshunts.com
> To: pero...@gmail.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] interface for "non-SQL people"
> 
> pere roca wrote:
> >   hi,
> >   some nice tool over there to let non-SQL knowing people to construct their
> > queries? I'm using pgAdmin III but I know some SQL. 
> >   there is no other option than constructing an HTML with forms, drop-down
> > menus...?
> >
> >   thanks,
> >   pERE
> >   
> 
> Your best bet which is not free is Crystal Reports it can do  all the 
> above you list. It can automate creation of  HTML, email, export Excel,  
> ad hoc filters in drop downs, and text inputs,  Graphs and  all kinds of 
> other stuff. 
> http://www.sap.com/solutions/sapbusinessobjects/sme/reporting-dashboarding/index.epx
> 
> its designed to be easy to use for the NON have not a clue 
> SQL/programmer people.
> 
> There are other software packages out there.  Note I have never used any 
> of these can not comment... 
> http://www.inetsoft.com/products/StyleReportEE/
> http://www.pentaho.com/products/reporting/
> http://www.inetsoftware.de/products/crystal-clear
> http://www.actuate.com/products/
> http://www.agata.org.br/
> http://jasperforge.org/plugins/project/project_home.php?projectname=jasperreports
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
  
_
Your E-mail and More On-the-Go. Get Windows Live Hotmail Free.
http://clk.atdmt.com/GBL/go/171222985/direct/01/

Re: [GENERAL] interface for "non-SQL people"

[justin]

pere roca wrote:

  hi,
  some nice tool over there to let non-SQL knowing people to construct their
queries? I'm using pgAdmin III but I know some SQL. 
  there is no other option than constructing an HTML with forms, drop-down

menus...?

  thanks,
  pERE
  


Your best bet which is not free is Crystal Reports it can do  all the 
above you list. It can automate creation of  HTML, email, export Excel,  
ad hoc filters in drop downs, and text inputs,  Graphs and  all kinds of 
other stuff. 
http://www.sap.com/solutions/sapbusinessobjects/sme/reporting-dashboarding/index.epx


its designed to be easy to use for the NON have not a clue 
SQL/programmer people.


There are other software packages out there.  Note I have never used any 
of these can not comment... 
http://www.inetsoft.com/products/StyleReportEE/

http://www.pentaho.com/products/reporting/
http://www.inetsoftware.de/products/crystal-clear
http://www.actuate.com/products/
http://www.agata.org.br/
http://jasperforge.org/plugins/project/project_home.php?projectname=jasperreports

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to send multiple SQL commands from Python?

[Adrian Klaver]

On Saturday 10 October 2009 5:48:39 pm Massa, Harald Armin wrote:
> Adrian,
>
> While I was walking the dog I thought of a better solution.
>
> > sql_str = """ALTER TABLE  %(xn)s OWNER TO xdev;
> > GRANT ALL ON TABLE  %(xn)s TO xdev;
> > REVOKE ALL ON TABLE %(xn)s FROM PUBLIC;
> > GRANT SELECT ON TABLE %(xn)s TO PUBLIC;"""
> >
> > cur.execute(sql_str,{'xn':table_name})
> > --
>
> This will not work.
>
> Because: "xn" will be escaped as "data", that is... the resulting string
> will be:
>
> ALTER TABLE E'waschbaer' ONER TO xdev;
>
> which obviously is not what you want.

Thanks for pointing that out.

-- 
Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to send multiple SQL commands from Python?

[Massa, Harald Armin]

Adrian,

While I was walking the dog I thought of a better solution.
>
> sql_str = """ALTER TABLE  %(xn)s OWNER TO xdev;
> GRANT ALL ON TABLE  %(xn)s TO xdev;
> REVOKE ALL ON TABLE %(xn)s FROM PUBLIC;
> GRANT SELECT ON TABLE %(xn)s TO PUBLIC;"""
>
> cur.execute(sql_str,{'xn':table_name})
> --
>
This will not work.

Because: "xn" will be escaped as "data", that is... the resulting string
will be:

ALTER TABLE E'waschbaer' ONER TO xdev;

which obviously is not what you want.

You can do

sql=sql_str % dict(xn=table_name)

and after taht

cur.execute(sql)

be aware that there is no quoting; so there is the danger of SQL injection,
table_name should not come from outside.




Mutliline strings are easy in Python by using triple-quoting:

sql_str = """ALTER TABLE  %(xn)s OWNER TO xdev;
GRANT ALL ON TABLE  %(xn)s TO xdev;
REVOKE ALL ON TABLE %(xn)s FROM PUBLIC;
GRANT SELECT ON TABLE %(xn)s TO PUBLIC;"""


 With psycopg2 there is also the cursor-attribute "query", so with:

print cur.query

you can see the query actually passed to PostgreSQL (with %(whatever)s
replaced by psycopg2s calls to libpq)

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
%s is too gigantic of an industry to bend to the whims of reality

Re: [GENERAL] What's wrong with this regexp?

[Randal L. Schwartz]

> "Nick" == Nick   writes:

Nick> SELECT TRUE WHERE '/steps/?step=10' ~ '^\/steps\/\?step=10$'

Here's the first clue:

merlyn=# select '^\/steps\/\?step=10$';
WARNING:  nonstandard use of escape in a string literal
LINE 1: select '^\/steps\/\?step=10$';
   ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
 ?column?  
---
 ^/steps/?step=10$
(1 row)

Notice the \'s just disappeared, so it's not gonna have much good
for the ?, which will be interpreted as the "optional" suffix.

Even adding 'E' (from the hint) isn't quite enough:

merlyn=# select E'^\/steps\/\?step=10$';
 ?column?  
---
 ^/steps/?step=10$
(1 row)

We need the resulting value to have \? in it, and that's not
there yet.  So, that's the clue.  Don't need \/, but do need \\?, so
it looks like this:

merlyn=# select E'^/steps/\\?step=10$';
  ?column?  

 ^/steps/\?step=10$
(1 row)

Aha, and now we have the right string for the regex engine, so
let's test that match:

merlyn=# select '/steps/?step=10' ~ E'^/steps/\\?step=10$';
 ?column? 
--
 t
(1 row)

Bingo.  True.

-- 
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
 http://www.stonehenge.com/merlyn/>
Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc.
See http://methodsandmessages.vox.com/ for Smalltalk and Seaside discussion

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What's wrong with this regexp?

[Tim Landscheidt]

Nick  wrote:

> SELECT TRUE WHERE '/steps/?step=10' ~ '^\/steps\/\?step=10$'

> Im guessing its an escape issue, but where am I going wrong?

You need to double-escape the question mark: Once for the
string literal, once for the regular expression (and you do
not need to escape the slashes). This gives:

| tim=# SELECT TRUE WHERE '/steps/?step=10' ~ E'^/steps/\\?step=10$';
|  bool
| --
|  t
| (1 Zeile)

| tim=#

Tim


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] What's wrong with this regexp?

[Nick]

SELECT TRUE WHERE '/steps/?step=10' ~ '^\/steps\/\?step=10$'

Im guessing its an escape issue, but where am I going wrong?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Prepared statements with a variable number of parameters

[Raymond O'Donnell]

On 10/10/2009 18:52, Pavel Stehule wrote:

> 2009/10/10 Raymond O'Donnell :

>> My question is, what do people normally do when there's a variable
>> number of parameters? - for example:
>>
>>  select . where item_id in ( ... );
>>
> 
> You can use arrays here.
> 
> select ... where item_id = ANY(string_to_array($1, ','))

Nice one! Thanks Pavel.

My item_id values are integers, so I added ::integer[] to the above and
all works nicely:

...where item_id = any(string_to_array($1, ',')::integer[])

Thanks again,

Ray.


--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to send multiple SQL commands from Python?

[Adrian Klaver]

On Saturday 10 October 2009 1:24:05 pm Kynn Jones wrote:
> On Sat, Oct 10, 2009 at 4:14 PM, Adrian Klaver  wrote:
> > On Saturday 10 October 2009 12:27:39 pm Adrian Klaver wrote:
> > > On Saturday 10 October 2009 12:09:29 pm Kynn Jones wrote:
> > > > I am porting some code from Perl to Python; in the Perl original I
> > > > use either DBI::do or a rickety home-built module to pass multiple
> > > > SQL statements (as one single block of SQL) to the Pg server.  The
> > > > typical usage is something like this:
> > > > $dbh->do( < > > > ALTER TABLE $xn OWNER TO xdev;
> > > > GRANT ALL ON TABLE $xn TO xdev;
> > > >
> > > > REVOKE ALL ON TABLE $xn FROM PUBLIC;
> > > > GRANT SELECT ON TABLE $xn TO PUBLIC;
> > > > EOSQL
> > > >
> > > >
> > > > How can I do this sort of thing from Python?
> > > >
> > > > I've looked into the docs for psycopg2, but I can't find anything
> > > > like the do command used above.  Did I overlook it?  If not, what can
> > > > I use instead?
> > > >
> > > > I'm not wedded to psycopg2, in fact its lack of documentation worries
> >
> > me;
> >
> > > > if there's a better alternative that I can use from Python please let
> >
> > me
> >
> > > > know.
> > > >
> > > > TIA!
> > > >
> > > > kynn
> > >
> > > One way
> > > Using psycopg2
> > > DSN = "dbname=? user=? port=? host=?"
> > > con = psycopg2.connection(DSN)
> > > cur = con.cursor()
> > > cur.execute(statement1)
> > > cur.execute(statement2)
> > > 
> > > con.commit()
> > >
> > > Another way, not tested, is triple quote entire block above and pass it
> >
> > to
> >
> > > cur.execute.
> > >
> > > --
> > > Adrian Klaver
> > > akla...@comcast.net
> >
> > I missed the part where you wanted to do it as one block with variables
> > already
> > substituted.
> >
> > For that I usually do something like:
> >
> > sql_str = "ALTER TABLE " + $xn + " OWNER TO xdev;"
> > sql_str += "GRANT ALL ON TABLE " + $xn + " TO xdev;"
> > sql_str += "REVOKE ALL ON TABLE " + $xn + " FROM PUBLIC;"
> > sql_str += "GRANT SELECT ON TABLE " + $xn + " TO PUBLIC;"
> >
> > cur.execute(sql_str)
>
>
> kynn

While I was walking the dog I thought of a better solution.

sql_str = "ALTER TABLE  %(xn)s OWNER TO xdev;"
sql_str += "GRANT ALL ON TABLE  %(xn)s TO xdev;"
sql_str += "REVOKE ALL ON TABLE %(xn)s FROM PUBLIC;"
sql_str += "GRANT SELECT ON TABLE %(xn)s TO PUBLIC;"

cur.execute(sql_str,{'xn':table_name})
-- 
Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Cannot upgrade to 8.4.1 on Windows

[Bill Todd]

I am a PostgreSQL novice. I successfully  installed 8.3.3 on a Windows 
XP Pro machine using the pgInstaller and have been using it since. Now I 
want to upgrade to 8.4.1.


The only installer I can find for Windows for 8.4.1 is the One Click 
installer provided by EnterpriseDB. If I attempt to run the One Click 
installer while logged on as the postgresql user it fails immediately by 
displaying the infamous Windows dialog that says the program has 
encountered an error and must be shutdown and asking if you want  to 
send a report to Microsoft or not.


Thinking that this might be a rights issue I added the postgresql user 
to the Administrators  group and tried the install again. This time a 
dialog appeared stating that  the Microsoft C++ RTL installer had 
encountered an error. The dialog then disappeared. In both cases the 
installer never reached the point of displaying its splash screen.


Next, I tried running the installation under my normal login, which is 
also a member of the Administrators group, and it ran. Of course the 
installation it created is unusable because PostgreSQL now runs under my 
account, the postgres database does not exist, a database with my 
account name was created and I am sure other things are messed up also. 
Therefore I uninstalled 8.4.1 and deleted the installation directory.


Is there a complete set of instructions for installing 8.4.1 on Windows 
somewhere? I have not been able to find anything using Google and the 
information in the manual is  incompatible with the One Click installer. 
If there is no accurate documentation can someone suggest what I am 
doing wrong?  Thanks.


Bill

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to send multiple SQL commands from Python?

[Rich Shepard]

On Sat, 10 Oct 2009, Adrian Klaver wrote:


One way
Using psycopg2
DSN = "dbname=? user=? port=? host=?"
con = psycopg2.connection(DSN)
cur = con.cursor()
cur.execute(statement1)
cur.execute(statement2)

con.commit()

Another way, not tested, is triple quote entire block above and pass it to
cur.execute.


  Both work with pysqlite so I imagine they'll work with postgres as well.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to send multiple SQL commands from Python?

[Kynn Jones]

On Sat, Oct 10, 2009 at 4:14 PM, Adrian Klaver  wrote:

> On Saturday 10 October 2009 12:27:39 pm Adrian Klaver wrote:
> > On Saturday 10 October 2009 12:09:29 pm Kynn Jones wrote:
> > > I am porting some code from Perl to Python; in the Perl original I use
> > > either DBI::do or a rickety home-built module to pass multiple SQL
> > > statements (as one single block of SQL) to the Pg server.  The typical
> > > usage is something like this:
> > > $dbh->do( < > > ALTER TABLE $xn OWNER TO xdev;
> > > GRANT ALL ON TABLE $xn TO xdev;
> > >
> > > REVOKE ALL ON TABLE $xn FROM PUBLIC;
> > > GRANT SELECT ON TABLE $xn TO PUBLIC;
> > > EOSQL
> > >
> > >
> > > How can I do this sort of thing from Python?
> > >
> > > I've looked into the docs for psycopg2, but I can't find anything like
> > > the do command used above.  Did I overlook it?  If not, what can I use
> > > instead?
> > >
> > > I'm not wedded to psycopg2, in fact its lack of documentation worries
> me;
> > > if there's a better alternative that I can use from Python please let
> me
> > > know.
> > >
> > > TIA!
> > >
> > > kynn
> >
> > One way
> > Using psycopg2
> > DSN = "dbname=? user=? port=? host=?"
> > con = psycopg2.connection(DSN)
> > cur = con.cursor()
> > cur.execute(statement1)
> > cur.execute(statement2)
> > 
> > con.commit()
> >
> > Another way, not tested, is triple quote entire block above and pass it
> to
> > cur.execute.
> >
> > --
> > Adrian Klaver
> > akla...@comcast.net
>
> I missed the part where you wanted to do it as one block with variables
> already
> substituted.
>
> For that I usually do something like:
>
> sql_str = "ALTER TABLE " + $xn + " OWNER TO xdev;"
> sql_str += "GRANT ALL ON TABLE " + $xn + " TO xdev;"
> sql_str += "REVOKE ALL ON TABLE " + $xn + " FROM PUBLIC;"
> sql_str += "GRANT SELECT ON TABLE " + $xn + " TO PUBLIC;"
>
> cur.execute(sql_str)
>

Many thanks!


> On a side note the Psycopg mailing list is:
> http://lists.initd.org/mailman/listinfo/psycopg
>
> It is very helpful and makes up for the documentation issues.


That's good to know.  Thanks again.

kynn

Re: [GENERAL] How to send multiple SQL commands from Python?

[Adrian Klaver]

On Saturday 10 October 2009 12:27:39 pm Adrian Klaver wrote:
> On Saturday 10 October 2009 12:09:29 pm Kynn Jones wrote:
> > I am porting some code from Perl to Python; in the Perl original I use
> > either DBI::do or a rickety home-built module to pass multiple SQL
> > statements (as one single block of SQL) to the Pg server.  The typical
> > usage is something like this:
> > $dbh->do( < > ALTER TABLE $xn OWNER TO xdev;
> > GRANT ALL ON TABLE $xn TO xdev;
> >
> > REVOKE ALL ON TABLE $xn FROM PUBLIC;
> > GRANT SELECT ON TABLE $xn TO PUBLIC;
> > EOSQL
> >
> >
> > How can I do this sort of thing from Python?
> >
> > I've looked into the docs for psycopg2, but I can't find anything like
> > the do command used above.  Did I overlook it?  If not, what can I use
> > instead?
> >
> > I'm not wedded to psycopg2, in fact its lack of documentation worries me;
> > if there's a better alternative that I can use from Python please let me
> > know.
> >
> > TIA!
> >
> > kynn
>
> One way
> Using psycopg2
> DSN = "dbname=? user=? port=? host=?"
> con = psycopg2.connection(DSN)
> cur = con.cursor()
> cur.execute(statement1)
> cur.execute(statement2)
> 
> con.commit()
>
> Another way, not tested, is triple quote entire block above and pass it to
> cur.execute.
>
> --
> Adrian Klaver
> akla...@comcast.net

I missed the part where you wanted to do it as one block with variables already 
substituted.

For that I usually do something like:

sql_str = "ALTER TABLE " + $xn + " OWNER TO xdev;"
sql_str += "GRANT ALL ON TABLE " + $xn + " TO xdev;"
sql_str += "REVOKE ALL ON TABLE " + $xn + " FROM PUBLIC;"
sql_str += "GRANT SELECT ON TABLE " + $xn + " TO PUBLIC;"

cur.execute(sql_str)

On a side note the Psycopg mailing list is:
http://lists.initd.org/mailman/listinfo/psycopg

It is very helpful and makes up for the documentation issues.

-- 
Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Building PG 8.4.1 with ossp-uuid on Centos 5.3

[Christophe Pettus]

On Oct 10, 2009, at 11:53 AM, Tom Lane wrote:

Whose uuid package are you using?


http://www.ossp.org/pkg/lib/uuid/

Building from source.


If it's something you built
from source, it more than likely installed into /usr/local/, and
then your problem is that PG isn't searching /usr/local/lib/ for the
libraries.  The best fix for the latter is to adjust the ldconfig
configuration to include that directory.


It is indeed installing it in /usr/local/lib, but the ldconfig was  
set.  What's irritating is that (looking at config.log) it is finding  
the library just fine, but not finding the uuid_export function inside  
of it.


--
-- Christophe Pettus
   x...@thebuild.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to send multiple SQL commands from Python?

[Adrian Klaver]

On Saturday 10 October 2009 12:09:29 pm Kynn Jones wrote:
> I am porting some code from Perl to Python; in the Perl original I use
> either DBI::do or a rickety home-built module to pass multiple SQL
> statements (as one single block of SQL) to the Pg server.  The typical
> usage is something like this:
> $dbh->do( < ALTER TABLE $xn OWNER TO xdev;
> GRANT ALL ON TABLE $xn TO xdev;
>
> REVOKE ALL ON TABLE $xn FROM PUBLIC;
> GRANT SELECT ON TABLE $xn TO PUBLIC;
> EOSQL
>
>
> How can I do this sort of thing from Python?
>
> I've looked into the docs for psycopg2, but I can't find anything like the
> do command used above.  Did I overlook it?  If not, what can I use instead?
>
> I'm not wedded to psycopg2, in fact its lack of documentation worries me;
> if there's a better alternative that I can use from Python please let me
> know.
>
> TIA!
>
> kynn

One way
Using psycopg2
DSN = "dbname=? user=? port=? host=?"
con = psycopg2.connection(DSN)
cur = con.cursor()
cur.execute(statement1)
cur.execute(statement2)

con.commit()

Another way, not tested, is triple quote entire block above and pass it to 
cur.execute.

-- 
Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Integer range?

[Scott Ribe]

> Neither, really. The cast shortcut you're using is binding to the
> digits more tightly than the minus prefix.

I see, thanks.

-- 
Scott Ribe
scott_r...@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to send multiple SQL commands from Python?

[Kynn Jones]

I am porting some code from Perl to Python; in the Perl original I use
either DBI::do or a rickety home-built module to pass multiple SQL
statements (as one single block of SQL) to the Pg server.  The typical usage
is something like this:
$dbh->do( <

Re: [GENERAL] Building PG 8.4.1 with ossp-uuid on Centos 5.3

[Tom Lane]

Christophe Pettus  writes:
> I'm attempting to build a version of PG 8.4.1 with the OSSP uuid  
> libraries on Centos 5.3 (I'm building PG from source).  I simply  
> cannot seem to find the right libraries to install; even very  
> promising candidates seem to be a different version than the one PG is  
> expecting.

Whose uuid package are you using?  If it's actually been packaged
properly for RHEL/Centos, I would bet that the problem is you forgot
to install the uuid-devel subpackage.  If it's something you built
from source, it more than likely installed into /usr/local/, and
then your problem is that PG isn't searching /usr/local/lib/ for the
libraries.  The best fix for the latter is to adjust the ldconfig
configuration to include that directory.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Prepared statements with a variable number of parameters

[Pavel Stehule]

Hello

2009/10/10 Raymond O'Donnell :
> I'm using PDO in PHP for database access (actually Zend Framework), and
> following best practice by using parameters in prepared statements.
>
> My question is, what do people normally do when there's a variable
> number of parameters? - for example:
>
>  select . where item_id in ( ... );
>

You can use arrays here.

select ... where item_id = ANY(string_to_array($1, ','))

Regards
Pavel Stehule

> In this case, I mightn't know how many values will go inside the
> parentheses.
>
> Thanks,
>
> Ray.
>
> --
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> r...@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> --
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Prepared statements with a variable number of parameters

[Raymond O'Donnell]

I'm using PDO in PHP for database access (actually Zend Framework), and
following best practice by using parameters in prepared statements.

My question is, what do people normally do when there's a variable
number of parameters? - for example:

  select . where item_id in ( ... );

In this case, I mightn't know how many values will go inside the
parentheses.

Thanks,

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Building PG 8.4.1 with ossp-uuid on Centos 5.3

[Christophe Pettus]

Greetings,

I'm attempting to build a version of PG 8.4.1 with the OSSP uuid  
libraries on Centos 5.3 (I'm building PG from source).  I simply  
cannot seem to find the right libraries to install; even very  
promising candidates seem to be a different version than the one PG is  
expecting.  Configuring PG after installing them with --with-ossp-uuid  
returns the error:


	configure: error: library 'ossp-uuid' or 'uuid' is required for OSSP- 
UUID


However, it *is* finding the libraries correctly, but not the symbol  
uuid-export:


conftest.c:(.text+0x7): undefined reference to `uuid_export'

Any thoughts?
--
-- Christophe Pettus
   x...@thebuild.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: transaction ID wraparound - should I use 'VACUUM' or 'VACUUM FULL' ?

[Michal Szymanski]

On 10 Paź, 04:38, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Alvaro Herrera  writes:
> > Michal Szymanski wrote:
> >> In old version of Postgres we have to execute 'VACUUM FULL' to solve
> >> problem of  transaction ID wraparound, do we need to execute 'VACUUM
> >> FULL' in Postgres 8.3 or 8.4 to avoid this problem?
> > No, plain VACUUM suffices.
>
> Just to clarify: plain VACUUM has always sufficed; in *no* version of
> Postgres has it ever been the case that VACUUM FULL was more useful
> than VACUUM to protect against wraparound.
>
>                         regards, tom lane

For me it was clear that plain VACUUM is enough but my friend had
situation when wraparound happend when he used plain vaccum after
'vacuum full' prblem was solved. I will change our vacuum full to
plain vacuum and we will see what happen.

Regards
Michal Szymanski


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Best data type to use for sales tax percent

[Christophe Pettus]

On Oct 10, 2009, at 3:33 AM, Jasen Betts wrote:
CREATE DOMAN sales_tax_rate AS DECIMAL CHECK (VALUE >= 0 AND VALUE  
<=1);


why the latter check ( VALUE <=1 )?


Since this version has no scale on the DECIMAL, the second check keeps  
it from being larger than 1.0, since it's presumably a percentage from  
0% to 99%.


--
-- Christophe Pettus
   x...@thebuild.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Best data type to use for sales tax percent

[Roderick A. Anderson]

Jasen Betts wrote:

On 2009-10-09, Peter Eisentraut  wrote:

On Fri, 2009-10-09 at 11:46 -0700, Christophe Pettus wrote:
Domains are basically type aliases with an optional CHECK clause, so  
you could do something like:


CREATE DOMAN sales_tax_rate AS DECIMAL(5,5) CHECK (VALUE >= 0);

Then, you can use the type "sales_tax_rate" in your tables, etc. just  
as a normal first-class type.  (The only limitation, right now, is  
that you can't create an array of them.)

Actually I wouldn't bother with the precision and scale at all.  I'd go
with something like

CREATE DOMAN sales_tax_rate AS DECIMAL CHECK (VALUE >= 0 AND VALUE <=1);


why the latter check ( VALUE <=1 )?


I think the initial post implied it was to be used directly in the 
calculations, no "sales_tax_rate/100", so a fraction is needed. 
Hopefully no one is experiencing 99%+ tax rates.



\\||/
Rod
--


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] bulk inserts

[Jasen Betts]

On 2009-09-29, Alan Hodgson  wrote:
> On Tuesday 29 September 2009, Sam Mason  wrote:
>> ?? I'm not sure what you're implying about the semantics here, but it
>> doesn't seem right.  COPY doesn't somehow break out of ACID semantics,
>> it's only an *optimization* that allows you to get large quantities of
>> data into the database faster.  The main reason it's faster is because
>> parsing CSV data is easier than parsing SQL.
>>
>> At least I think that's the only difference; anybody know better? 
>
> I think a big reason is also that the client can stream the data without 
> waiting for a network round trip ack on every statement.

a single insert statement can insert many rows.

the win with copy is more that the data part can be parsed one record
at a time. whereas for the insert the whole must be parsed, also the
quoting rules are simpler for CSV or pg's tab-delimted format.




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Best data type to use for sales tax percent

[Jasen Betts]

On 2009-10-09, Peter Eisentraut  wrote:
> On Fri, 2009-10-09 at 11:46 -0700, Christophe Pettus wrote:
>> Domains are basically type aliases with an optional CHECK clause, so  
>> you could do something like:
>> 
>>  CREATE DOMAN sales_tax_rate AS DECIMAL(5,5) CHECK (VALUE >= 0);
>> 
>> Then, you can use the type "sales_tax_rate" in your tables, etc. just  
>> as a normal first-class type.  (The only limitation, right now, is  
>> that you can't create an array of them.)
>
> Actually I wouldn't bother with the precision and scale at all.  I'd go
> with something like
>
> CREATE DOMAN sales_tax_rate AS DECIMAL CHECK (VALUE >= 0 AND VALUE <=1);

why the latter check ( VALUE <=1 )?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Best data type to use for sales tax percent

[Jasen Betts]

On 2009-10-08, Mike Christensen  wrote:
> (Sorry for the super-easy question)
>
> I want to store sales tax (as a percent) in the DB, such as 9.5%.
> What's the best data type for this? 

real, or numeric, probably numeric.

> I'm guessing numeric(2,3) should be fine, yes? 

depends on the range of values you want to store.

generally percentages are most usefully represented as decimal fractions
especially if you intend to do arithmetic with them.

numeric reccomended useful for financial stuff.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] automated row deletion

[Jasen Betts]

On 2009-10-07, Dave Huber  wrote:
> --_000_7CDADB576E07AC4FA71E1B12566C9126540E0A0C34ltimb1LTIcom_
> Content-Type: text/plain; charset="us-ascii"
> Content-Transfer-Encoding: quoted-printable
>
> A colleague gave me the following query to run:
>
> DELETE FROM data_log_20msec_table WHERE (log_id IN (SELECT log_id FROM data=
> _log_20msec_table ORDER BY log_id DESC OFFSET 1000))

looks slower than neccessary.

DELETE FROM data_log_20msec_table WHERE log_id < (SELECT log_id FROM
data= _log_20msec_table ORDER BY log_id DESC OFFSET 1000 LIMIT 1 )



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Access dynamic NEW.column_name in trigger?

[Scott Marlowe]

On Fri, Oct 9, 2009 at 8:23 PM, Nick  wrote:
> This is a very abbr version of my problem, but what would be the best
> way to do this...
>
> DECLARE
>  column_name_var TEXT;
> BEGIN
>  RETURN NEW.column_name_var;
> END;

plpgsql doesn't really do dynamic column names.  You'll need to
implement it in C, perl, or tcl really.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Stuck vacuum...

[Mike Diehl]

On Friday 09 October 2009 08:22:52 pm Tom Lane wrote:
> Mike Diehl  writes:
> > I'm doing a routine vacuum on my database and it seems to be getting
> > "stuck."
> >
> > I've got a "servers" table with only a few rows and columns in it and
> > this is what vacuum has to say:
> >
> > # VACUUM ANALYZE VERBOSE servers;
> > INFO:  vacuuming "public.servers"
> > INFO:  scanned index "servers_id" to remove 19200 row versions
> > DETAIL:  CPU 0.02s/0.01u sec elapsed 0.03 sec.
> > INFO:  scanned index "servers_name" to remove 19200 row versions
> > DETAIL:  CPU 0.03s/0.01u sec elapsed 0.05 sec.
> >
> > The problem is that it's been stalled for several minutes.  It's
> > beginning to sound like a hard drive problem.  Any other ideas?
>
> Maybe it's waiting for some other session to release a page-level lock.
> Have you got anything else running in the database, and if so what is
> that doing?
>
>   regards, tom lane

I restarted Postgres and re-ran the vacuum.  All seems OK now.  Thank you for 
your time.  I guess I should have refrained from jumping to conclusions.

-- 

Take care and have fun,
Mike Diehl.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general