[GENERAL] perl: $sth-{TYPE} ...?
in looking for a way to abstract the generation of formatting
methods based on field type (dates=center formatted; numerics
right-flush; text left-flush, etc) for web pages i've run into a
gap i don't know how to close:
using perl (5.005_03) and DBI (1.13, which refers to
DBD/Pg.pm 0.95), how do i connect $sth-{TYPE} to
$dbh-type_info_all()?
#!/usr/bin/perl
use DBI;
my $dbh = DBI-connect('dbi:Pg:dbname=myDB');
my $ATT = $dbh-type_info_all();
my %ref = reverse %{shift(@$ATT)};
my %typ = map {$ATT-[$_]-[1] = $_} (0 .. $#$ATT); # NOT!
#my %typ = map { ...what?... } @$ATT;
my $sth = $dbh-prepare('select * from myTable');
$sth-execute();
my @typ = @{$sth-{TYPE}};
my @nam = @{$sth-{NAME}};
foreach my $f ( 0..$#nam ) {
my $n = shift @nam;
my $t = shift @typ;
print $n (type #$t) is a , $ATT-[ $typ-{$t} ]-[0], \n;
}
$dbh-disconnect();
__END__
i looked through the postgresql.org faq and non-faq areas and
didn't find what i'm looking for...
i'm using the paranoid-haven debian potato, based on older
gadgets, and would like to stay that way (y'all can have the fun
of bleeding on the edge if you want). what's the incantation for
getting postgresql type info from the fields a table (or view)?
--
Hey, let's change the whole justice system. Everybody gets to
kill one person -- if you do two, you go to jail. That should
cut down on the abrasive personalities, don't you think?
[EMAIL PROTECTED]
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [GENERAL] SERIAL, too low a value
=?iso-8859-1?Q?Daniel_=C5kerud?= [EMAIL PROTECTED] writes: I want to create a sequence with a higher value than an int4. Please explain how to do this, if possible. There's no way in current releases. If not, this is certainly an issue that has to be targeted real soon, or? Sequences are already reimplemented as int8-based for 7.2. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] !! Newbie question!!!! connecting to multiple databases
On Fri, Aug 24, 2001 at 09:53:00PM +, uncleleo wrote:
I am attemping to create multiple databases with Postgresql ver. 7.0.3
running on Mandrake 8.0 rpm. The tool that I am using is Pgadmin ver 7.1.0.
Can someone tell me how I can connect to different databases in a single
select statement? Such as, I have a database named Customer and another
named Products. I wish to Select from table A in the Customer database
and table A in Products database. I know that its possible in SQL Server and
other databases.
If anyone can I help I would appreciate it.
postgresql doesn't allow you to connect to anything but tables
in the 'current' database via sql.
but in an external language you can have several connections
open, each to a different database:
#!perl
use DBI;
my $db1 = DBI-connect('dbi:Pg:dbname=people');
my $db2 = DBI-connect('dbi:Pg:dbname=inventory');
my $st1 = $db1-prepare('select * from client');
$st1-execute();
while ( my $rec = $st1-fetchrow_hashref ) {
my $st2 = $db2-prepare(select $rec-{afield} from $rec-{atable});
$st2-execute();
foreach my $item ( $st2-fetchrow_hashref ) {
...
}
}
but it may be a sign that you need to revisit your data
paradigm, instead... (there are some cases where three levels of
structure are handy: db-table-field -- but usually two does
quite nicely: table-field within db.)
--
Hey, let's change the whole justice system. Everybody gets to
kill one person -- if you do two, you go to jail. That should
cut down on the abrasive personalities, don't you think?
[EMAIL PROTECTED]
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] storing large graphs in postgres
I need to store very large graphs structures in postgres. The graphs are close to 20GB when in flatfile format. I first tried using an adjacency list representation, i.e., graph (source INT8 PRIMARY KEY, dest INT8[]); but operating on the array type seems a bit inflexible. I took a look at the contrib/array stuff as suggested in a previous post, but it seems like that only allows for boolean predicates on the array. I.e., I would like to be able to say 'return all nodes within distance two from x' using purely sql. Of course I could use an edge-list format: graph (source INT8, dest INT8); but this takes up almost double the space (which is painful, given that the original input is close to 20GB). Any way to get richer queries on array types, or some other efficient way to store large graphs? I suppose some python glue, making multiple db calls, would do the trick, but it would be nicer if postgres could take care of it all. Thanks Taher __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] update set from where... with count
Hi. I am having trouble getting the following to work. Please find a
complete cut-paste-run example below between the lines that
demonstrates the problem.
___
CREATE TABLE items (
recdate DATE,
itemTEXT
);
CREATE TABLE summary (
recdate DATE,
itemTEXT,
hitsINTEGER
);
INSERT INTO items VALUES ('1-1-2000', 'widget');
INSERT INTO items VALUES ('1-1-2000', 'widget');
INSERT INTO items VALUES ('1-1-2000', 'widget');
INSERT INTO summary VALUES ('1-1-2000', 'widget', 0);
UPDATE
summary
SET
hits = s.hits + 1
FROM
summary AS s,
items AS i
WHERE
s.recdate = i.recdate AND
s.item= i.item;
SELECT
recdate, item, hits
FROM
summary;
___
It outputs:
recdate | item | hits
++--
2000-01-01 | widget |1
(1 row)
But what I would rather it have shown is:
recdate | item | hits
++--
2000-01-01 | widget |3
(1 row)
...because I am trying to update each widget's number of instances
for each day.
After playing around with this for a long time, trying to use
variations of count, grouping etc, I can't work out how to do this
efficiently.
There are several thousand widgets with several thousand
instances, each day. I have many differently structured widget
tables, the above is just an example. I would rather not add
lengthy trigger statements to each if I can avoid it with a single
neat solution, or a neat rule or something.
Do you have any suggestions for the best way to tackle this
problem? Can it be done with normal SQL or will I have to write a
PLSQL function to do it?
Thanks in advance for any light you can shed.
Jason.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] problem with selects based on dates
Hi everyone I have a problem with SELECTs using dates and WHERE. Some examples: SELECT * FROM emp; empno | ename|job | mgr | hiredate | sal | comm | deptno ---+++--++--+--+ 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 7566 | JONES | MANAGER| 7839 | 1981-04-02 | 2975 |0 | 20 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | | 10 7902 | FORD | ANALYST| 7566 | 1981-12-03 | 3000 | | 20 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | | 30 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | | 20 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | | 30 7788 | SCOTT | ANALYST| 7566 | 1987-04-19 | 3000 | | 20 7782 | CLARK | MANAGER| 7839 | 1981-06-09 | 2450 | | 10 7698 | BLAKE | MANAGER| 7839 | 1981-05-01 | 2850 | | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | | 30 7839 | KING | PRESIDENT | | 1981-11-17 | 5000 | | 10 (14 rows) db2001=# SELECT * FROM emp WHERE hiredate 1981; empno | ename|job | mgr | hiredate | sal | comm | deptno ---+++--++--+--+ 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 7566 | JONES | MANAGER| 7839 | 1981-04-02 | 2975 |0 | 20 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | | 10 7902 | FORD | ANALYST| 7566 | 1981-12-03 | 3000 | | 20 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | | 30 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | | 20 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | | 30 7788 | SCOTT | ANALYST| 7566 | 1987-04-19 | 3000 | | 20 7782 | CLARK | MANAGER| 7839 | 1981-06-09 | 2450 | | 10 7698 | BLAKE | MANAGER| 7839 | 1981-05-01 | 2850 | | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | | 30 7839 | KING | PRESIDENT | | 1981-11-17 | 5000 | | 10 (13 rows) db2001=# SELECT * FROM emp WHERE hiredate 1981-01-01; empno | ename|job | mgr | hiredate | sal | comm | deptno ---+++--++--+--+ 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 7566 | JONES | MANAGER| 7839 | 1981-04-02 | 2975 |0 | 20 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | | 10 7902 | FORD | ANALYST| 7566 | 1981-12-03 | 3000 | | 20 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | | 30 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | | 20 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | | 30 7788 | SCOTT | ANALYST| 7566 | 1987-04-19 | 3000 | | 20 7782 | CLARK | MANAGER| 7839 | 1981-06-09 | 2450 | | 10 7698 | BLAKE | MANAGER| 7839 | 1981-05-01 | 2850 | | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | | 30 7839 | KING | PRESIDENT | | 1981-11-17 | 5000 | | 10 (14 rows) It is not clear to me why in the last query the 1980-12-17 hiredate record is selected as well. Mike Withers University of Western Sydney Australia ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] a simple programming question
I have the following problem; The client of the DB issues a lot of the SQL commands over a network , the statements are of the simple form - select an integer from some table(s) using some criteria. I want to create a function that will accept the string containing all the statements to be executed, a string containing the length of the statements to be executed and will return the string containing all the integer values retrieved. And the questions are: 1. How to execute a sql statement that is contained in the text variable in the function 2. How to get the result of that statement into a variable. Thanks. Jefim Matskin - Senior SW engeneer Sphera Corporation Tel: +972.3.613.2424 Ext:104 mailto:[EMAIL PROTECTED] http://www.sphera.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Primary keys and speed
Hello. I am looking at useing uuid's as primary keys rather than a normal sequence of numbers. The uuids are long text strings like so: 7559e648-a29c-11d5-952f-00c026a18838 The reason for useing them is that it's almost gaurenteed that imported data from another site is going to have a unique identifier. But I was wondering if this will impact on the speed of the database. In the long run the application does not need to be blindingly fast as 99% of the time it is waiting on human interaction. Any ideas? -- Rob Brown-Bayliss ---==o==--- ---(end of broadcast)--- TIP 3: 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
[GENERAL] getting inherited table name
In the pgsql tutorial two tables are created capitals inherits cities. When you do SELECT * FROM cities, you get both capitals and cities. Is there anyway to get get the name of the table so I could possibly know the 'type' it was? Or should this be maintained as a separate column 'city_type' that has a value of 'capital'? Suppose I had another table river_cities and what I would want to be able to is SELECT * FROM cities and know whether the city was a river_city, capital, or nothing at all. thanks, --eric ---(end of broadcast)--- TIP 3: 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
[GENERAL] template0 and template1 databases
I notice that template0 can't be vacuumed; and that
template1 gets vacuumed but has no relations per \d.
What do these databases do?
I have a python script that finds all database names
per 'psql -l', vacuums and analyzes them ('vacuumdb -a
-z'), and then pg_dumps them through gzip into
separate files in a defined backup directory.
Currently, the script excludes template0 and template1
from being dumped and gzipped. Is this correct? Does
this matter?
I would like to offer up this script and one other for
others to use; but am trying to clean them up first.
Thanks,
Andrew
__
Do You Yahoo!?
Get email alerts NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [GENERAL] Bug in createlang?
Bruce Momjian writes: Does anyone have a comment on this? I wrote it a month ago. The fact that the database server is wide-open in the default installation is surely not good, but the problem is that we don't have a universally accepted way to lock it down. We could make password authentication the default, but that would annoy a whole lot of people. Another option would be to set the unix domain socket permissions to 0200 by default, so only the user that's running the server can get in. I could live with that; not sure about others. Richard Huxton [EMAIL PROTECTED] writes: Thomas T. Veldhouse wrote: Why does it ask 4 times? createlang is just a script - it basically runs /path/to/psql $QUERY - each query connects a separate time. Note that running a setup that requires password auth for the DBA will also be a major pain in the rear when running pg_dumpall: one password prompt per database, IIRC. We have other scripts that make more than one database connection, too. This brings up an issue I am concerned about. Right now, when we install the database with initdb, we basically are wide-opened to any local user who wants to connect to the database as superuser. In fact, someone could easily install a function in template1 that bypasses database security so even after you put a password on the superuser and others, they could bypass security. Do people have a good solution for this problem? Should be be installing a password for the super-user at initdb time? I see initdb has this option: --pwprompt -W Makes initdb prompt for a password of the database superuser. If you don't plan on using password authentication, this is not important. Otherwise you won't be able to use password authentication until you have a password set up. Do people know they should be using this initdb option if they don't trust their local users? I see no mention of it in the INSTALL file. I see it does: # set up password if [ $PwPrompt ]; then $ECHO_N Enter new superuser password: $ECHO_C stty -echo /dev/null 21 read FirstPw stty echo /dev/null 21 echo $ECHO_N Enter it again: $ECHO_C stty -echo /dev/null 21 read SecondPw stty echo /dev/null 21 echo if [ $FirstPw != $SecondPw ]; then echo Passwords didn't match. 12 exit_nicely fi echo ALTER USER \$POSTGRES_SUPERUSERNAME\ WITH PASSWORD '$FirstPw' \ | $PGPATH/postgres $PGSQL_OPT template1 /dev/null || exit_nicely if [ ! -f $PGDATA/global/pg_pwd ]; then echo The password file wasn't generated. Please report this problem. 12 exit_nicely fi -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] MySQL's (false?) claims... (was: Re: PL/java?)
Null asks: Would you elaborate on the alternatives that you see to cross-db queries? Are you saying that it would better to duplicate the same data in several databases or put everything in one huge database or what ...? Jan Wieck [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... IMNSVHO the biggest argument against cross-DB queries is this useless extensibility in PostgreSQL. As soon as someone creates a data type, operator, aggregate or function, things get complicated. Have a data type acctno defined in both databases. And yes, we have operators for equalness in both DB's too. Now we do a cross-DB join and have two acctnos in the WHERE clause. First off, all these objects will have different OID's in the two system catalogs. Next, how do you know if the two types will at least have a similar external representation? And which of the two = operator functions will get called? Jan Really the type isn't just acctno but dbname.acctno Operators could work by converting both types to some base type that 'both databases understand'. Cheers, Colin ---(end of broadcast)--- TIP 3: 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] Great Bridge ceases operations
Hello, This is very unfortunate. I wish all of the Great Bridge employees the best. Sincerely, Joshua Drake Command Prompt, Inc. Author: a href=http://stage.linuxports.com/projects/practicalpostgresql/book1.htm Practical PostgreSQL/a [EMAIL PROTECTED] (Bruce Momjian) wrote in message news:[EMAIL PROTECTED]... Effective tomorrow, Great Bridge LLC will cease operations. Full information can be obtained from: http://www.greatbridge.com/ Great Bridge .org, http://greatbridge.org/, will remain in place until projects are migrated to a new site. Tom, Jan, and I have enjoyed working for Great Bridge, and will continue our involvement with PostgreSQL. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Some changes to CVSup and AnonCVS access ...
In order to allow us to split easily across multiple machines, and move things transparently, there following changes are being made: AnonCVS - to access the repository via anon-cvs, please connect to a CVSROOT of: :pserver:[EMAIL PROTECTED]:/projects/cvsroot - passwd for [EMAIL PROTECTED]: blank CVSup - to access the cvsup server, please now connect to: cvsup.postgresql.org both of which will be updated from the master server every 4hrs, and access to the master server will be disabled over the next couple of days ... Any problems, please let me know as soon as possible ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] [ANNOUNCE] Great Bridge ceases operations
Tom, Jan, and I have enjoyed working for Great Bridge, and will continue our involvement with PostgreSQL. So what's going to happen to you guys then? We will cease existing tomorrow at noon. :-) Well, actually, we will stay with PostgreSQL and see what happens in the future. We worked on PostgreSQL before Great Bridge, and we will work after Great Bridge too. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Great Bridge ceases operations
This is a very sad news. What can I say ? Wish you all the best ! And also the best for PostgreSQL projects. - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: PostgreSQL-general [EMAIL PROTECTED] Sent: Friday, September 07, 2001 1:09 AM Subject: [GENERAL] Great Bridge ceases operations Effective tomorrow, Great Bridge LLC will cease operations. Full information can be obtained from: http://www.greatbridge.com/ Great Bridge .org, http://greatbridge.org/, will remain in place until projects are migrated to a new site. Tom, Jan, and I have enjoyed working for Great Bridge, and will continue our involvement with PostgreSQL. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] template0 and template1 databases
No. template0 is new for version 7.1. No doubt a developer can tell you exactly why dropping template0 would be a very bad idea; I am just getting in quickly to make sure you don't do so in the interim :-) - Original Message - From: Andrew Gould [EMAIL PROTECTED] To: J.H.M. Dassen (Ray) [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 06, 2001 7:51 AM Subject: Re: [GENERAL] template0 and template1 databases Thanks for the info and link. So if I'm running PostgreSQL 7.1.3 and have pg_dumped my databases, I can 'dropdb template0'? Andrew --- J.H.M. Dassen (Ray) [EMAIL PROTECTED] wrote: Andrew Gould [EMAIL PROTECTED] wrote: I notice that template0 can't be vacuumed; and that template1 gets vacuumed but has no relations per \d. What do these databases do? template0 is probably a leftover you got from importing a dump from an older version of PostgreSQL. template1 is the master database from which new databases are cloned; see http://www.postgresql.org/idocs/index.php?managing-databases.html#MANAGE-AG- CREATEDB HTH, Ray -- a infinite number of monkeys typing into GNU emacs would never make a good program .../linux/Documentation/CodingStyle ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Bug in createlang?
Bruce Momjian writes: Does anyone have a comment on this? I wrote it a month ago. The fact that the database server is wide-open in the default installation is surely not good, but the problem is that we don't have a universally accepted way to lock it down. We could make password authentication the default, but that would annoy a whole lot of people. Another option would be to set the unix domain socket permissions to 0200 by default, so only the user that's running the server can get in. I could live with that; not sure about others. Whatever you suggest. We basically create a world-writeable socket/database when we do initdb. It is similar to a product installing in a world-writable directory. I realize you can lock it down later, but it seems people need to lock it down _before_ doing initdb or somehow keep it locked down until they set security. Our new SO_PEERCRED/SCM_CREDS gives us a lockdown option on Linux/BSD platforms, but not on the others. If we do the socket permissions thing for initdb, when do we start setting the socket permissions properly? I realize there is no easy answer. I just wanted people to know this is a security hole. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] getting inherited table name
Eric Kolve wrote: In the pgsql tutorial two tables are created capitals inherits cities. When you do SELECT * FROM cities, you get both capitals and cities. Is there anyway to get get the name of the table so I could possibly know the 'type' it was? Or should this be maintained as a separate column 'city_type' that has a value of 'capital'? Suppose I had another table river_cities and what I would want to be able to is SELECT * FROM cities and know whether the city was a river_city, capital, or nothing at all. No additional fields are required: SELECT c.*, c.tableoid, pgc.relname as city_type FROM cities c, pg_class pgc WHERE c.tableoid = pgc.oid regards Nico ---(end of broadcast)--- TIP 3: 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
[GENERAL] Great Bridge ceases operations
Effective tomorrow, Great Bridge LLC will cease operations. Full information can be obtained from: http://www.greatbridge.com/ Great Bridge .org, http://greatbridge.org/, will remain in place until projects are migrated to a new site. Tom, Jan, and I have enjoyed working for Great Bridge, and will continue our involvement with PostgreSQL. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] embedded SQL cursos declare fails
I have committed these changes to ecpg.sgml. You can see the result in the development docs tomorrow. OK, I have backed out that patch. Would you review that section of the SGML and either commit a patch or send me one. Thanks. On Fri, Aug 24, 2001 at 02:07:48PM -0400, Bruce Momjian wrote: OK, I have applied the following patch. Interestingly, the SGML code marked this block as outdated, but didn't remove it for some reason. I'm afraid this patch is incorrect. - !--WARNING: FROM HERE ON THE TEXT IS OUTDATED!-- Don't know why this is said. - varlistentry -termOpen cursor statement/term -listitem - para - An open cursor statement looks like: - programlisting - exec sql open replaceablecursor/replaceable; - /programlisting - and is ignore and not copied from the output. - /para This is partly correct. The OPEN statement is not copied. Instead the DECLARE statement is issued at the place of the OPEN statement since it opens the cursor too. - A commit statement looks like - programlisting - exec sql commit; Thsi certainly exists. - and is translated on the output to - programlisting - ECPGcommit(__LINE__); But the translation is not correct anymore. - exec sql rollback; - /programlisting - and is translated on the output to - programlisting - ECPGrollback(__LINE__); Same here. These functions have been combined to ECPGtrans(int lineno, const char *connection_name, const char *transaction) Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] template0 and template1 databases
Thanks! Andrew --- Jeff Eckermann [EMAIL PROTECTED] wrote: No. template0 is new for version 7.1. No doubt a developer can tell you exactly why dropping template0 would be a very bad idea; I am just getting in quickly to make sure you don't do so in the interim :-) - Original Message - From: Andrew Gould [EMAIL PROTECTED] To: J.H.M. Dassen (Ray) [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 06, 2001 7:51 AM Subject: Re: [GENERAL] template0 and template1 databases Thanks for the info and link. So if I'm running PostgreSQL 7.1.3 and have pg_dumped my databases, I can 'dropdb template0'? Andrew --- J.H.M. Dassen (Ray) [EMAIL PROTECTED] wrote: Andrew Gould [EMAIL PROTECTED] wrote: I notice that template0 can't be vacuumed; and that template1 gets vacuumed but has no relations per \d. What do these databases do? template0 is probably a leftover you got from importing a dump from an older version of PostgreSQL. template1 is the master database from which new databases are cloned; see http://www.postgresql.org/idocs/index.php?managing-databases.html#MANAGE-AG- CREATEDB HTH, Ray -- a infinite number of monkeys typing into GNU emacs would never make a good program .../linux/Documentation/CodingStyle ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] SQL Server to PostgreSQL HOWTO
Someone asked me to do this, and I have a draft now. While it is clearly lacking in some areas, it might be of help generally. Ian Ian A. Harding Programmer/Analyst II Tacoma-Pierce County Health Department (253) 798-3549 mailto: [EMAIL PROTECTED] The Microsoft SQL Server to PostgreSQL Migration HOWTO Ian A. Harding [EMAIL PROTECTED] v1.00, 23 July 2000 How to move a database from a popular proprietary database to the world's most powerful open source database. __ Table of Contents 1. Disclaimer 2. Introduction 3. Considerations 4. Tables 5. Data 6. Views 7. Summary __ 1.Disclaimer The following document is offered in good faith as comprising only safe programming and procedures. No responsibility is accepted by the author for any loss or damage caused in any way to any person or equipment, as a direct or indirect consequence of following these instructions. 2.Introduction The most recent version of this document can always be found at http://www.tpchd.org/.html Microsoft SQL Server is very popular relational database management systems (RDBMS) with highly restrictive licensing and high cost of ownership if the database is of significant size, or is used by a significant number of clients. It does, however, provide a very user-friendly interface, is easy to learn and use, and has low cost entry level configurations. This has resulted in a very large installed user base. PostgreSQL now challenges MS SQL Server in basic feature set, reliability and performance, has a much less restrictive license, and is open source. As a matter of course, users are migrating to PostgreSQL from MS SQL Server as the cost of ownership becomes an issue, and as their knowledge of relational database systems increases. This HOW-TO is intended for the MS SQL Server user who is now ready to migrate databases to PostgreSQL. 3.Considerations RDBMS features are implemented differently and to different degrees by programmers. Some applications rely heavily on so-called middleware, or on the client application to handle business logic. Others attempt to put as much logic as possible in the database. Your migration will be far more difficult if your application is in the latter group. While it is a sound design choice to put logic in the database server, it will require programming in a vendor specific Structured Query Language (SQL) extension such as Microsoft's Transact SQL (T-SQL). This is also the case with PostgreSQL. There is no easy way to migrate stored procedures, triggers, or rules. On the bright side, PostgreSQL provides several language options, all of which are more graceful than T-SQL. RDBMS all provide built-in functions. However, like procedural extensions to SQL, they are not portable. Fortunately, there is some overlap, and the simple syntax makes migration relatively easy. Finally, the programmer's choice of SQL syntax can affect this process. Most RDBMS are approaching the evolving SQL standards. That is, they are leaning away from vendor specific syntax such as the '*=' syntax for a left outer join. This syntax is still supported in MS SQL Server as of version 7.0, but was never supported in PostgreSQL. This process will require either a mind-numbing amount of hand editing of script and data files, or use of a scripting language to programmatically modify these files, followed by a somewhat less enormous amount of editing. I am not smart enough to identify every possible option for the migration, or to accomodate them in a script. I have done this migration on a relatively complex database application in a reasonable amount of time. This, rather than a technically flawless script, should be your goal. I use Tool Command Language (TCL) for almost everything, so I use it here.You can use whatever language you like. 4. Tables Dump the table defininitions with the MS SQL Server scripting tool. From the Enterprise Manager, right click on your database and select 'All Tasks', then 'Generate SQL Scripts' from the context menu. Uncheck 'Script All Objects', and select 'All Tables'. On the 'Formatting' tab, de-select 'Generate DROP...'. On the 'Options' tab, select 'Script indexes' and Script PRIMARY KEYS...'. Select the 'MS-DOS' file format, and make sure 'Create one file' is checked. Click OK, give it a name, and put it somewhere you can find it. A brief look at this file will show you what we are up against. MS uses square brackets around all identifiers, to protect you from poor design choices such as using reserved keywords so crazy things like: CREATE TABLE [dbo].[Select] ([Union] [int]) are possible. PostgreSQL uses double quotes instead. MS uses the object owner qualification for all objects, 'dbo' in
