Re: [SQL] Add calculated fields from one table to other table
> I have two tables. Tick table has fields like ticker, time, price & volume
> and Timeseries
> table has fields like ticker, time, avg_price, avg_volume.
>
> The time field in Timeseries table is different from time in tick table,
> its the timeseries
> for every minute. Now I want to calculate the average price & volume from
> tick table for each
> ticker and for every minute and add those fields to timeseries table. Can
> anyone please help me
> out with the sql query.
>
> Note: The ticker in the tick table also has duplicate values, so i am not
> able to create
> relation between two tables.
Here is my guess how it can be done:
insert into Timeseries ( tiker, time, avg_price, avg_volume ) select ...
where select would be
selecttick,
date_trunc('minute', time) as minute,
avg(price) as avg_price,
avg(volume) as avg_volume
from ticker
where time between 'yourstartdate' and 'yourenddate'
group by tick, minute;
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] plpgsql record as parameter ???
On 10/18/06, Andy <[EMAIL PROTECTED]> wrote:
Hi, I have the following function:
CREATE OR REPLACE FUNCTION zahlavis_rech_list(int4,
varchar(10)) RETURNS "varchar" AS$BODY$DECLAREavis_id ALIAS FOR
$1;rech_type ALIAS FOR $2;rech_list text;sql text;rec
RECORD;BEGIN
rech_list := '';sql := 'SELECT '|| rech_type
||' as xx FROM rechnung WHERE id IN (SELECT id_rechnung FROM rechnung_zahlavis
WHERE id_zahlavis IN (' || avis_id || '))';FOR rec IN execute
sqlloop RAISE WARNING 'value = %', rec.xx ;
rech_list := rech_list || ',' || rec.xx;end loop;return
substr(rech_list,2); END$BODY$LANGUAGE 'plpgsql'
VOLATILE;
I want to give as a second parameter a column from
the table. It works ONLY when I run the function for the first and only with
that parameter.
For example:
select zahlavis_rech_list(1,
'nummer');
WARNING: value = 103670WARNING:
value = 103603WARNING: value = 103345WARNING: value =
103318WARNING: value = 103882WARNING: value =
103241WARNING: value = 109124
Total query runtime: 16 ms.Data retrieval
runtime: 15 ms.1 rows retrieved.
EXECUTION OK!
select zahlavis_rech_list(1, 'id');
WARNING: value = 504
ERROR: type of "rec.xx" does not match that
when preparing the planCONTEXT: PL/pgSQL function "zahlavis_rech_list"
line 14 at assignment
EXECUTION ERROR!
Both id, and nummer are columns from the table.
I tried different solutions but no result.
Help && regards,
Andy.
What do your tables look like? This is caused by a data type mismatch so I wonder if the columns nummer and id are different types.-- ==
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] Can we convert from Postgres to Oracle !!???
On Sun, Oct 22, 2006 at 12:03:38AM +0300, Devrim GUNDUZ wrote: > On Tue, 2006-10-17 at 14:21 +0530, Sandeep Kumar Jakkaraju wrote: > > Can we convert from Postgres to Oracle !!??? You can also run our software and get Oracle syntax for 1/25th the cost. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
