[SQL] Problem by inserting rows in a table via function
Hello all, I use postgresql 7.0.2 as backend, ODBC Driver 7.1.003 and MsAccess97 as frontend. Now I have the following Problem and hope, someone can help me. I developed a function in plpgsql, which inserts rows in a table *price*. This function is called from VBA-Code and everything seemed to be OK, but then I realized, that I can't change these rows inserted by the function. When I put in rows by the normal way as linked table, I can change these rows. Where is the difference to insert rows by a function or per hand. Has anyone an idea, please let me know. Thanks in advance Irina E-Mail: [EMAIL PROTECTED]
Re: [SQL] Extracting date from epoche
"Richard Huxton" <[EMAIL PROTECTED]> writes:
> Tom - I thought 'epoch'::timestamp should work too - good reason, or just
> One Of Those Things (tm)?
It works --- it's a symbolic value, though.
regression=# select 'epoch'::timestamp;
?column?
--
epoch
(1 row)
regression=# select 'epoch'::timestamp + ('1 day'::interval);
?column?
1970-01-01 19:00:00-05
(1 row)
Note the epoch is midnight GMT = 7pm local time here.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Correct syntex for implicit curor in for loops
Hi All, Could someone please tell me the Correct syntex for implicit curor in FOR loops Thank you in advance. -- Najm Hashmi Tel:514-271-9791 www.mondo-live.com www.flipr.com
[SQL] Changing PL/pgSQL triggers
Hi, What's the easiest way to modify or view a function written in PL/pgSQL? I've been using pg_dump to get the original function, then dropping and creating the function and trigger after making a change. Is there an easier way? -James
Re: [SQL] Changing PL/pgSQL triggers
On Thu, Jun 21, 2001 at 01:30:39PM -0400, James Orr wrote: > Hi, > > What's the easiest way to modify or view a function written in PL/pgSQL? I've been >using pg_dump to get the original function, then dropping and creating the function >and trigger after making a change. Is there an easier way? pgAcess should make it easier. It lets you look at the definition and lets you modify/drop/recreate it. If you're on windows, you might want to look a pgAdmin (available at greatbridge.org). -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer *> -| <- Tribble Archery ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [SQL] Select most recent record?
Marc, Did you ever get your problem solved to your satisfaction? We have a very simular problem with a historical database containing typically 5K id and updates to data every few seconds. We tried unsuccessfully to optimize queries such as those already suggested to you. We found the best means to quickly query the data valid at any given time was to: 1. Have a TIME and ENDTIME column in your table. The ENDTIME column held when the data became invalid. The initial value for the ENDTIME column was 'infinity'. 2. Have an INSERT rule that set the ENDTIME of all previous records with same ID to TIME. Here is the rule: CREATE RULE d_people_ON_INSERT AS ON INSERT TO d_people DO UPDATE d_people SET endtime = new.time WHERE ( id = new.id ) AND ( endtime = 'infinity' ) ; 3. Selects for any given time are then like the ones below and very fast. /* For time '2000-11-20 15:56' */ SELECT * FROM d_people WHERE ( time <= '2000-11-20 15:56' ) AND ( endtime > '2000-11-20 15:56' ); /* For latest time */ SELECT * FROM d_people WHERE ( time <= now()) AND ( endtime > now()); Granted, INSERTs take a little longer since they trigger an UPDATE. But optimized indices help greatly with this. I highly recommend the following book on the problems and solutions of temporal data in databases written by the man who is defining the temporal functionalities of SQL3. Richard Snodgrass, "Developing Time-Oriented Database Applications in SQL" http://www.amazon.com/exec/obidos/ASIN/1558604367/qid=993149249/sr=1-4/ref=s c_b_4/103-3746626-6461410 I hope this helps. It may be overkill, depending on the type and quantity of your data. Thanks, Mark Hamby > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Marc Sherman > Sent: Wednesday, May 16, 2001 6:28 AM > To: pgsql-sql List > Subject: [SQL] Select most recent record? > > > Hi, I was hoping I could get some help with a select statement. > > I have a log table with three columns: id int4, timestamp datetime, > value int4. > > For any given ID, there will be a large number of rows, with > different timestamps and values. > > I'd like to select the newest (max(timestamp)) row for each id, > before a given cutoff date; is this possible? > > The best I've been able to come up with is the rather ugly (and > very slow): > > select * from log as l1 where timestamp in > (select max(timestamp) from log where id=l1.id and > timestamp<'2001-01-01' group by id); > > There must be a better way to do this; any tips? > > Thanks, > - Marc > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [SQL] Select most recent record?
From: Mark Hamby [mailto:[EMAIL PROTECTED]] > > Marc, > Did you ever get your problem solved to your satisfaction? > We have a very simular problem with a historical database > containing typically 5K id and updates to data every few > seconds. We tried unsuccessfully to optimize queries > such as those already suggested to you. We found the best > means to quickly query the data valid at any given time was > to: [snip] By adding an index, I was able to get the performance of the query within a reasonable threshold; it's still slow, but acceptable. I'll bear your suggestion in mind for next time I run into this kind of problem, though, if my sol'n can't handle the data. Thanks, - Marc ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] case, new column not found
On Thu, 21 Jun 2001, [iso-8859-1] Martín Marqués wrote: > I'm trying somethings here and I get strange errors: > > select *,( > (CASE WHEN titulo LIKE '%Matematica%' THEN 1 ELSE 0 END) + > (CASE WHEN descripcion LIKE '%Matematica%' THEN 1 ELSE 0 END) + > (CASE WHEN incumbencia LIKE '%Matematica%' THEN 1 ELSE 0 END) ) > AS encontrados > FROM admin_view > WHERE admin_view.nivel=1 AND encontrados > 0; > > ERROR: Attribute 'encontrados' not found > > Why is it? encontrados should be an attribute of type INT with the count of > the rows found. IIRC, unless "encontrados" is both a pre-existing column of admin_view and the name you're using in the select, it's only an item in a select list. It's not an attribute and since select list processing happens after where clause processing (since you need to know which rows to do it to -- imagine expensive computation in select list -- you only want it to occur on returned rows) it isn't visible to the where clause. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] case, new column not found
=?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= <[EMAIL PROTECTED]> writes: > select *,( > (CASE WHEN titulo LIKE '%Matematica%' THEN 1 ELSE 0 END) + > (CASE WHEN descripcion LIKE '%Matematica%' THEN 1 ELSE 0 END) + > (CASE WHEN incumbencia LIKE '%Matematica%' THEN 1 ELSE 0 END) ) > AS encontrados > FROM admin_view > WHERE admin_view.nivel=1 AND encontrados > 0; > ERROR: Attribute 'encontrados' not found > Why is it? encontrados should be an attribute of type INT with the count of > the rows found. No it shouldn't. Items in the select list are not attributes. Since the WHERE phase logically precedes evaluation of the SELECT output list, you can hardly expect to be able to use SELECT outputs in WHERE. You could work around this with a nested sub-SELECT, viz select * from (select *, (CASE ...) AS encontrados FROM admin_view) subsel WHERE subsel.nivel=1 AND subsel.encontrados > 0; at a possible penalty in performance. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Correct syntex for implicit curor in for loops
Jan Wieck <[EMAIL PROTECTED]> writes: >> Is there a tentative timeline for 7.2? > With our last closeness of the real release date vs. the > first estimations, and we don't even have a proposal up to > now - well, maybe this or next year is the most precise I can > tell you ;-} The problem last time was we'd decided that 7.1 would have WAL, and WAL took forever, and we weren't in a position to back off and make a release without it. This cycle we don't have any comparable huge must-do projects. I'm inclined to plan a 7.2 release cycle for late summer and release with whatever's done by then. Anything that's not ready will just have to wait for the next bus. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
