Re: [SQL] Determining if two subnets intersect
Tom Lane <[EMAIL PROTECTED]> writes: > Florian Weimer <[EMAIL PROTECTED]> writes: > > Is there some efficient PostgreSQL expression which is true if and > > only if two subnets (given as values of type cidr) have non-empty > > intersection (even if the intersection is not a CIDR network)? > > Maybe I'm missing something, but ISTM it's only possible for two > CIDR subnets to overlap if one contains the other. So you could > check with > > A <<= B OR B <<= A Oh, I think you are right; I haven't paid attention. Thanks. -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://cert.uni-stuttgart.de/ RUS-CERT +49-711-685-5973/fax +49-711-685-5898 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: how can we get total records in pg server?
Hello, Here's a quick function to count the total number of rows in none system tables: DROP FUNCTION cnt_rows(); CREATE FUNCTION cnt_rows() RETURNS int4 AS ' DECLARE r RECORD; rb RECORD; tot int4; sqlstr text; BEGIN tot:=0; FOR r IN select * from pg_tables where not tablename like ''pg_%'' LOOP sqlstr:=''SELECT count(*) AS cnt FROM '' || quote_ident(r.tablename) || '';''; FOR rb IN EXECUTE sqlstr LOOP RAISE NOTICE ''Number of rows in %=%'',r.tablename,rb.cnt; tot:=tot+rb.cnt; END LOOP; END LOOP; RAISE NOTICE ''Total Number of rows for all none system tables=%'',tot; RETURN tot; END; ' LANGUAGE 'plpgsql'; You'll also need to have the plpgsql language created for your database (eg: createlang plpgsql testdb) then just execute the above script then select cnt_rows(); in psql. -Stuart ORIGINAL MESSAGE: hi all, consider the pg server with databases bhuvan uday guru the need is to get TOTAL RECORDS of all the THREE DATABASES or atleast TOTAL RECORDS of ONE DATABASE. Seems to be simple. Thankx in advance and infact i was a newbie. Regards, Bhuvaneswar. "There is nothing new under the sun, but there are lots of old things we don't know yet." -Ambrose Bierce On Mon, 23 Jul 2001, omid omoomi wrote: > you mean this ? > select count(*) from tablefoo; > > > >From: Bhuvan A <[EMAIL PROTECTED]> > >To: [EMAIL PROTECTED] > >Subject: [SQL] how can we get total records in pg server? > >Date: Mon, 23 Jul 2001 20:03:42 +0530 (IST) > > > > > >Hi all, > > > >how can we get the COUNT of total records in the db server? > > > >hope this could be simple for pg experts. > >thankx in advance! > > > >Regards, > >Bhuvaneswar. > >=== = > > Eighty percent of married men cheat in America. The rest cheat in > >Europe. > > -- Jackie Mason > >=== = > > > > > >---(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 > > > _ > Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Meta integrity
I'm working on a project based on an unusual data model. Some entities
aren't represented by separate tables, they're grouped in the same table
just like the following simplified model shows:
CREATE TABLE class (
id CHAR(8) NOT NULL,
name VARCHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO class VALUES ('X', 'Class x') ;
INSERT INTO class VALUES ('Y', 'Class y') ;
CREATE TABLE object (
id INTEGER NOT NULL,
class_id CHAR(8) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (class_id) REFERENCES class (id)
);
INSERT INTO object VALUES (1, 'X') ;
INSERT INTO object VALUES (2, 'Y') ;
INSERT INTO object VALUES (3, 'X') ;
Now suppose we need to store in a separate table attributes from objects
from the specific class 'X'. Defining this table with...
CREATE TABLE specific_attribute (
idINTEGER NOT NULL,
value TEXTNOT NULL,
object_id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (object_id) REFERENCES object (id)
);
...will only guarantee that each attribute points to an existent object
but it will not care about the object's class. Question is: how could I
also enforce this kind of "meta integrity"? The following table
definition came to my mind, but its an illegal construction:
CREATE TABLE specific_attribute (
idINTEGER NOT NULL,
value TEXTNOT NULL,
object_id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (object_id, 'X') REFERENCES object (id, class_id)
);
Thanks in advance,
--
Renato
Sao Paulo - SP - Brasil
[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: [SQL] Re: Inserts in triggers Follow Up
Morgan, > One other problem I am having in that proc is SELECT'ing INTO a var. > > declare > id lookup_sports.sport_id%TYPE; > begin > SELECT INTO id sport_id FROM lookup_sports WHEREsome > clause > > id is always null That's because the SELECT INTO variable and SELECT INTO record syntax are confusingly different. It's for records: SELECT INTO record_var * FROM ... for simple variables: SELECT column INTO variable FROM ... I'm not clear on the origin of the inconsistency; my guess is that jan copied it over from PL/SQL. > good suggestion to differentiate my vars from my columns. For the > sake of > expediency I named them the same so when I was writing the insert > statements I could just copy/past my columns list into my values > list. You should *always* do this. Most functions with identical column and variable names will confuse the compiler and result in errors. It'd have been nice if PL/pgSQL supported variable naming with a special character preifx (e.g. $variable). Does anyone know of a character that won't give the parser fits? Currently I'm using "v_", same as Richard. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Meta integrity
Renato, > ...will only guarantee that each attribute points to an existent > object > but it will not care about the object's class. Question is: how could > I > also enforce this kind of "meta integrity"? The following table > definition came to my mind, but its an illegal construction: > > CREATE TABLE specific_attribute ( >idINTEGER NOT NULL, >value TEXTNOT NULL, >object_id INTEGER NOT NULL, >PRIMARY KEY (id), >FOREIGN KEY (object_id, 'X') REFERENCES object (id, class_id) > ); This is a fairly common problem that has no solution using REFERENCES, either in Postgres or in SQL 99. You basically have two choices: 1. The rigorous -- write your own Triggers and Constraints to enforce this kind of integrity, including INSERT, UPDATE, and DELETE triggers on the various tables. Between postgresql.org and Roberto Mello's sight, there's quite a bit of material on triggers. 2. The simple -- write functions to perform inserts, updates and deletes on these tables. Put the relation into those functions, and make users use those functions instead of direct SQL command access. I took the second approach to solve a similar problem, because I had quite a number of other business rules I needed to apply, and adding the special relationship rule was only one more. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Re: Inserts in triggers Follow Up
Tom, > No, this isn't right. If you check the source code you will discover > that plpgsql is extremely lax about the positioning of the INTO > clause, > and will in fact accept it almost anywhere. Datatype has nothing to > do with this. (It probably should be stricter, but at this point I > doubt we could change it without drawing howls of anguish from those > who did it the other way.) Thanks for setting me straight, Tom. I guess I combines some bugs in 7.0.2 with the original sketchy instructions and assumed that there were stricter syntactical rules than there are. This is good because it now means I can use the logical syntax of SELECT data INTO var ... which to me is easier to read, for everything. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: Inserts in triggers Follow Up
"Josh Berkus" <[EMAIL PROTECTED]> writes: > That's because the SELECT INTO variable and SELECT INTO record syntax > are confusingly different. It's > for records: > SELECT INTO record_var * FROM ... > for simple variables: > SELECT column INTO variable FROM ... > I'm not clear on the origin of the inconsistency; my guess is that jan > copied it over from PL/SQL. No, this isn't right. If you check the source code you will discover that plpgsql is extremely lax about the positioning of the INTO clause, and will in fact accept it almost anywhere. Datatype has nothing to do with this. (It probably should be stricter, but at this point I doubt we could change it without drawing howls of anguish from those who did it the other way.) I'm not sure about Morgan's problem, but I suspect it isn't in the part of the query that he showed us. An unexpected substitution in the WHERE clause seems like a plausible theory. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Meta integrity
On Wed, 25 Jul 2001, Renato De Giovanni wrote:
> I'm working on a project based on an unusual data model. Some entities
> aren't represented by separate tables, they're grouped in the same table
> just like the following simplified model shows:
>
> CREATE TABLE class (
>id CHAR(8) NOT NULL,
>name VARCHAR(30) NOT NULL,
>PRIMARY KEY (id)
> );
>
> INSERT INTO class VALUES ('X', 'Class x') ;
> INSERT INTO class VALUES ('Y', 'Class y') ;
>
> CREATE TABLE object (
>id INTEGER NOT NULL,
>class_id CHAR(8) NOT NULL,
>PRIMARY KEY (id),
>FOREIGN KEY (class_id) REFERENCES class (id)
> );
>
> INSERT INTO object VALUES (1, 'X') ;
> INSERT INTO object VALUES (2, 'Y') ;
> INSERT INTO object VALUES (3, 'X') ;
>
> Now suppose we need to store in a separate table attributes from objects
> from the specific class 'X'. Defining this table with...
>
> CREATE TABLE specific_attribute (
>idINTEGER NOT NULL,
>value TEXTNOT NULL,
>object_id INTEGER NOT NULL,
>PRIMARY KEY (id),
>FOREIGN KEY (object_id) REFERENCES object (id)
> );
>
> ...will only guarantee that each attribute points to an existent object
> but it will not care about the object's class. Question is: how could I
> also enforce this kind of "meta integrity"? The following table
> definition came to my mind, but its an illegal construction:
>
> CREATE TABLE specific_attribute (
>idINTEGER NOT NULL,
>value TEXTNOT NULL,
>object_id INTEGER NOT NULL,
>PRIMARY KEY (id),
>FOREIGN KEY (object_id, 'X') REFERENCES object (id, class_id)
> );
Well, if you don't mind the extra space (and a bit of cheating), this
might work (untested):
add an attribute to specific_attribute class_id default 'X' and a check
constraint to prevent it from ever being something else and a unique
constraint on (id,class_id) to object (meaningless since id is already
unique, but necessary for following the letter of the spec), and then do a
foreign key (object_id, class_id) references object(id, class_id) in
specific_attribute.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Meta integrity
Yes, its not a task for _relation_ dbms. I am database developer, I like rdbms, but now I think that I should start to use LDAP for these kind of tasks. What people can say? Jul 25, 08:22 -0700, Josh Berkus wrote: > Renato, > > > ...will only guarantee that each attribute points to an existent > > object > > but it will not care about the object's class. Question is: how could > > I > > also enforce this kind of "meta integrity"? The following table > > definition came to my mind, but its an illegal construction: > > > > CREATE TABLE specific_attribute ( > >idINTEGER NOT NULL, > >value TEXTNOT NULL, > >object_id INTEGER NOT NULL, > >PRIMARY KEY (id), > >FOREIGN KEY (object_id, 'X') REFERENCES object (id, class_id) > > ); > > This is a fairly common problem that has no solution using REFERENCES, > either in Postgres or in SQL 99. You basically have two choices: > > 1. The rigorous -- write your own Triggers and Constraints to enforce > this kind of integrity, including INSERT, UPDATE, and DELETE triggers on > the various tables. Between postgresql.org and Roberto Mello's sight, > there's quite a bit of material on triggers. > > 2. The simple -- write functions to perform inserts, updates and deletes > on these tables. Put the relation into those functions, and make users > use those functions instead of direct SQL command access. > > I took the second approach to solve a similar problem, because I had > quite a number of other business rules I needed to apply, and adding the > special relationship rule was only one more. > > -Josh > > > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > my best regards, Grigoriy G. Vovk ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] about cursor
Hello, I am using posgresql 7.0 with apache 1.3.19, and redhat 7.1 I am writing SQL queries embedded in PHP language embedded in HTML, I would like to know if it is possible to declare a cursor for select, and to include an update in the queries contained in the Cursor, if not what are the possible alternatives... Thanks, Frederic. ___ Do You Yahoo!? -- Vos albums photos en ligne, Yahoo! Photos : http://fr.photos.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
