Re: [SQL] date_trunc for 5 minutes intervals
Hi,
Thanks for the several replies both on and off the list.
To be more specific, I am wanting to aggregate data to a 5/10/15 min
interval. Currently, I am aggregating data that falls in hour / day /
month / year periods for both count() and sum(). The sql I am currently
using is:
SELECT count(id) AS count, sum(conn_bytes) AS
sum, hisec_port, conn_protocol,
date_trunc('hour'::text, datetime) AS date_trunc
FROM trafficlogs
WHERE (conn_outbound = false)
GROUP BY date_trunc('hour'::text, datetime),
conn_protocol, hisec_port
HAVING (count(*) = ANY (
SELECT count(*) AS count
FROM trafficlogs
GROUP BY hisec_port, date_trunc('hour'::text, datetime)
ORDER BY count(*) DESC)
);
Which produces:
count sum hisec_portconn_protocol date_trunc
12192 5,050 2003/09/17 00:00:00
11176 5,050 2003/09/17 01:00:00
12192 5,050 2003/09/17 02:00:00
11176 5,050 2003/09/17 03:00:00
10160 5,050 2003/09/17 04:00:00
- if you know of a more efficient way to do this than the sql above, pls
let me know
In my previous post I should have said I wanted to aggregating data in
5/10/15 min intervals in a similar manner to the above
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: Fw: [SQL] Max input parameter for a function
Kumar, > While trying to allocate about 36 input parameters, I got an error saying > that the max input parameter for a function is only 32. > > Is it right? How to overcome this? Because I wanna insert records into a > table of 55 columns with a lot of NULL able columns. First off, if your table has 55 columns and many are nullable, then you have a database design problem ... your database is not normalized. Assuming that you can't fix your database, there's two methods you can take: 1) Rather than pushing in all of the columns as parameters, you can dump the record into a holding table (e.g. mytable_buffer) and then call a procedure to process the data there. 2) You can re-compile PostgreSQL to accept more parameters. This requires: 1. Backup your database cluster using pg_dumpall. (make sure to double-check!) 2. Shut down postgresql 3. Delete the PGDATA directory 4. Go into your postgresql source, and edit the file src/include/pg_config.h , and raise "INDEX_MAX_KEYS" to the desired level, probably 64. Warning: This may impose a minor performance penalty on Postgres! 5. Re-compile Postgres, starting with "make clean" 6. Run initdb 7. Restore your database cluster from the pg_dumpall file -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] how to create a multi columns return function ?
Hi I'm moving databases from sybase to postgres. But I have difficulties in creating a postgres equivalent to the sybase stored procedures... Apparently, Postgres functions should work, but the syb stored procedures get only one parameter and return several colums Here's the code I wrote in postgresql : create function function_name( int ) returns text AS ' SELECT column1, column2, column3,...,column15 FROM table_name WHERE colum1 = $1 AND column5 = \'specific value\' AND column8 = \'specific_value2 \' ' LANGUAGE 'SQL'; and I get the message error : returns multi columns I'm wondering too if It's possible to create a view with a parameter if functions don't work. Has anybody faced the same problem ? I need help thanks
[SQL] plpgsql related question: intervals and variables
Hello out there, We have a problem in plpgsql: We want to add variable periods (result of a query) to a timestamp. Our Code looks like this: Begin heute := ''today''; Select Into vk ourcolumn From table where other = foo; If vk > 0 Then vk_txt := ''Vorkuehlung notwendig''; ez := heute + interval ''vk days''; Else vk_txt := ''Keine Vorkuehlung vorgeschrieben''; End if; We get the following: ERROR: Bad interval external representation 'vk days' The variable 'heute' is declared as timestamp, 'vk' as integer! What have we done wrong?? :( Thanks in advance, Willi, Albin -- = Wilhelm Graiss Altirdning 12 8952 Irdning 03682/22451/267 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] function problem
Can anybody tell me why the following code when activated by a select only affects the first line of the table??? create or replace function increase(integer) returns void as 'update tab set price=price*(1+$1/100.0)' language sql; Thanks. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Writing the SQL queries inside Functions and operators
Hi Friends, What is the use of user defined operators, as functions also perform the same job as an operator thanks in advance ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] how to create a multi columns return function ?
Take a look at the section on 'SQL Functions Returning Sets' http://www.postgresql.org/docs/7.3/static/xfunc-sql.html#AEN31304 > Hi > > I'm moving databases from sybase to postgres. > But I have difficulties in creating a postgres equivalent to the sybase stored > procedures... > > Apparently, Postgres functions should work, but the syb stored procedures get > only one parameter and return several colums > > Here's the code I wrote in postgresql : > > create function function_name( int ) returns text > AS ' SELECT column1, column2, column3,...,column15 > FROM table_name > WHERE colum1 = $1 AND column5 = \'specific value\' AND column8 = > \'specific_value2 \' ' > LANGUAGE 'SQL'; > > and I get the message error : returns multi columns > > I'm wondering too if It's possible to create a view with a parameter if > functions don't work. > > Has anybody faced the same problem ? > > I need help > > thanks -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(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] plpgsql related question: intervals and variables
Willhelm, > Begin > >heute := ''today''; > Select Into vk ourcolumn From table where other = foo; >If vk > 0 Then > vk_txt := ''Vorkuehlung notwendig''; > ez := heute + interval ''vk days''; PL/pgSQL handles variable like SQL, not like PHP or Perl. You can't do a variable substitution inside quotes, and you need to cast: ez := heute + interval (cast(vk as text) || '' days''); Also, the string 'today' has no special meaning in PL/pgSQL. I think you want now() instead. I'm afraid that you're going to need a tutorial on SQL datatypes, casting, and similar issues ... I wish I had one to recommend to you. Just keep in mind that SQL scripting languages (like PL/pgSQL) are not Perl! -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] plpgsql related question: intervals and variables
On Tuesday 21 October 2003 14:58, Wilhelm Graiss wrote: >heute := ''today''; > Select Into vk ourcolumn From table where other = foo; >If vk > 0 Then > vk_txt := ''Vorkuehlung notwendig''; > ez := heute + interval ''vk days''; > The variable 'heute' is declared as timestamp, > 'vk' as integer! > > What have we done wrong?? Quoted the vk variable. You want something like: ez := heute + (vk || '' days'')::interval; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] how to create a multi columns return function ?
On Tuesday 21 October 2003 14:08, [EMAIL PROTECTED] wrote: > Hi > > I'm moving databases from sybase to postgres. > But I have difficulties in creating a postgres equivalent to the sybase > stored procedures... > > Apparently, Postgres functions should work, but the syb stored procedures > get only one parameter and return several colums > > Here's the code I wrote in postgresql : > > create function function_name( int ) returns text > AS ' SELECT column1, column2, column3,...,column15 You've said it's returning "text" whereas it's returning whatever your columns are. You'll want to do something like: CREATE TYPE fn_ret_type AS ( column1 int4, column2 text, column3 date, ... ); CREATE FUNCTION function_name(int) RETURNS fn_ret_type ... If it returns multiple rows you want SETOF fn_ret_type -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Writing the SQL queries inside Functions and operators
On Tuesday 21 October 2003 08:34, Dharan wrote:
> Hi Friends,
>
>
> What is the use of user defined operators, as functions also perform
> the same job as an operator
Easier to write. Compare:
'hello ' || ' there ' || 'everyone'
with
text_concat('hello', text_concat('there', text_concat('everyone') ) )
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] function problem
"geraldo" <[EMAIL PROTECTED]> writes: > Can anybody tell me why the following code when activated > by a select only affects the first line of the table??? > create or replace function increase(integer) > returns void as 'update tab set price=price*(1+$1/100.0)' > language sql; Works fine here. regression=# create table tab (price numeric); CREATE TABLE regression=# insert into tab values(1); INSERT 154584 1 regression=# insert into tab values(10); INSERT 154585 1 regression=# insert into tab values(100); INSERT 154586 1 regression=# select * from tab; price --- 1 10 100 (3 rows) regression=# create or replace function increase(integer) regression-# returns void as 'update tab set price=price*(1+$1/100.0)' regression-# language sql; CREATE FUNCTION regression=# select increase(42); increase -- (1 row) regression=# select * from tab; price -- 1.4200 14.2000 142. (3 rows) regression=# regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] [postgres] Re: Deutsche PostgreSQL-Mailingliste unter postgresql.org
OK, da sich eine Pluralität dafür ausgesprochen hat, und Marc die Liste sowieso schon angelegt hatte, ist [EMAIL PROTECTED] hiermit eröffnet. (Ja, der Name klingt vielleicht ein bisschen blöd, aber so bleibt das alles einheitlich.) Zum Eintragen ist es dann glaub ich "subscribe" an [EMAIL PROTECTED] senden. Peter Eisentraut writes: > Hallo Allerseits, > > ich habe mit Marc Fournier vereinbart, dass wir eine deutsche > PostgreSQL-Mailingliste unter postgresql.org anlegen können. Ich denke, > das würde der Einheitlichkeit des Auftretens entgegen kommen, zumal es > auch schon eine französische und eine türkische gibt. > > Was haltet ihr also davon, die ganze Operation dorthin zu verlegen? > > -- Peter Eisentraut [EMAIL PROTECTED] Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] plpgsql related question: intervals and variables
On Tue, 21 Oct 2003, Josh Berkus wrote: > >heute := ''today''; > > Select Into vk ourcolumn From table where other = foo; > >If vk > 0 Then > > vk_txt := ''Vorkuehlung notwendig''; > > ez := heute + interval ''vk days''; > > PL/pgSQL handles variable like SQL, not like PHP or Perl. You can't do a > variable substitution inside quotes, and you need to cast: > > ez := heute + interval (cast(vk as text) || '' days''); I think that something like ez := heute + vk * interval '1 day'; might be better in general. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] [GENERAL] Alias-Error
Ok. The exact error message is as follows: SQLException Time : Mon Oct 20 13:15:25 CEST 2003 SQLException ErrorCode : 0 SQLException SQLState : null SQLException Message : ERROR: Relation "c2" does not exist SQLException Connection: 4878867 The query is something like this: SELECT DISTINCT C2.cmc_mchap, C2.cmc_sort FROM sis_cmca, sis_cmca C2 WHERE cm_status != 'U' AND sis_cmca.cmc_name='INTERN2000' AND C2.cmc_name='INTERN2000' AND sis_cmca.cmc_mchap=C2.cmc_mchap Tom Lane wrote: > > Jost Richstein <[EMAIL PROTECTED]> writes: > > I am running a query with alias (a self join) against > > version 7.3.4 on Suse Linux 7.3 and on FreeBSD (v5?). > > It runs fine on Linux, but produces an error on > > FreeBSD: "unknown alias C2". > > The string "unknown alias" appears nowhere in the 7.3 sources. > Are you sure you are talking to a Postgres database? > > regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] [GENERAL] Alias-Error
On Tuesday 21 October 2003 08:17, Jost Richstein wrote: > Ok. The exact error message is as follows: > > SQLException Time : Mon Oct 20 13:15:25 CEST 2003 > SQLException ErrorCode : 0 > SQLException SQLState : null > SQLException Message : ERROR: Relation "c2" does not exist > SQLException Connection: 4878867 > > The query is something like this: > > SELECT DISTINCT C2.cmc_mchap, C2.cmc_sort > FROM sis_cmca, sis_cmca C2 > WHERE cm_status != 'U' AND sis_cmca.cmc_name='INTERN2000' AND ^ You're missing a table name here. It could be PG is reporting the wrong error. > C2.cmc_name='INTERN2000' AND sis_cmca.cmc_mchap=C2.cmc_mchap -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
