Re: [SQL] SQL a simple menu - plz help
>> Here, this link should help: >> >> Search PostgreSQL - Opera >> http://www.postgresql.org/search.cgi?q=connectby > > Thanks...rtfm I know :) RTFM? Oh, yeah, that's right -- Read The /FUNNY/ Manual! =D -- Randolf Richardson - [EMAIL PROTECTED] Vancouver, British Columbia, Canada Please do not eMail me directly when responding to my postings in the newsgroups. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] not in vs not exists - vastly diferent performance
"[EMAIL PROTECTED] (Tom Lane)" wrote in comp.databases.postgresql.sql: > "Iain" <[EMAIL PROTECTED]> writes: > >> I found this interesting and thought I'd offer it up for comment. > > You didn't say what PG version you are using, but I'd venture to bet > it is pre-7.4. This must've been one of the enhancements. =) -- Sir Randolf, noble spam fighter - [EMAIL PROTECTED] Vancouver, British Columbia, Canada Please do not eMail me directly when responding to my postings in the newsgroups. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Radius of a zip code
"[EMAIL PROTECTED] (Tom Lane)" wrote in comp.databases.postgresql.sql: [sNip] > I'm guessing that the big problem is that you didn't measure longitude > and latitude in identical units in your table, so your "circle" isn't > real circular, and the smaller problem is that "miles" converts to > "degrees of arc" differently at different latitudes. Don't forget that there are two different types of "miles" which need to be considered when measuring distances: 1 statute/land mile = 1.609 km 1 nautical/sea mile = 1.85 km Since kilometers are consistent over land and water (and in the great vacuum of space), the metric system should always be used to ensure clarity, unless the only land masses the user is concerned with have no bodies of water. =) -- Sir Randolf, noble spam fighter - [EMAIL PROTECTED] Vancouver, British Columbia, Canada Please do not eMail me directly when responding to my postings in the newsgroups. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Array fields in Postgresql...
> Can any one give me a link/tutorial for using arrays in databases..I
> need Queries to access the arrays as well...
A quick search in Google.Com yielded a number of results which included
the following:
PostgreSQL Tutorial
http://www.eskimo.com/support/PostgreSQL/tutorial/
("Arrays" are listed near the end of the page)
In Google.Com, you can also use the following search string:
+postgresql +arrays +tutorial
--
Randolf Richardson - [EMAIL PROTECTED]
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/
This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SOLVED: Emulating 'connect by prior' using stored proc
[sNip] > In order to make this work with postgres an additional table is needed > that can hold the level (depth) of the branch because pgsql doesn't like > returning a tuple that isn't based on a defined structure. Once you've > created this table you can pretty much forget about it. > > CREATE TABLE "node_relationships_n_level" ( > "level" integer > ) inherits (node_relationships); > > > Now create your stored procedure. > > CREATE OR REPLACE FUNCTION "crawl_tree" (integer,integer) RETURNS SETOF > node_relationships_n_level AS 'DECLARE > temp RECORD; > child RECORD; > BEGIN > SELECT INTO temp *, $2 AS level FROM node_relationships WHERE > child_node_id = $1; > > IF FOUND THEN > RETURN NEXT temp; > FOR child IN SELECT child_node_id FROM node_relationships WHERE > parent_node_id = $1 ORDER BY ordinal LOOP > FOR temp IN SELECT * FROM crawl_tree(child.child_node_id, $2 + > 1) LOOP > RETURN NEXT temp; > END LOOP; > END LOOP; >END IF; > RETURN NULL; > END; > ' LANGUAGE 'plpgsql'; > > > The second parameter must be zero. This is a kludge because this is a > recursive function and I needed some way of passing the level to > successive function calls. However, if you like, you could consider this > to be a "level offset"--set it to '2' and all the levels returned will > be n + 2. > > Execute "SELECT * FROM crawl_tree(682904,0)" and you're done. > > Hope this helps people. I have one question because I'm not clear about something with your implementation (a good one too by the looks of it -- thanks for sharing this information); if I start my query from an item at level 5, will the level be reflected as such, or will it dynamically start at 1? As I understand it, in Oracle the level would begin at 1 in this case. Thanks in advance. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] HELP ME
> Where can i find a tutorial on PL/PGSQL?.Help me by > listing some sites to guide me in this context. Does the following meet your needs? Chapter 19. PL/pgSQL - SQL Procedural Language http://www.sql.org/sql-database/postgresql/manual/plpgsql.html -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Oracle 'connect by prior' now eaiser in 7.3?
[sNip] > See connectby() in contrib/tablefunc. Someone was working on SQL99 > recursive queries but it didn't get done for 7.4 -- perhaps it will be > in 7.5. In the meantime, connectby() is in 7.3 and might work for you. Oracle 8i doesn't seem to have any special indexing to handle this efficiently. Do you happen to know if PostgreSQL will have a special indexing option for this feature? If it does, it will very likely provide a major performance advantage over Oracle. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Closed
> is here nobody? Is this group closed? I see that your question wasn't answered. There are people around here, but it looks like the activity is a bit scattered -- some newsgroups are much busier, such as Novell.Com's DeveloperNet and SpamCop.Net's help newsgroups, while there are others that see very little activity at all. Anyway, don't worry, this place is active in a healthy way. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Expressional Indexes
[sNip] >> I have been considering using "calculated index" or "computed index" >> but dunno if that really conveys anything. > > Well, "Expression Indexes" is the most accurate. Or "Expression-Based > Indexes." What is the proposed definition of an "Expression Index?" When I see this term, I get the impression I can create an index that's based on the results of a SELECT, such as for selecting data with specific values or ranges of values... For example, if I want to index on a date field but only have the index keep track of the most recent 30 days (and then create a secondary index for all dates) so as to improve performance on more heavily loaded systems. Am I understanding this new terminology correctly? Thanks in advance. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Expressional Indexes
>> For example, if I want to index on a date field but only have the index >> keep track of the most recent 30 days (and then create a secondary >> index for all dates) so as to improve performance on more heavily >> loaded systems. >> >> Am I understanding this new terminology correctly? Thanks in advance. > > No, you could do the above using "partial indexes" but it wouldn't work > very well in this case because the "last 30 days" keeps moving and you > would have to keep redefining the index periodically. For the application I will need to develop in the future, it would be okay for this index to hold data more than 30 days old, and then be redefined on a monthly basis along with regular database vacuuming (and other maintenance). Could this be done with a "partial index" as follows? CREATE INDEX my_index on my_table (create_date) WHERE (create_date > age(timestamp '30 days')); If I've made any mistakes here, please don't hesitate to let me know because the age() function is new to me. > It also wouldn't really help performance. Really? A smaller index would result in fewer comparisons behind-the- scenes though, wouldn't it? > Expression Indexes are just more powerful "functional indexes". In 7.3 > they could be used for indexing expressions like "lower(foo)". In 7.4 > they're more powerful and you can index expressions other than simple > function calls. [sNip] So an "Expression Index" could, for example, be used to sort alpha- numeric data in a case-insensitive manner? I just want to make sure I'm understanding this correctly. Thanks. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SOLVED: Emulating 'connect by prior' using stored proc
[sNip] > It's the same in this case. The tree building stuff only ever looks down > so the level returned in the query results will start at 1 no matter > where you enter your tree. In our case we could enter the tree at > 'node' 100 and get the tree below that but the function will start at 1 > because we only interested in the data below not the entry point and not > where in the tree we entered. Thanks. That's excellent news! =) > As in the solution if you really want to start it at 5 then set that as > the value of your second parameter. Interesting. Although I don't have an immediate need for this, I'm sure it could be useful when storing the results of multiple queries of this nature in a temporary table. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL a simple menu - plz help
>> You're in need of the "CONNECT BY" option which is ideal for
>> scenarios such as yours. Unfortunately PostgreSQL doesn't have it
>> yet, but I believe there is a PLSQL script (or something like this)
>> which emulates the CONNECT BY behaviour and it's called "connectby()"
>> or something like that.
>
> Thanks...Ill look into that.
Here, this link should help:
Search PostgreSQL - Opera
http://www.postgresql.org/search.cgi?q=connectby
--
Randolf Richardson - [EMAIL PROTECTED]
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/
This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] SQL a simple menu - plz help
> As subj says. A simple menu is defined in a table. But how to extract > it? Heres the deal: > > Table menu has these 3 columns: menuidparentmenudesc > > Ok this should be easy right? Those menu-rows that has parent=0 is > "toplevel". And so forth. An example: > > 10"Items" > 20"Standings" > 30"Test" > 42"Liverpool" > 52"AC Milan" > 61"Itemno1" > > This menu should in layout come in this order: > > Items > Itemno1 > Test > Standings > AC Milan > Liverpool > > That is: Alphabetically sorted and with the children underneath the > parents. Right? Yes - and selecting the menu and doing some gymnastics > in ASP isnt that hard. But ASP must be the language that has the > "crapiest" way of handling arrays...so a simple task is really messy. > Creating a stored procedure involes 2-3 temporary tables as I see it. > Not vey elegant either. > > So my question is: Isnt there any way of selecting this order directly > using SQL?? Some nice clean and beautiful way? You're in need of the "CONNECT BY" option which is ideal for scenarios such as yours. Unfortunately PostgreSQL doesn't have it yet, but I believe there is a PLSQL script (or something like this) which emulates the CONNECT BY behaviour and it's called "connectby()" or something like that. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(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] Expressional Indexes
[sNip]
>> bearing in mind that this index can be used with queries that contain
>> WHERE conditions like "create_date >= some-date-constant". The planner
>> must be able to convince itself that the right-hand side of the WHERE
>> condition is >= the cutoff in the index's predicate condition. Since
>> the planner is not very bright, both items had better be simple DATE
>> constants, or it won't be able to figure it out ...
>
> Note that if you're just doing this to speed up regular queries where
> you have create_date in some small range, then you'll likely not see
> much of an increase. Mainly you'll just save space.
Saving space is not my objective for this particular problem, but of
course it's definitely good news as I strive to optimize from every angle
whenever possible.
What I'm aiming for is to get an increase, no matter how small,
because the volume is expected to be extremely heavy duty (unfortunately I
can't discuss too many details of the project due to agreements I've made
with others), so even if the end result seems insignificant from the
perspective of a single query, the advantages become obvious when the
queries are performed repeatedly simultaneously for wide variety of massive
numbers of clients.
> What can be interesting is to create a partial index like this but over
> a second unrelated column. Something like:
>
> CREATE INDEX my_dec_03_index on my_table (userid)
> WHERE (create_date >= date '2003-11-02');
>
> Then you can do queries like
>
> SELECT * FROM my_table WHERE userid = ? AND create_date >= date
> '2003-11-02'
>
> And it'll be able to efficiently pull out just those records, even if
> there are thousands more records that are older than 2003-11-02.
What a fascinating trick. I could also use dummy data instead of
"userid" (which has obvious functionality as implied by its name), or even
data that the applications can even specify first because they'll be able
to determine things on the client-side that will make index selection more
appropriate.
I haven't checked into this yet, but is there a way to specify which
index PostgreSQL use as a parameter in a SELECT?
> This avoids having to create a two-column index with a low-selectivity
> column like "month".
Thanks, both of you. I've got some re-thinking to do for this project
I'm working on (since it's a low priority project at the moment, I've got
plenty of time to re-work the plan over and over again).
--
Randolf Richardson - [EMAIL PROTECTED]
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/
This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
