[SQL] Querying for name/value pairs in reverse
I'm no SQL expert by any means so I'm wondering if something like this is possible. I have two tables like this: create table Errors ( Id serial not null, CreateDate timestamp not null, primary key (Id) ); create table ErrorValues ( Id serial not null, ErrorId int not null, Name varchar(255) not null, Value text not null, primary key (Id), foreign key (ErrorId) references Errors (Id) ); Now, selecting specific ErrorValues with a bunch of names that are related to an Error is of course pretty simple. But I want to go the other way. I want to query for: 'give me all Errors that have the Code=1234 AND Address=1.2.3.4 AND Type=OSX Name/Value pairs' What is a good recipe to deal with this? Is something like this possible with standard sql? Is there a nice PG way to do this? Are there good books that cover real world stuff like this? So many questions from a SQL noob. S. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Doubt about User-defined function.
I have doubt about user-defined function returns value : * why function returns( accepts ) One attribute in the arguments of function/table, Otherwise it returns the whole record of the table. Function accept more than arguments . * Why it doesn`t return more than one arguments in the function (or) Attribute of that table. * Its like same as C-language function, Because in C function returns one value (or) structure/array of characters. * My question is, In psql user-defined function doesn`t returns more than one attribute. Is it possible (or) Not. Some examples : i) create function com2(text,integer) returns text as ' select city.city from city,weather where weather.city=$1 AND city.pop>$2' language sql; Res : Return type of the function is Text, As well as it is one of the argument of function. ( At the type of returning, why it doesn`t also accepts integer ). ii) create function usrs_tab(text,integer) returns city as 'select city.city,city.pop,city.state from city,weather where weather.city=$1 AND city.pop>$2' language sql; Res : Return one record from City table ( table contains city, pop, state only ). Thanking you.-- sathiyamoorthy
Re: [SQL] Querying for name/value pairs in reverse
This is definitely doable. one "Set" way that I could think of doing this would be to first compile a temp table with all of the Value/Pairs that your looking to search for and then just JOIN the ID's (in this case it would be the Value and Pair) to the ErrorValues table. This should give you all of the ErrorID's in the ErrorValues table which would then allow you to JOIN up against the Errors table to get information like CreateDate. Another way, would be to use XML instead of Value/Pair to turn it into a Node and Value type of thing. You could then use XQuery to search inside of the XML attribute for what you were looking for. SET theory would be a better alternative but this is just a different idea. One Naming convention tip, I like to name my PrimaryKey's something more descriptive than just ID. You'll notice that your ErrorValues table had to include the foreign key called ErrorID that actually relates to attribute ID in the Errors table. When your looking at miles and miles of code or reviewing JOIN syntax " a.ID = b.ErrorID" sometimes it's easier to validate if it looks like this. "a.ErroID = b.ErrorID". Just my 2 cents... Hope that helps. -Paul On 7/15/06, Stefan Arentz <[EMAIL PROTECTED]> wrote: I'm no SQL expert by any means so I'm wondering if something like thisis possible.I have two tables like this: create table Errors (Id serial not null,CreateDate timestamp not null,primary key (Id));create table ErrorValues (Id serial not null,ErrorId int not null,Name varchar(255) not null, Value text not null,primary key (Id),foreign key (ErrorId) references Errors (Id));Now, selecting specific ErrorValues with a bunch of names that arerelated to an Error is of course pretty simple. But I want to go the other way. I want to query for:'give me all Errors that have the Code=1234 AND Address=1.2.3.4 ANDType=OSX Name/Value pairs'What is a good recipe to deal with this? Is something like this possible with standard sql? Is there a nice PG way to do this?Are there good books that cover real world stuff like this?So many questions from a SQL noob.S.---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Regular Expression in SQL
I recall not long ago a discussion about regular expressions in a query that hit on this exact topic but don't think it was ever resolved so I am giving it a go again...Here is my query (keep in mind that I am just experimenting now so don't worry about the fact that I am using nested substring function calls): SELECT referrer, substring(referrer FROM '^([^\\/]*\\/\\/[^\\/]*)(\\/)?'), substring(referrer FROM '^[^\\/]*\\/\\/www\\.google\\.[^\\/]*\\/[^\\?]*\\?(.*)$'), substring(substring(referrer FROM '^[^\\/]*\\/\\/www\\.google\\.[^\\/]*\\/[^\\?]*\\?(.*)$') FROM '((%&q=)|(q=))#"[^&]*#"((&%)|())' FOR '#') FROM one_hour_air.web_page_viewWHERE referrer ~ '^[^\\/]*\\/\\/(www.google\\.[^\\/]*)\\/'What I get is:referrer substringsubstring_1substring_2 http://www.google.ca/search?q=one+hour+heating&hl=enhttp://www.google.caq=one+hour+heating&hl=enq= http://www.google.com/search?hl=en&q=One+hour+heating+and+Airhttp://www.google.comhl=en&q=One+hour+heating+and+Airhl=en&q=What I expected for substring_2 was (respectively): one+hour+heatingOne+hour+heating+and+AirI thought by using the FOR '#' I could specify exactly what part of the _expression_ I would get but it still grabs the first (...) of the pattern. At least that is what the documentation in seciton 9.7.2 at http://www.postgresql.org/docs/8.1/static/functions-matching.html led me to believe. How can I get the part of the string I am really after without using one nested substring after another? Thanks,Aaron Bono== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ==
Re: [SQL] Doubt about User-defined function.
I am really confused about what your question is. Functions can take zero to many arguments and return one value. The argument types are different from (or at least are independent of) the return value. Arguments are not returned, they are passed into the function. Your use of the terminology appears to be inconsistent with the definitions of these words. -AaronOn 7/15/06, sathiya moorthy <[EMAIL PROTECTED]> wrote: I have doubt about user-defined function returns value : * why function returns( accepts ) One attribute in the arguments of function/table, Otherwise it returns the whole record of the table. Function accept more than arguments . * Why it doesn`t return more than one arguments in the function (or) Attribute of that table. * Its like same as C-language function, Because in C function returns one value (or) structure/array of characters. * My question is, In psql user-defined function doesn`t returns more than one attribute. Is it possible (or) Not. Some examples : i) create function com2(text,integer) returns text as ' select city.city from city,weather where weather.city=$1 AND city.pop>$2' language sql; Res : Return type of the function is Text, As well as it is one of the argument of function. ( At the type of returning, why it doesn`t also accepts integer ). ii) create function usrs_tab(text,integer) returns city as 'select city.city,city.pop,city.state from city,weather where weather.city=$1 AND city.pop>$2' language sql; Res : Return one record from City table ( table contains city, pop, state only ). == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ==
Re: [SQL] Querying for name/value pairs in reverse
On 7/15/06, Stefan Arentz <[EMAIL PROTECTED]> wrote: I'm no SQL expert by any means so I'm wondering if something like thisis possible.I have two tables like this:create table Errors ( Id serial not null, CreateDate timestamp not null, primary key (Id) );create table ErrorValues ( Id serial not null, ErrorId int not null, Name varchar(255) not null, Value text not null, primary key (Id), foreign key (ErrorId) references Errors (Id) );Now, selecting specific ErrorValues with a bunch of names that arerelated to an Error is of course pretty simple. But I want to go theother way. I want to query for: 'give me all Errors that have the Code=1234 AND Address= 1.2.3.4 ANDType=OSX Name/Value pairs'What is a good recipe to deal with this? Is something like thispossible with standard sql? Is there a nice PG way to do this? Try thisSELECT Errors.ID, Errors.CreateDateFROM ErrorsWHERE Errors.ID IN ( SELECT ErrorValues.id, FROM ErrorValues WHERE (ErrorValues.name = 'Code' AND ErrorValues.value = '1234') INTERSECT SELECT ErrorValues.id, FROM ErrorValues WHERE (ErrorValues.name = 'Address' AND ErrorValues.value = '1.2.3.4') INTERSECT SELECT ErrorValues.id, FROM ErrorValues WHERE (ErrorValues.name = 'Type' AND ErrorValues.value = 'OSX Name/Value pairs') );== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] Querying for name/value pairs in reverse
On 7/15/06, Paul S <[EMAIL PROTECTED]> wrote: This is definitely doable. one "Set" way that I could think of doing this would be to first compile a temp table with all of the Value/Pairs that your looking to search for and then just JOIN the ID's (in this case it would be the Value and Pair) to the ErrorValues table. This should give you all of the ErrorID's in the ErrorValues table which would then allow you to JOIN up against the Errors table to get information like CreateDate. This works - the subselect I sent earlier kind of does this (the subselect can act as a temp table in memory so you don't have to create a physical one). Another way, would be to use XML instead of Value/Pair to turn it into a Node and Value type of thing. You could then use XQuery to search inside of the XML attribute for what you were looking for. SET theory would be a better alternative but this is just a different idea. I wonder if the use of XML in a database is a very good idea. (am I treading on religious territory here?) I can think of some examples where XML can be useful but the problem I see with it is that your data structure is embedded in a single field and your database schema does not describe your data structure very well anymore. I always like to use the database schema as a way to document the data structure so if you have the DB diagrams, you can understand everything there. One Naming convention tip, I like to name my PrimaryKey's something more descriptive than just ID. You'll notice that your ErrorValues table had to include the foreign key called ErrorID that actually relates to attribute ID in the Errors table. When your looking at miles and miles of code or reviewing JOIN syntax " a.ID = b.ErrorID" sometimes it's easier to validate if it looks like this. "a.ErroID = b.ErrorID ". Just my 2 cents...I wholeheartedly agree. If you don't adopt a good naming convention like this, you will come to regret it as your application grows in size and complexity. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] Regular Expression in SQL
"Aaron Bono" <[EMAIL PROTECTED]> writes: > I thought by using the FOR '#' I could specify exactly what part of the > expression I would get but it still grabs the first (...) of the pattern. Hmm ... I think that this is a bug in similar_escape(): it ought to transform parentheses in a SIMILAR pattern into non-capturing parentheses. Until this is fixed, your best bet is to use the POSIX-regexp form of substring(). You can't sneak non-capturing parens through similar_escape, because it'll try to escape the ? ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
