Re: [SQL] two tables - foreign keys referring to each other...
On Tue, Feb 20, 2001 at 11:34:30PM -0800, Stephan Szabo wrote: > > You have to use ALTER TABLE to add the constraint to one of the tables. Maybe I am stating the obvious but you should make sure that you include the ALTER TABLE statements in the *.sql files that you use to create the tables, rather than running them from the psql prompt. Otherwise you'll have trouble to figure out what exactly you did when you come back to the database later; as foreign keys don't show up as 'foreign keys' in schema dumps, but as a set of triggers. Those are quite hard to read, or interpret as what they essentially are, i.e. foreign keys (depending on your philosophical outlook, that is, whether you consider the essence of your foreign keys to be a set of triggers, or vice versa ;-)). Regards, Frank
Re: [SQL] two tables - foreign keys referring to each other...
I think, if it is relationship many-to-many (one admin can be in many institute, and one institute can has many admin, you should use relation table, see below. > -> here we go > BEGIN; -- begin table transaction -- Only Postgresql > CREATE TABLE institute_t ( > nameVARCHAR(48) PRIMARY KEY, > street VARCHAR(48) NOT NULL, > zip VARCHAR(16), > townVARCHAR(32) NOT NULL, > country CHAR(2) NOT NULL, /* country codes ISO-3166*/ > phone VARCHAR(32) NOT NULL, > fax VARCHAR(32), > admin VARCHAR(16) REFERENCES admin_t > ON UPDATE CASCADE > ON DELETE SET NULL > DEFERRABLE > INITIALLY DEFERRED > ); > create table institute_admin ( row int primary key, namevarchar(48) references institute_t, login varchar(16) references admin_t ); > CREATE TABLE admin_t ( > login VARCHAR(16) PRIMARY KEY, > passwordVARCHAR(16) NOT NULL, > email VARCHAR(32) NOT NULL, > real_name VARCHAR(32) NOT NULL, > street VARCHAR(48) NOT NULL, > zip VARCHAR(16), > townVARCHAR(32) NOT NULL, > country CHAR(2) NOT NULL, /* country codes -- refer to > ISO-3166*/ > phone VARCHAR(32) NOT NULL, > fax VARCHAR(32), > access INTEGER NOT NULL, > institute VARCHAR(48) REFERENCES institute_t > ON UPDATE CASCADE > ON DELETE SET NULL > DEFERRABLE > INITIALLY DEFERRED > ); > COMMIT; > If you have diffarant relation, describe it. - Grigoriy G. Vovk
Re: [SQL] pl/Perl
> > 1: can you call other stored procedures from within pl/Perl > No. darn. > > > 2: from within a pl/Perl script , can i do a select etc.. > >i'm assuming no, because you cannot use DBI.. but just wondering > >if there is a way.. > Not currently. darn. > > 3: installing it.. i installed postgres 7.0.3 from ports on my fbsd system. > >when i tried to install pl/perl i get this.. > > > > cd /usr/ports/databases/postgresql7/work/postgresql-7.0.2/src/pl/plperl/ > > perl Makefile.pl > > make > Try using gmake instead of make (cd /usr/ports/devel/gmake, make) thanks ! works great. Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose PGP Public Key : http://bignose.hub.org/public.txt
Re: [SQL] pl/Perl
Jie Liang <[EMAIL PROTECTED]> writes: > My choice: > if involving a lot of regular expressions, pl/Perl is better; > if involving a lot of SQLs or other functions(or store procedures), > then pl/pgsql is better. Also consider pltcl, which has pretty nearly perl-equivalent regexp support, and can do queries too. Besides which it's easier to build/ install than plperl. It's a shame that plperl doesn't yet have support for making queries. It hasn't really progressed much past the proof-of-concept stage IMHO, but no one is working on it :-( regards, tom lane
[SQL] logging a psql script
Hello, I would like my psql script to log everything that it does. I set the following \set ECHO all \o foo.txt \qecho some sql, some ddl, etc... \o But foo.txt only contains DROP DROP DROP CREATE CREATE CREATE I want it to contain everything that I see on the screen, what am I missing? Thanks Ken
Re: [SQL] logging a psql script
On Wed, Feb 21, 2001 at 04:51:00PM -0500, Ken Kline wrote: > Hello, >I would like my psql script to log everything that it does. > I set the following > > \set ECHO all > \o foo.txt > \qecho > > some sql, some ddl, etc... > > \o > > > But foo.txt only contains > > DROP > DROP > DROP > CREATE > CREATE > CREATE > > I want it to contain everything that I see on the screen, what am I > missing? Dunno how you do it via \o; what I do is run the postmaster with the -d 2 option and then log everything to syslogd. This will log every query in detail. It's very convenient while you're developing and testing, especially if you run a separate window with tail -f /wherever/you/write/your/postgres.log Regards, Frank
[SQL] pl/Perl
Hello Few questions about pl/perl as the docs on this are very sparse.. (i find that with our procedural language docs in general) 1: can you call other stored procedures from within pl/Perl 2: from within a pl/Perl script , can i do a select etc.. i'm assuming no, because you cannot use DBI.. but just wondering if there is a way.. 3: installing it.. i installed postgres 7.0.3 from ports on my fbsd system. when i tried to install pl/perl i get this.. cd /usr/ports/databases/postgresql7/work/postgresql-7.0.2/src/pl/plperl/ perl Makefile.pl make "../../../src/Makefile.global", line 135: Need an operator "../../../src/Makefile.global", line 139: Missing dependency operator "../../../src/Makefile.global", line 143: Need an operator "../../../src/Makefile.global", line 144: Missing dependency operator "../../../src/Makefile.global", line 148: Need an operator "../../../src/Makefile.global", line 149: Need an operator "../../../src/Makefile.global", line 150: Need an operator "../../../src/Makefile.port", line 1: Need an operator "../../../src/Makefile.port", line 3: Need an operator "../../../src/Makefile.port", line 6: Need an operator "../../../src/Makefile.port", line 8: Need an operator "../../../src/Makefile.port", line 16: Need an operator "../../../src/Makefile.global", line 246: Missing dependency operator "../../../src/Makefile.global", line 247: Could not find ../../../src/Makefile.custom "../../../src/Makefile.global", line 248: Need an operator "../../../src/Makefile.global", line 253: Missing dependency operator "../../../src/Makefile.global", line 255: Need an operator "../../../src/Makefile.global", line 284: Missing dependency operator "../../../src/Makefile.global", line 286: Need an operator "../../../src/Makefile.global", line 288: Missing dependency operator "../../../src/Makefile.global", line 290: Need an operator "../../../src/Makefile.global", line 292: Missing dependency operator "../../../src/Makefile.global", line 294: Need an operator "../../../src/Makefile.global", line 296: Need an operator "../../../src/Makefile.global", line 299: Need an operator "../../../src/Makefile.global", line 301: Need an operator "../../../src/Makefile.global", line 304: Need an operator make: fatal errors encountered -- cannot continue any tips ? Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose PGP Public Key : http://bignose.hub.org/public.txt
Re: [SQL] now() with microsecond granulity needed
*** Tom Lane <[EMAIL PROTECTED]> [Tuesday, 20.February.2001, 11:57 -0500]: > > using now() to init TIMESTAMP fields I got resolution of one second. How > > can I define DEFAULT in column (TIMESTAMP type) to get higher > > time-resolution (TIMESTAMP supports microseconds). > > You could make a variant of now() that relies on gettimeofday() instead > of time(). Note that you probably won't get microsecond precision in > any case... Do You mean changing sources and recompiling? So there is no way of getting more accurate NOW time directly in SQL ? /DEFAULT clause/ at this moment? I just have now about 5..10 inserts per second, so in fact even ms would help me. -- radoslaw.stachowiak.http://alter.pl/
Re: [SQL] now() with microsecond granulity needed
> "Radoslaw" == Radoslaw Stachowiak <[EMAIL PROTECTED]> writes: Radoslaw> *** Tom Lane <[EMAIL PROTECTED]> [Tuesday, Radoslaw> 20.February.2001, 11:57 -0500]: >> > using now() to init TIMESTAMP fields I got resolution of one >> second. How > can I define DEFAULT in column (TIMESTAMP type) >> to get higher > time-resolution (TIMESTAMP supports >> microseconds). >> >> You could make a variant of now() that relies on gettimeofday() >> instead of time(). Note that you probably won't get >> microsecond precision in any case... Radoslaw> Do You mean changing sources and recompiling? So there Radoslaw> is no way of getting more accurate NOW time directly in Radoslaw> SQL ? /DEFAULT clause/ at this moment? the timeofday function seems to work: acspg=# select timeofday(); timeofday - Wed Feb 21 15:56:43.150389 2001 EST (1 row) acspg=#
Re: [SQL] pl/Perl
On Wed, 21 Feb 2001, Jeff MacDonald wrote: > 1: can you call other stored procedures from within pl/Perl No. > 2: from within a pl/Perl script , can i do a select etc.. >i'm assuming no, because you cannot use DBI.. but just wondering >if there is a way.. Not currently. > 3: installing it.. i installed postgres 7.0.3 from ports on my fbsd system. >when i tried to install pl/perl i get this.. > > cd /usr/ports/databases/postgresql7/work/postgresql-7.0.2/src/pl/plperl/ > perl Makefile.pl > make Try using gmake instead of make (cd /usr/ports/devel/gmake, make) > > "../../../src/Makefile.global", line 135: Need an operator > "../../../src/Makefile.global", line 139: Missing dependency operator > "../../../src/Makefile.global", line 143: Need an operator > "../../../src/Makefile.global", line 144: Missing dependency operator > "../../../src/Makefile.global", line 148: Need an operator > "../../../src/Makefile.global", line 149: Need an operator > "../../../src/Makefile.global", line 150: Need an operator > "../../../src/Makefile.port", line 1: Need an operator > "../../../src/Makefile.port", line 3: Need an operator > "../../../src/Makefile.port", line 6: Need an operator > "../../../src/Makefile.port", line 8: Need an operator > "../../../src/Makefile.port", line 16: Need an operator > "../../../src/Makefile.global", line 246: Missing dependency operator > "../../../src/Makefile.global", line 247: Could not find ../../../src/Makefile.custom > "../../../src/Makefile.global", line 248: Need an operator > "../../../src/Makefile.global", line 253: Missing dependency operator > "../../../src/Makefile.global", line 255: Need an operator > "../../../src/Makefile.global", line 284: Missing dependency operator > "../../../src/Makefile.global", line 286: Need an operator > "../../../src/Makefile.global", line 288: Missing dependency operator > "../../../src/Makefile.global", line 290: Need an operator > "../../../src/Makefile.global", line 292: Missing dependency operator > "../../../src/Makefile.global", line 294: Need an operator > "../../../src/Makefile.global", line 296: Need an operator > "../../../src/Makefile.global", line 299: Need an operator > "../../../src/Makefile.global", line 301: Need an operator > "../../../src/Makefile.global", line 304: Need an operator > make: fatal errors encountered -- cannot continue > > any tips ? > > Jeff MacDonald, > > - > PostgreSQL Inc| Hub.Org Networking Services > [EMAIL PROTECTED]| [EMAIL PROTECTED] > www.pgsql.com | www.hub.org > 1-902-542-0713| 1-902-542-3657 > - > Facsimile : 1 902 542 5386 > IRC Nick : bignose > PGP Public Key : http://bignose.hub.org/public.txt > >
[SQL] two tables - foreign keys referring to each other...]
Chris, > ..or generally: how do you create two crosslinked foreign keyed tables? > > hopefully an easy problem for the real professionals! No. The problem is: Why would you want to create two crosslinked foriegn-keyed tables? As an experienced SQL professional, this seems like a recipe for instant disaster to me. The purpose of a foriegn key is to enforce a parent-->child or data table<--reference list relationships, to prevent incomplete or erroneous records from being added. This requires the foriegn key relationship to be one-way. In fact, if you built your two-way foriegn keys (using ALTER TABLE), I think you might find that you can't add any records to either table. Certainly you won't be able to delete any. I once had an experience with a complex legacy data structure where I accidentally set up a circular foriegn key relationship (among 5 tables), and I had to re-build the database from scripts to fix it. If you're looking to set up a many-to-many relationship, then what you want is a linking table. However, I highly advise you to pick up a primer on database design (such as "Database Design for Mere Mortals") before proceeding any further, or you'll end up spending the next year paying for what you don't understand now. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Re: [SQL] now() with microsecond granulity needed
Daniel Wickstrom <[EMAIL PROTECTED]> writes: > the timeofday function seems to work: Hmm, exactly what I was thinking of, except that it returns a text rather than a timestamp (a strange choice...). It seems a little buggy as well because the formatting of the microseconds part is wrong --- will fix that, and document it. regards, tom lane
Re: [SQL] pl/Perl
FYI, My choice: if involving a lot of regular expressions, pl/Perl is better; if involving a lot of SQLs or other functions(or store procedures), then pl/pgsql is better. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Wed, 21 Feb 2001, Jeff MacDonald wrote: > > > 1: can you call other stored procedures from within pl/Perl > > No. > > darn. > > > > > > 2: from within a pl/Perl script , can i do a select etc.. > > >i'm assuming no, because you cannot use DBI.. but just wondering > > >if there is a way.. > > Not currently. > > darn. > > > > 3: installing it.. i installed postgres 7.0.3 from ports on my fbsd system. > > >when i tried to install pl/perl i get this.. > > > > > > cd /usr/ports/databases/postgresql7/work/postgresql-7.0.2/src/pl/plperl/ > > > perl Makefile.pl > > > make > > Try using gmake instead of make (cd /usr/ports/devel/gmake, make) > > thanks ! works great. > > > Jeff MacDonald, > > - > PostgreSQL Inc| Hub.Org Networking Services > [EMAIL PROTECTED]| [EMAIL PROTECTED] > www.pgsql.com | www.hub.org > 1-902-542-0713| 1-902-542-3657 > - > Facsimile : 1 902 542 5386 > IRC Nick : bignose > PGP Public Key : http://bignose.hub.org/public.txt >
Re: [SQL] logging a psql script
Ken Kline wrote: >Hello, > I would like my psql script to log everything that it does. psql -e -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The LORD bless thee, and keep thee; The LORD make his face shine upon thee, and be gracious unto thee; The LORD lift up his countenance upon thee, and give thee peace." Numbers 6:24-26
