[SQL] Re: Beginner problems with functions
On Mon, 21 Aug 2000, Stephan Szabo wrote: > I haven't thought of an elegant way to do it, although you could > fake some of it with a table of the appropriate structure with a sequence. > It's really ugly, but the pl/sql(tcl/perl/etc...) function gets the > next value of the sequence and inserts the results into a table with > the sequence number and returns the number to you. OK, this might probably work for the short time. Could someone give me any hope for the future that there will be other solutions in higher versions of PostgreSQL which support the missing feature? Kind regards Andreas.
Re: [SQL] Continuous inserts...
Hi! At 08:18 18.08.00 -0700, you wrote: [...] >I didn't try with vacuum, I just did a table lock and that >seemed to still hang the inserts with two tables, so I figured >maximum safety was adding the third table. If it works with two >that's much cooler. Was this with real data or just a small test >set? It was a test set ... ~2 records, *BUT* I found that postgres decides when it starts to use the rule - means, if you do continous inserts on the table and create the rule, there's a varying time until the rule applies. In my first tests, I re-connected the DB very often, and the the change seemed immediate. Any ideas on how to 'promote' the rules faster?!? Greetings, Joe -- +-- Science & Engineering Applications GmbH --+ | | | Joerg Hessdoerfer | | Leading SW developer Phone:+49 (0)2203-962211 | | S.E.A GmbH Fax: -962212 | | D-51147 KoelnInternet: [EMAIL PROTECTED] | |http://www.sea-gmbh.com| +---+
[SQL] using INTERSECT and UNION in IN clause
Hi, postgresql 7.0.2. Why by executing the following query select * from magazine where id in ( select mag_id from dict where word = 'akademie' intersect select mag_id from dict where word = 'der' intersect select mag_id from dict where word = 'klasse' ) I receive the following error: ERROR: parse error at or near 'intersect' while the query select mag_id from dict where word = 'akademie' intersect select mag_id from dict where word = 'der' intersect select mag_id from dict where word = 'klasse' ) is executed successfully. Is it possible to use INTERSECT and UNION keywords in subqueries? Regards, Alex
[SQL] tip: weird parse error for pl/pgsql
Hi everyone, After fiddling for about a day to work out why my pl/pgsql stored procedures weren't working I finally discovered why. It seems that pl/pgsql has a problem parsing Window style new line characters. I was writing my stored procedures using a Windows app, and then running them on my Linux Postgres database. It keeped on giving me... an error found one line 1 near " " which wasn't very helpful. Anyway just make sure you write your stored procs in your Unix environment or save them as Unix format. I hope this tip saves somebody some time. :) Cheers, Keith.
[SQL] Time Help
Hello all, I have a query result of @ 2 hours 10 mins 6 secs and I would like to change that to 02:10:06. Currently the field is listed as "timespan" This allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 min 6 sec which are all the formats that I will be entering the time formats! How do I convert it into a the format of 02:10:06 Thanks to you all
Re: [SQL] Time Help
Mark, I tried that and had to change it to: SELECT '0:00:00'::timespan + '02:10:06'::timespan; To get any response. the response i got was @ 2 hours 10 mins 6 secs Still in the wrong format If is use : SELECT '0:00:00'::time + '02:10:06'::timespan; It get No such function 'time_timespan' with the specified attributes So i guess what I want to do is convert a timespan into time How would I do that? At 09:55 AM 8/22/00 -0400, Mark Volpe wrote: >I'm not sure at all what you are asking, but I'm thinking you're trying to >convert a "timespan" to a "time". Try adding it to a time like this: >SELECT '0:00:00'::time + '02:10:06'::timespan; > >Mark > >"Brian C. Doyle" wrote: > > > > Hello all, > > > > I have a query result of @ 2 hours 10 mins 6 secs and I would like to > > change that to 02:10:06. Currently the field is listed as "timespan" This > > allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 min 6 sec > > which are all the formats that I will be entering the time formats! How do > > I convert it into a the format of 02:10:06 > > > > Thanks to you all
Re: [SQL] Time Help
I'm not sure at all what you are asking, but I'm thinking you're trying to convert a "timespan" to a "time". Try adding it to a time like this: SELECT '0:00:00'::time + '02:10:06'::timespan; Mark "Brian C. Doyle" wrote: > > Hello all, > > I have a query result of @ 2 hours 10 mins 6 secs and I would like to > change that to 02:10:06. Currently the field is listed as "timespan" This > allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 min 6 sec > which are all the formats that I will be entering the time formats! How do > I convert it into a the format of 02:10:06 > > Thanks to you all
Re: [SQL] using INTERSECT and UNION in IN clause
Alex Guryanow <[EMAIL PROTECTED]> writes: > Is it possible to use INTERSECT and UNION keywords in subqueries? No, not at the moment. This is one of many things we hope to fix when we redesign querytrees (currently planned for 7.2 cycle). regards, tom lane
Re: [SQL] Time Help
I tried it on a box with postgres 6.5.3 and I got the result you did. On postgres 7.0 I get 02:10:06 for both results. You should upgrade to 7.0 - it is _much_ better. Mark "Brian C. Doyle" wrote: > > Mark, > > I tried that and had to change it to: > > SELECT '0:00:00'::timespan + '02:10:06'::timespan; > > To get any response. the response i got was > > @ 2 hours 10 mins 6 secs > > Still in the wrong format > If is use : > > SELECT '0:00:00'::time + '02:10:06'::timespan; > > It get > > No such function 'time_timespan' with the specified attributes > > So i guess what I want to do is convert a timespan into time > How would I do that? > > At 09:55 AM 8/22/00 -0400, Mark Volpe wrote: > >I'm not sure at all what you are asking, but I'm thinking you're trying to > >convert a "timespan" to a "time". Try adding it to a time like this: > >SELECT '0:00:00'::time + '02:10:06'::timespan; > > > >Mark > > > >"Brian C. Doyle" wrote: > > > > > > Hello all, > > > > > > I have a query result of @ 2 hours 10 mins 6 secs and I would like to > > > change that to 02:10:06. Currently the field is listed as "timespan" This > > > allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 min 6 sec > > > which are all the formats that I will be entering the time formats! How do > > > I convert it into a the format of 02:10:06 > > > > > > Thanks to you all
Re: [SQL] Time Help
SELECT '0:00:00'::time + '@ 2 hours 10 mins 6 secs'::timespan; ?column? -- 02:10:06 Mark "Brian C. Doyle" wrote: > > Mark, > > On your 7.0 box would you do: > > SELECT '0:00:00'::time + '@ 2 hours 10 mins 6 secs':timespan; > > For me and see if it will convert it! Need to decide if the upgrade will be > with it and if it does this then it is > > Thanks for your help Mark > > At 10:36 AM 8/22/00 -0400, you wrote: > >I tried it on a box with postgres 6.5.3 and I got the result you did. On > >postgres 7.0 I get 02:10:06 for both results. You should upgrade to 7.0 - it > >is _much_ better. > > > >Mark > > > >"Brian C. Doyle" wrote: > > > > > > Mark, > > > > > > I tried that and had to change it to: > > > > > > SELECT '0:00:00'::timespan + '02:10:06'::timespan; > > > > > > To get any response. the response i got was > > > > > > @ 2 hours 10 mins 6 secs > > > > > > Still in the wrong format > > > If is use : > > > > > > SELECT '0:00:00'::time + '02:10:06'::timespan; > > > > > > It get > > > > > > No such function 'time_timespan' with the specified attributes > > > > > > So i guess what I want to do is convert a timespan into time > > > How would I do that? > > > > > > At 09:55 AM 8/22/00 -0400, Mark Volpe wrote: > > > >I'm not sure at all what you are asking, but I'm thinking you're trying to > > > >convert a "timespan" to a "time". Try adding it to a time like this: > > > >SELECT '0:00:00'::time + '02:10:06'::timespan; > > > > > > > >Mark > > > > > > > >"Brian C. Doyle" wrote: > > > > > > > > > > Hello all, > > > > > > > > > > I have a query result of @ 2 hours 10 mins 6 secs and I would like to > > > > > change that to 02:10:06. Currently the field is listed as > > "timespan" This > > > > > allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 > > min 6 sec > > > > > which are all the formats that I will be entering the time formats! > > How do > > > > > I convert it into a the format of 02:10:06 > > > > > > > > > > Thanks to you all
Re: [SQL] tip: weird parse error for pl/pgsql
Keith Wong <[EMAIL PROTECTED]> writes:
> It seems that pl/pgsql has a problem parsing Window style
> new line characters.
Ah-hah, good catch! I have fixed this bug for 7.1. If you want to
patch your local copy, the critical changes are:
*** src/pl/plpgsql/src/scan.l.orig Thu Jun 22 19:08:34 2000
--- src/pl/plpgsql/src/scan.l Tue Aug 22 10:59:28 2000
***
*** 143,155
* Ignore whitespaces but remember this happened
* --
*/
! [ \t\n]+ { plpgsql_SpaceScanned = 1; }
/* --
* Eat up comments
* --
*/
! --[^\n]* ;
\/\* { start_lineno = yylineno;
BEGIN IN_COMMENT;
}
--- 146,158
* Ignore whitespaces but remember this happened
* --
*/
! [ \t\r\n]+{ plpgsql_SpaceScanned = 1; }
/* --
* Eat up comments
* --
*/
! --[^\r\n]*;
\/\* { start_lineno = yylineno;
BEGIN IN_COMMENT;
}
regards, tom lane
Re: [SQL] Continuous inserts...
Wierd, I've not seen that behavior really, although I've never done time sensitive stuff. It might be the time before the shared cache updates? Not sure really. If you do the rule inline with your inserts (rather than a second transaction) does it still wait? Stephan Szabo [EMAIL PROTECTED] On Tue, 22 Aug 2000, Joerg Hessdoerfer wrote: > Hi! > > At 08:18 18.08.00 -0700, you wrote: > [...] > > >I didn't try with vacuum, I just did a table lock and that > >seemed to still hang the inserts with two tables, so I figured > >maximum safety was adding the third table. If it works with two > >that's much cooler. Was this with real data or just a small test > >set? > > It was a test set ... ~2 records, *BUT* I found that postgres > decides when it starts to use the rule - means, if you do continous > inserts on the table and create the rule, there's a varying time until > the rule applies. In my first tests, I re-connected the DB very often, > and the the change seemed immediate. > > Any ideas on how to 'promote' the rules faster?!? >
[SQL] Null function parameters
Hi All, I am trying to create a function that takes an int as its param and insert the value into a table. The problem occurs when the value passed is NULL, the error message returned is - Execute failed ERROR: ExecAppend: Fail to add null value in not null attribute type However my understanding was that if the default value is SQL NULL then any values passed into the function that are null would be treated as 'NULL'. This doesn't seem to be the case. Chances are I am overlooking something, could any one point me in the right direction? Cheers Graham
Re: [SQL] Time Help
In Conclusion: 7.0.2 by default outputs the time span as 00:00:00 no matter what format you put in. There is no need to convert it! Thanks to Mark for all your help!! At 11:03 AM 8/22/00 -0400, Mark Volpe wrote: >SELECT '0:00:00'::time + '@ 2 hours 10 mins 6 secs'::timespan; > > ?column? >-- > 02:10:06 > >Mark > > >"Brian C. Doyle" wrote: > > > > Mark, > > > > On your 7.0 box would you do: > > > > SELECT '0:00:00'::time + '@ 2 hours 10 mins 6 secs':timespan; > > > > For me and see if it will convert it! Need to decide if the upgrade will be > > with it and if it does this then it is > > > > Thanks for your help Mark > > > > At 10:36 AM 8/22/00 -0400, you wrote: > > >I tried it on a box with postgres 6.5.3 and I got the result you did. On > > >postgres 7.0 I get 02:10:06 for both results. You should upgrade to > 7.0 - it > > >is _much_ better. > > > > > >Mark > > > > > >"Brian C. Doyle" wrote: > > > > > > > > Mark, > > > > > > > > I tried that and had to change it to: > > > > > > > > SELECT '0:00:00'::timespan + '02:10:06'::timespan; > > > > > > > > To get any response. the response i got was > > > > > > > > @ 2 hours 10 mins 6 secs > > > > > > > > Still in the wrong format > > > > If is use : > > > > > > > > SELECT '0:00:00'::time + '02:10:06'::timespan; > > > > > > > > It get > > > > > > > > No such function 'time_timespan' with the specified attributes > > > > > > > > So i guess what I want to do is convert a timespan into time > > > > How would I do that? > > > > > > > > At 09:55 AM 8/22/00 -0400, Mark Volpe wrote: > > > > >I'm not sure at all what you are asking, but I'm thinking you're > trying to > > > > >convert a "timespan" to a "time". Try adding it to a time like this: > > > > >SELECT '0:00:00'::time + '02:10:06'::timespan; > > > > > > > > > >Mark > > > > > > > > > >"Brian C. Doyle" wrote: > > > > > > > > > > > > Hello all, > > > > > > > > > > > > I have a query result of @ 2 hours 10 mins 6 secs and I would > like to > > > > > > change that to 02:10:06. Currently the field is listed as > > > "timespan" This > > > > > > allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 > > > min 6 sec > > > > > > which are all the formats that I will be entering the time formats! > > > How do > > > > > > I convert it into a the format of 02:10:06 > > > > > > > > > > > > Thanks to you all
Re: [SQL] Null function parameters
"Graham Vickrage" <[EMAIL PROTECTED]> writes: > However my understanding was that if the default value is SQL NULL then any > values passed into the function that are null would be treated as 'NULL'. Not sure what you think you meant by that, but a null is a null. If you declared the table column as NOT NULL then Postgres is doing exactly what it should. You may wish to code the insert along the lines of INSERT INTO table VALUES (..., COALESCE($1, suitable-default), ...) COALESCE is a handy notation for "value1 unless it's NULL, in which case value2". regards, tom lane
Re: [SQL] Continuous inserts...
Stephan Szabo wrote: > Wierd, I've not seen that behavior really, although I've never > done time sensitive stuff. It might be the time before the > shared cache updates? Not sure really. If you do the rule > inline with your inserts (rather than a second transaction) > does it still wait? Just jumping in not having followed the discussion. But... The rules applied to a table by the rewriter are taken out of the relation descriptor that is returned by heap_open() or heap_openr(). I haven't looked at the code, but pg_class only has a boolean telling if a class has rules or not. Could it be that adding more rules (or dropping just a few instead of all) doesn't update the pg_class tuple, thus the syscache for the table isn't invalidated and other backends continue to use the old information instead of rescanning pg_rewrite? Jan > > Stephan Szabo > [EMAIL PROTECTED] > > On Tue, 22 Aug 2000, Joerg Hessdoerfer wrote: > > > Hi! > > > > At 08:18 18.08.00 -0700, you wrote: > > [...] > > > > >I didn't try with vacuum, I just did a table lock and that > > >seemed to still hang the inserts with two tables, so I figured > > >maximum safety was adding the third table. If it works with two > > >that's much cooler. Was this with real data or just a small test > > >set? > > > > It was a test set ... ~2 records, *BUT* I found that postgres > > decides when it starts to use the rule - means, if you do continous > > inserts on the table and create the rule, there's a varying time until > > the rule applies. In my first tests, I re-connected the DB very often, > > and the the change seemed immediate. > > > > Any ideas on how to 'promote' the rules faster?!? > > > -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [SQL] Continuous inserts...
Jan Wieck <[EMAIL PROTECTED]> writes: > I haven't looked at the code, but pg_class only has a boolean > telling if a class has rules or not. Could it be that adding > more rules (or dropping just a few instead of all) doesn't > update the pg_class tuple, thus the syscache for the table > isn't invalidated and other backends continue to use the old > information instead of rescanning pg_rewrite? This is done correctly in current sources --- see setRelhasrulesInRelation(). However I recall having dorked with that code not long ago, and I forget what it looked like before. Perhaps 7.0.* is broken in this respect? Would think people would have noticed, though. regards, tom lane
