[SQL] Please don't kill me!
I have two statements that accomplish the same task and I'm trying to decide which to use. One uses a sub-select, and the other just does a few more joins. I expect that giving the SELECT statement's themseleves won't get me much help, so here is the output of the EXPLAIN query that I ran on both of them. I read the FAQ on EXPLAIN a bit but I'm still confused. So could somebody help me understand why it appears as though the first query will run much faster (?) than the second? --snip!-- Nested Loop (cost=81.80..114.17 rows=33 width=68) InitPlan -> Seq Scan on l_portal_statuses (cost=0.00..22.50 rows=10 width=4) -> Merge Join (cost=81.80..86.63 rows=3 width=52) -> Merge Join (cost=59.13..63.43 rows=33 width=44) -> Sort (cost=22.67..22.67 rows=10 width=28) -> Seq Scan on contacts m (cost=0.00..22.50 rows=10 width=28) -> Sort (cost=36.47..36.47 rows=333 width=16) -> Seq Scan on buildings b (cost=0.00..22.50 rows=333 width=16) -> Sort (cost=22.67..22.67 rows=10 width=8) -> Seq Scan on contracts c (cost=0.00..22.50 rows=10 width=8) -> Index Scan using executives_pkey on executives e (cost=0.00..8.14 rows=10 width=16) Merge Join (cost=174.38..247.30 rows=333 width=76) -> Index Scan using executives_pkey on executives e (cost=0.00..60.00 rows=1000 width=16) -> Sort (cost=174.38..174.38 rows=33 width=60) -> Merge Join (cost=167.58..173.53 rows=33 width=60) -> Merge Join (cost=59.13..63.43 rows=33 width=44) -> Sort (cost=22.67..22.67 rows=10 width=28) -> Seq Scan on contacts m (cost=0.00..22.50 rows=10 width=28) -> Sort (cost=36.47..36.47 rows=333 width=16) -> Seq Scan on buildings b (cost=0.00..22.50 rows=333 width=16) -> Sort (cost=108.44..108.44 rows=100 width=16) -> Merge Join (cost=92.50..105.12 rows=100 width=16) -> Sort (cost=69.83..69.83 rows=1000 width=12) -> Seq Scan on contracts c (cost=0.00..20.00 rows=1000 width=12) -> Sort (cost=22.67..22.67 rows=10 width=4) -> Seq Scan on l_portal_statuses l (cost=0.00..22.50 rows=10 width=4) --snip!-- Hopefully that's not too ugly. TIA -- Dave
[SQL] Don't want blank data
Greetings, Is there a way to have postgresql always return a value for each row requested? To be more clear, if I were using a Perl SQL hybrid I would write something like SELECT computer_ip or 'unset' FROM computers; So that if computers.computer_ip is NULL or '' I will get 'unset' back from the database. I hope this makes sense and somebody can point me in a good direction -- Dave
Re: [SQL] Change or get currentdb
>From \? \c[onnect] [dbname|- [user]] connect to new database (currently '') so typing "\c" gives you the database you're currently connected to and "\c " would connect you to that database. On Thu, 25 Jan 2001, Sergiy Ovcharuk wrote: ->How can I change and/or get to know a current db name using sql script in ->PostgreSQL? -- Dave
Re: [SQL] SQL Join - MySQL/PostgreSQL difference?
On Thu, 1 Feb 2001, Brice Ruth wrote: ->SELECT -> a.Number, -> a.Code, -> a.Text ->FROM -> b, -> a ->WHERE -> (b.Id = a.Id) AND These next two statements are very ambiguous. Make them explicit as you have with "(b.Id = a.Id)" and "(b.d_Id = 'key3')" Also, be sure that 'key3' is how what you want looks in the database -> (VersionId = 'key1') AND -> (Category = 'key2') AND -> (b.d_Id = 'key3') ->ORDER BY -> a.Number; Also, make sure ( '\d b' ) that your columns are case-sensatively named 'Id' and such as this does matter. -- Dave
[SQL] Data Types
Hello. I have a table in which I'm trying to store the length of a sound file. I decided to use the TIME data type. Was this correct? One of the operations I want to do is sum() all of my files lengths to get the total amount in terms of time, of sound that I have. I notice that sum() doesn't take a TIME argument, so I cast it to an interval as such: SELECT SUM( length::interval ) FROM songs; However this gives me output that I don't know how to read: '7 02:34:27' Does that read as 7 Days, 2 Hours, 34 Minutes and 27 seconds? TIA -- Dave
Re: [SQL] Temp Tables & Connection Pooling
On Fri, 2 Mar 2001, Gerald Gutierrez wrote: ->Recently I wanted to implement Dijkstra's algorithm as a stored procedure, ->and finding that PL/PGSQL cannot return record sets, I thought about using ->a temporary table for the results. If tempoary tables are session-specific, ->however, then wouldn't connection pooling make it unusable since the table ->might "disappear" from one query to the next? What are alternative ->approaches to implementing Dijkstra's algorithm inside the database? Wouldn't a VIEW do what you want? -- Dave ---(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] Two way encryption in PG???
On Sun, 4 Mar 2001, Boulat Khakimov wrote: ->How do I encrypt/decrypt something in PG? Perhaps it'd be better to one-way encrypt something? Granted I don't know the details of your project, but allowing a way to "decrypt" something is rather insecure. -- Dave ---(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] How do I use text script containing SQL?
On Mon, 5 Mar 2001, Jeff S. wrote: ->I want to be able to use the file to create my table. ->I've tried psql -d databasename -e < filename.txt ->but that doesn't work. You're making it too dificult :-) 'psql -d databasename < filename.txt' should work just fine -- Dave ---(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
[SQL] explain EXPLAIN?
Hello, I'm looking for a better tutorial of how EXPLAIN works. I know Mr. Tom Lane wrote a "quick & dirty explanation" and that "plan-reading is an art that deserves a tutorial, and I haven't had time to write one". In which case I'd like to know if there's any other tutorials/resources. I think I get the jist of it (an index scan is better than a seq scan?) but I'd like to read more. Does anybody have any suggestions? -- Dave ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Optimization via explicit JOINs
Greetings, I've been toying aroudn with postgres 7.1beta5's ability to control the planner via explicitely JOINing tables. I then (just for giggles) compare the difference in the EXPLAIN results. I'm no super-mondo-DBA or anything, but in my two attempts so far, the numbers I get out of EXPLAIN have been about 1/2 as small. Below are two EXPLAIN results, am I correct in reading that one is indeed "twice as fast" as the other? I say twice as fast because the top-most cost in the first query is 58.62, but in the second one it's only 32.09. Am I reading this correctly? -- First EXPLAIN -- Sort (cost=58.62..58.62 rows=14 width=60) -> Nested Loop (cost=0.00..58.35 rows=14) -> Nested Loop (cost=0.00..29.99 rows=14) -> Seq Scan on playlist p (cost=0.00..1.61 rows=14) -> Index Scan using songs_pkey on songs s (cost=0.00..2.01 rows=1) -> Index Scan using artists_pkey on artists a (cost=0.00..2.01 rows=1) -- Second EXPLAIN -- Sort (cost=32.09..32.09 rows=1) -> Nested Loop (cost=0.00..32.08 rows=1) -> Nested Loop (cost=0.00..30.06 rows=1) -> Seq Scan on playlist p (cost=0.00..1.61 rows=14) -> Index Scan using songs_pkey on songs s (cost=0.00..2.02 rows=1) -> Index Scan using artists_pkey on artists a (cost=0.00..2.01 rows=1) -- Dave ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Optimization via explicit JOINs
On Fri, 9 Mar 2001, Stephan Szabo wrote: ->Not entirely. Those are only estimates, so they don't entirely line up ->with reality. Also, I notice the first estimates 14 rows and the second ->1, which is probably why the estimate is higher. In practice it probably ->won't be significantly different. So really I'm just getting back estimations of cost and rows returned? Incidentally, both queries returned the same data set, that's a Good Thing (tm). -- Dave ---(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] Optimization via explicit JOINs
On Fri, 9 Mar 2001, Stephan Szabo wrote: -> Hmm, what were the two queries anyway? The "slower" query SELECT to_char( p.insertion_time, 'HH:MI AM MM/DD' ) as time_in, s.nameas title, a.nameas artist, s.length as length FROM playlist p, songss, artists a WHERE p.waiting = TRUE AND p.song_id = s.song_id AND s.artist_id = a.artist_id ORDER BY p.item_id The "faster" query SELECT to_char( p.insertion_time, 'HH:MI AM MM/DD' ) as time_in, s.nameas title, s.length as length, a.nameas artist FROM playlist p JOIN songs s USING (song_id), artists a WHERE p.waiting = TRUE AND p.song_id = s.song_id AND s.artist_id = a.artist_id ORDER BY p.item_id; Notice how the only difference is in the FROM clause? -- Dave ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Optimization via explicit JOINs
On Fri, 9 Mar 2001, Stephan Szabo wrote: ->As a question, how many rows does ->select * from playlist p join songs s using (song_id) where ->p.waiting=TRUE; ->actually result in? Well it depends. Most of the time that playlist table is "empty" (no rows where waiting = TRUE), however users can (in a round about way) insert into that table, so that there could be anywhere from 10, to 2,342, to more. Why do you ask? (The reason those plans chose 14 was because, at the time, there were 14 rows in playlist) -- Dave ---(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: [SQL] Select very slow...
On Sun, 18 Mar 2001, Fernando Eduardo B. L. e Carvalho wrote: > select p.city,count(*) from sales s, person p where s.doc = p.doc > group by p.city; > >Anyone help-me? 1: VACUUM ANALYZE sales VACUUM ANALYZE person; 2: That 'count(*)' is going to be slow. Try counting a column that's indexed (p.doc might work?) 3: EXPLAIN ; That should give you some hints on what to optimize. -- Dave ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Self-Referencing
Hello, I have a feeling this isn't going to make much sense, but I'm gonig to try anyway. What I'd like to do is be able to refer to an outer-SELECT from an inner-SELECT. I hope this makes sense. I need to be able to refer to the row that's being processed in a SELECT. I'm going to use the idea of 'this' referring to the row that's currently being processed. Here's the example of what I'd like: SELECT building_id, num_buildings, ( SELECT count( building_id ) FROM building_portals WHERE building_id = THIS.building_id ) FROM buildings; Am I making things too complicated, and if so will somebody *PLEASE* tell me the easier way to do this. Thanks. -- Dave ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] DROP TABLE in transaction
Hello. I was wondering if anybody could explain to me why I can't roll back dropping a table. I would think that of all the events that should be rollback-able, dropping a table would be the first on the list. -- Dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] DROP TABLE in transaction
On Thu, 12 Apr 2001, Peter Eisentraut wrote: > Because DROP TABLE removes the table file on disk, and you can't roll back > that. Actually, in 7.1 you can. ;-) Well I understand that it's being taken from the disk, but why does that action have to be done *right now*? Why can't it be postponed until I type 'commit;' ? I wonder how much time this addition would have saved those of us who type quickly and use the tab-completion too much :) -- Dave ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] is this proper sql?
On Tue, 17 Apr 2001, clayton cottingham wrote: > now i personally dont think this is real sql > anyone? Nope, not real. Although that type of syntax would be handy IMHO. -- Dave ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] index/join madness
On Wed, 23 May 2001, Michael Richards wrote: > Finally, I'm planning on moving this to 7.2 and converting all the > joins to use outer joins. Will there be a significant penalty in > performance running outer joins? Why are you planning on using outer joins? Yes there is a performance penalty because postgres will have to emit more tuples. Are you sure that you need to use outer joins? -- Dave ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Help with LIKE
I have a table with 8,628,633 rows that I'd LIKE to search (ha ha). I have a very simple query: SELECT * FROM tableA WHERE column1 LIKE '%something%'; tableA.column1 has an index on it and the database has been vacuumed recently. My problem is with the output of EXPLAIN: ++ | QUERY PLAN | ++ | Seq Scan on tableA (cost=0.00..212651.61 rows=13802 width=46) | | Filter: (column1 ~~ '%something%'::text) | ++ I don't like that cost (2,12,651) at all! Is there anyway I can optimize this query? Make a different kind of index (it's currently btree)? Use substr or indexof or something instead of LIKE? Thoughts? -- David Olbersen iGuard Engineer 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Rows UPDATEd?
I'm looking for a way to see how many rows were UPDATEd. I Googled a bit and found that if I were using pl/pgsql I could use 'GET DIAGNOSTICS'. But I'm not using pl/pgsql. Is there some other way I could find out how many rows were affected by the last statement in my transaction? For what it's worth, a Perl script is doing this using the Pg module. I didn't see anything in the Pg man page describing this. It does cover INSERT and DELETE by using "$cmdStatus = $result->cmdStatus", but not UPDATE. Any suggestions? -- David Olbersen iGuard Engineer 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Rows UPDATEd? (solved!)
Using the Pg module, one can use $res->cmdStatus for UPDATE as well as INSERT and DELETE to see how many rows were UPDATE/INSERT/DELETEd and what action was taken. One can also use $res->cmdTuples in the same manner. Looks like the documentation is just a little lacking. -- David Olbersen iGuard Engineer 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 > -Original Message- > From: David Olbersen > Sent: Thursday, May 29, 2003 10:01 AM > To: [EMAIL PROTECTED] > Subject: [SQL] Rows UPDATEd? > > > > I'm looking for a way to see how many rows were UPDATEd. I > Googled a bit and found that if I were using pl/pgsql I could > use 'GET DIAGNOSTICS'. But I'm not using pl/pgsql. Is there > some other way I could find out how many rows were affected > by the last statement in my transaction? > > For what it's worth, a Perl script is doing this using the Pg > module. I didn't see anything in the Pg man page describing > this. It does cover INSERT and DELETE by using "$cmdStatus = > $result->cmdStatus", but not UPDATE. > > Any suggestions? > > -- > David Olbersen > iGuard Engineer > 11415 West Bernardo Court > San Diego, CA 92127 > 1-858-676-2277 x2152 > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] (long) What's the problem?
Anybody have any ideas about a problem with this query?
urldb2=> EXPLAIN
urldb2-> SELECT
urldb2-> id,
urldb2-> source,
urldb2-> insertedby,
urldb2-> insertedon,
urldb2-> priority
urldb2-> FROM
urldb2-> indexscan
urldb2-> WHERE
urldb2-> lower(
urldb2-> substring(
urldb2-> urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2-> ) ~ '^q.*'
urldb2-> ORDER BY source;
QUERY PLAN
---
Sort (cost=23.50..23.50 rows=3 width=48)
Sort Key: source
-> Seq Scan on indexscan (cost=0.00..23.47 rows=3 width=48)
Filter: (lower("substring"(urlhost(source), (rposition('www.'::text,
(urlhost(source))::character varying) + 2))) ~ '^q.*'::text)
(4 rows)
OK, cost=23.50..23.50, should be a quickie. I'll EXPLAIN ANALYZE just to be safe:
urldb2=> EXPLAIN ANALYZE
urldb2-> SELECT
urldb2-> id,
urldb2-> source,
urldb2-> insertedby,
urldb2-> insertedon,
urldb2-> priority
urldb2-> FROM
urldb2-> indexscan
urldb2-> WHERE
urldb2-> lower(
urldb2-> substring(
urldb2-> urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2-> ) ~ '^q.*'
urldb2-> ORDER BY source;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Since EXPLAIN ANALYZE runs the query and then analyzes the results I haven't included
that in this email because the error is the same.
urlhost() is an immutable custom Pl/Perl function.
rposition() is a volatile custom C function.
indexscan has 614 tuples and only takes up 7 pages.
The load on this machine is zero when I attempt this, and no other processes are
trying to use massive amounts of resources. This is a P3 550 with 512MB of RAM.
I can provide more information if needed.
Anybody have ideas about the problem?
--
David Olbersen
iGuard Engineer
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] (long) What's the problem?
Anybody have any ideas about a problem with this query?
urldb2=> EXPLAIN
urldb2-> SELECT
urldb2-> id,
urldb2-> source,
urldb2-> insertedby,
urldb2-> insertedon,
urldb2-> priority
urldb2-> FROM
urldb2-> indexscan
urldb2-> WHERE
urldb2-> lower(
urldb2-> substring(
urldb2-> urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2-> ) ~ '^q.*'
urldb2-> ORDER BY source;
QUERY PLAN
---
Sort (cost=23.50..23.50 rows=3 width=48)
Sort Key: source
-> Seq Scan on indexscan (cost=0.00..23.47 rows=3 width=48)
Filter: (lower("substring"(urlhost(source), (rposition('www.'::text,
(urlhost(source))::character varying) + 2))) ~ '^q.*'::text)
(4 rows)
OK, cost=23.50..23.50, should be a quickie. I'll EXPLAIN ANALYZE just to be safe:
urldb2=> EXPLAIN ANALYZE
urldb2-> SELECT
urldb2-> id,
urldb2-> source,
urldb2-> insertedby,
urldb2-> insertedon,
urldb2-> priority
urldb2-> FROM
urldb2-> indexscan
urldb2-> WHERE
urldb2-> lower(
urldb2-> substring(
urldb2-> urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2-> ) ~ '^q.*'
urldb2-> ORDER BY source;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Since EXPLAIN ANALYZE runs the query and then analyzes the results I haven't included
that in this email because the error is the same.
urlhost() is an immutable custom Pl/Perl function.
rposition() is a volatile custom C function.
indexscan has 614 tuples and only takes up 7 pages.
The load on this machine is zero when I attempt this, and no other processes are
trying to use massive amounts of resources. This is a P3 550 with 512MB of RAM.
I can provide more information if needed.
Anybody have ideas about the problem?
--
David Olbersen
iGuard Engineer
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Datatype conversion help
Yasir, If this is a date you're playing with, simply use: to_char( , 'MM-DD-' ) to get what you want. -- David Olbersen iGuard Engineer St. Bernard Software 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 > -Original Message- > From: Yasir Malik [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 08, 2003 1:29 PM > To: [EMAIL PROTECTED] > Subject: Re: [SQL] Datatype conversion help > > > Thank you so much! But my problem is that when I do > to_char(mn, '00') || '-' || to_char(dy, '00') || '-' || to_char(yr, > '') > > where mn, dy, and yr are ints, is that the output has a space > after the > the dash. For example, I get > 07- 25- 1994 > > instead of what I want: > 07-25-1994 > > Thanks, > Yasir > > On Tue, 8 Jul 2003, Richard Rowell wrote: > > > Date: 08 Jul 2003 15:21:33 -0500 > > From: Richard Rowell <[EMAIL PROTECTED]> > > To: Yasir Malik <[EMAIL PROTECTED]> > > Subject: Re: [SQL] Datatype conversion help > > > > On Tue, 2003-07-08 at 15:07, Yasir Malik wrote: > > > I've tried to_char(in_val, '99'), and that returns a > string that is two > > > > select to_char(9,'00'); > > > > ---(end of > broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Functional Indexes
Hello all, I have a function (urlhost) which finds the 'host' portion of a URL. In the case of http://www.foobar.com/really/long/path/to/a/file it returns "www.foobar.com". I also have a function (urltld) which returns the TLD of a URL. In the case of http://www.foobar.com/really/long/path/to/a/file it returns ".com" (the leading dot is OK). urltld uses urlhost to do it's job (how should be apparent). Now the question: is there a single index I can create that will be used when my WHERE clause contains either urlhost or urltld? I could create two functional indexes, but that seems a bit silly to me. Any thoughts? -- David Olbersen iGuard Engineer St. Bernard Software 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Line length in pl/pgsql function
Tom Lane wrote: > Hmm ... plpgsql had some string-length issues as recently as 7.2.2, but > I don't know of any problems since then. Could you submit a *complete* > test case, rather than making us guess the details? Sure. I didn't want to dump a huge email to have somebody say "Addressed in 7.3.5" :) PostgreSQL version...: 7.3.4 Compiled with: gcc 2.95.3 Architecture.: Intel Pentium III Operating System.: FreeBSD 4.5-STABLE Reproduction -- 1) Connect to the database using psql 2) Attempt to create a return type and function using: \i test_ratedby_category_lang.plsql The errors from psql are attached as psql-errors.txt The file with the function is attached as test_ratedby_category_lang.plsql The postgresql.conf and current postmaster.opts are attached as well. I can send syslog output as well, if desired, at any debug level (I don't know what's appropriate). I tried reproducing the bug before I composed this message and it didn't break in the same way as yesterday. That's not very helpful -- but it did still break with the same error message (as seen in psql-errors.txt) but my syslog output was different. I can attach both if that's helpful. Any more info needed? -- David Olbersen iGuard Engineer St. Bernard Software 15015 Avenue of Sciences San Diego, CA 92127 x2152 psql:test_ratedby_category_lang.plsql:1: ERROR: Relation 'ratedby_return_set' already exists psql:test_ratedby_category_lang.plsql:101: ERROR: parser: parse error at or near "t" at character 3613 psql:test_ratedby_category_lang.plsql:102: ERROR: parser: parse error at or near "LOOP" at character 21 psql:test_ratedby_category_lang.plsql:118: ERROR: parser: parse error at or near "ELSIF" at character 13 psql:test_ratedby_category_lang.plsql:119: ERROR: parser: parse error at or near "LOOP" at character 21 psql:test_ratedby_category_lang.plsql:135: ERROR: parser: parse error at or near "ELSE" at character 13 psql:test_ratedby_category_lang.plsql:136: ERROR: parser: parse error at or near "LOOP" at character 21 psql:test_ratedby_category_lang.plsql:137: ERROR: parser: parse error at or near "IF" at character 17 psql:test_ratedby_category_lang.plsql:156: ERROR: parser: parse error at or near "ELSE" at character 9 psql:test_ratedby_category_lang.plsql:157: ERROR: parser: parse error at or near "LOOP" at character 21 psql:test_ratedby_category_lang.plsql:174: ERROR: parser: parse error at or near "ELSIF" at character 13 psql:test_ratedby_category_lang.plsql:175: ERROR: parser: parse error at or near "LOOP" at character 21 psql:test_ratedby_category_lang.plsql:193: ERROR: parser: parse error at or near "ELSE" at character 13 psql:test_ratedby_category_lang.plsql:194: ERROR: parser: parse error at or near "LOOP" at character 21 psql:test_ratedby_category_lang.plsql:195: ERROR: parser: parse error at or near "IF" at character 17 psql:test_ratedby_category_lang.plsql:196: ERROR: parser: parse error at or near "IF" at character 13 psql:test_ratedby_category_lang.plsql:198: ERROR: parser: parse error at or near "RETURN" at character 9 psql:test_ratedby_category_lang.plsql:199: WARNING: COMMIT: no transaction in progress COMMIT psql:test_ratedby_category_lang.plsql:200: ERROR: parser: parse error at or near "' LANGUAGE '" at character 1 test_ratedby_category_lang.plsql Description: test_ratedby_category_lang.plsql postgresql.conf Description: postgresql.conf postmaster.opts Description: postmaster.opts ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Line length in pl/pgsql function
Tom Lane wrote: > Hate to tell you this, but it's just pilot error. You've got comments > like these embedded in the plpgsql function: > > ELSIF cat = ''none'' THEN > -- none,none = don't show the languages, or categories > (whaaat?) FOR result IN > > That quote in "don't" has to be doubled. Remember this whole thing is a > giant string literal as far as the outer CREATE FUNCTION syntax is > concerned. The fact that the quote is within a comment in terms of the > plpgsql syntax doesn't mean a thing to the outer string-literal parser. *sigh* I'd rather have pilot error than having to wait for a patch :) It's funny, I use Vim with syntax highlighting to catch this sort of thing. 99% of the time it does, I guess this is that other 1%. > PS: note to hackers: Fabien's new error localization code does a pretty > decent job of fingering the problem. When I loaded this test file into > CVS tip I got > > psql:test_ratedby_category_lang.plsql:95: ERROR: syntax error at or near "t" > at character 3419 psql:test_ratedby_category_lang.plsql:95: LINE 81: > -- none,everything = don't show the language... > psql:test_ratedby_category_lang.plsql:95: > ^ That would have been handy! -- David Olbersen iGuard Engineer St. Bernard Software 15015 Avenue of Sciences San Diego, CA 92127 x2152 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Line length in pl/pgsql function
David Olbersen wrote: > *sigh* I'd rather have pilot error than having to wait for a patch :) Hmmm, that doesn't look right in retrospect. What I meant to say was THANK YOU TOM! -- David Olbersen iGuard Engineer St. Bernard Software 15015 Avenue of Sciences San Diego, CA 92127 x2152 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
