Re: [SQL] PL/pgSQL "compilation error"
On 3/14/01, 4:28:03 PM, Josh Berkus <[EMAIL PROTECTED]> wrote regarding Re: [SQL] PL/pgSQL "compilation error": > Tim, > > Hello all - I apologize for the newbie-esque question, but the debug output > > from postgres when you have a bug in your PL/pgSQL procedure is none to > > detailed. > It's an improvement over the query parser errors -- at least in > PL/pgSQL you get a line number! Although in your case, the line number > is deceptive. Line numbers always are deceptive when it comes to bracketing/quotes/structure. And you can get line-numbers on a query if you stick it in a text-file and use psql -f. > This brings up an important point. We have a medium-large user base for > PL/pgSQL out there, but it appears that Jan Wieck no longer has time to > develop the language ... nor should he be the sole developer. Howe do > we find more developers to expand & extend PL/pgSQL? I'd be willing to > contribute financially, but can't afford to actuall hire somebody on my > own (and don't have the moxie to doe the programming!). There are four issues with plpgsql: 1. features (not too bad, fine for most trigger functions, wouldn't want to write an OS in it). 2. error messages (could be better, although it might be that I'm just not used to them yet) 3. documentation (someone was doing a plpgsql cookbook - is (s)he still about?) 4. quoting literal strings (drives me up the *~!%&ing wall - have you tried putting a literal string in an execute?) This is partly growing pains with PG I'd guess, look at the changes since 6.early - PG is moving into the bigtime (I can remember Ingres from University days :-) I'd be loath for plpgsql to get carried away with functionality. I like Perl a lot, but I'm not sure I want to kick off 4M of interpreter every time someone inserts on a view. I'm happy to do my bit on the documentation side, but I don't want to end up duplicating the cookbook - does anyone know the status of this? I'd guess 90% of plpgsql functions will be on the same "theme" as something in a cookbook so it's the perfect format for learning/cut & paste. > > I've created the following procedure and am getting an error when I try to > > update the table. The error is something like "parse error near ; on line 50". > > Line 50 is the last line. > Actually, your question is not newbie-esque. That's quite a > sophisticated procedure! To be honest Josh, that was my opinion. > THe problem is simple. PL/pgSQL at this time does not support the "ELSE > IF" structure. Thus, the compiler is looking for more "END IF"s that it > doesn't find, and errors out when it gets to the end of the procedure > without seeing them. Missed that completely - reading my expectations, not the code. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] need to join successive log entries into one
>> Original Message << On 3/14/01, 5:24:12 PM, George Young <[EMAIL PROTECTED]> wrote regarding [SQL] need to join successive log entries into one: > I have a table like: > run | seq | start| done > 1415|261| 2001-01-29 12:36:55| 2001-02-07 13:02:38 > 1415|263| 2001-02-14 07:40:04| 2001-02-15 16:05:04 > 1415|264| 2001-02-16 16:05:05| 2001-03-08 16:34:03 > 1415|265| 2001-03-08 16:34:04| > This is a log of some operations done on some runs. 'seq' is the step > within the run. > I need to produce a new table that coalesces immediately successive > operations on a run into one, e.g.: > run | start | done > 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38 > 1415| 2001-02-14 07:40:04| > 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39 > 1747| 2001-03-05 13:13:58| > 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15 Try: select run,min(start),max(done) from mytable group by run; - Richard Huxton ---(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] pl/Perl
On 3/14/01, 5:04:49 PM, Jie Liang <[EMAIL PROTECTED]> wrote regarding Re: [SQL] pl/Perl : > Tom, I'm not the organ grinder, but maybe the monkey can help ;-) > 1.Where or how I can get pltcl.so? I have not find this file anywhere in > my > source except a pltcl.c. Did you set the relevant parameter with ./configure? Something like --with-tcl probably --help will give a list of options. > 2.Dose installation same as plpgsql? > i.e. > CREATE FUNCTION pltcl_call_handler () RETURNS OPAQUE AS > '/usr/local/pgsql/lib/pltcl.so' LANGUAGE 'C'; I just use the "createlang" command-line tool. > CREATE TRUSTED PROCEDURAL LANGUAGE 'pltcl' > HANDLER pltcl_call_handler > LANCOMPILER 'PL/pgtcl'; > 3.Where I can find more doc about pltcl? There's a bit in the user-manual (developer's version - 7.1 - anyway) on the website. Actually - there seems to be a break in the "Next" links in my local copy of the docs. Is that just me? Try a google search for tcl/tk - should turn up plenty of general stuff on the language. I don't use it myself, but it seems great for simple projects. - Richard Huxton > Thanks for your time. > Jie LIANG ---(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] List Concatination
On 3/15/01, 5:02:37 PM, Tom Lane <[EMAIL PROTECTED]> wrote regarding Re: [SQL] List Concatination : > Josh Berkus <[EMAIL PROTECTED]> writes: > >> Note that this is probably not a good idea - the ordering of the > >> contacts will not be well-defined. When I asked about this Tom Lane was > >> quite surprised that it worked, so no guarantees about long-term > >> suitability. Sorry - issue was to do with the ordering of the concatenation, not the user-defined aggregates (iirc - it's getting late here). I do remember I got different orders when selecting and updating. In my case it didn't matter, and I'm guessing if the order reverses in your case when 8.x is released it's not the end of the world either. If you were joining words in a sentence, obviously it would matter (unless you were on usenet ;-) > > Hmmm ... this feature is very, very, useful now that I know how to use > > it. I'd love to see it hang around for future versions of PgSQL. Tom? > As I said before, user-defined aggregates are certainly not going away. > I don't recall the conversation Richard was thinking of, so I'm not sure > exactly what was at issue there. Most likely it was some fine point, > not the basic existence of the feature. > regards, tom lane No not at all - and sorry for any confusion. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] MultiByte strings
On 3/19/01, 10:13:40 AM, Tubagus Nizomi <[EMAIL PROTECTED]> wrote regarding [SQL] MultiByte strings: > Hello, > i have an error > "psql: ERROR: MultiByte strings (MB) must be enabled to use this function" > where i must enable MultiByte strings (MB) ?? Assuming you enabled this at the ./configure stage you need to use the -E encoding flag when you run createdb. See the Administrator's Guide / Localisation section for details. - Richard Huxton ---(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] SOME PL/PGSQL PROBLEMS
On 3/23/01, 9:10:16 AM, datactrl <[EMAIL PROTECTED]> wrote regarding [SQL] SOME PL/PGSQL PROBLEMS: Sorry, can't reproduce this at all Jack > I found there are some problems with PL/pgSQL. > 1) 'Drop Table' doesn't work at all No problems in 7.1, can't remember about previous versions > 2) '--' comment causes parsing error No problems ever AFAIK > 3) Execute doesn't work at all. It always causes parsing error! I know this is only in 7.1, not in earlier versions > Some questions > 1) Can I use variable in FROM clause for a table name? No - afraid not. Queries are pre-compiled as I understand it, although this is possible in pltcl (and plperl I think). > 2) Can I use variable in WHERE such as xxx = variable? Yes, exactly as you show > JACK Take a look at http://www.brasileiro.net/postgres/ for some example plpgsql functions or check http://techdocs.postgresql.org or Bruce's book at http://www.postgresql.org/docs/awbook.html If you have a specific example, please post it with the version of PG you are running. HTH - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] rows equal
On 3/20/01, 7:23:04 PM, Marios Moutzouris <[EMAIL PROTECTED]> wrote regarding [SQL] rows equal: > Hello > I need a sql query to a postgresql database which can tell me whether the > rows returned ar equal. > i.e > --- > Status > --- > Order > --- > Collected > -- > This is two columns. Both are not equal. > Any help?...without having to write code to fetch the result and go > through etc etc. Sorry - don't think so. Unless you've defined a unique index on one or more of these columns you'll have to check the contents of the columns. If they are never supposed to be the same, define a unique index. - Richard Huxton ---(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
