Re: [SQL] Group by 15 Minute Steps
# Usally, the steps between each entries is 300 seconds. How can I # group by by 15, 30, 45 minutes so that i can get averages over the # specified timeframe? For 15-minute data, I'd compute the "quadrant" of each record and group by the quadrant number. Anything that occurs from :00 to :14 is in quadrant zero, :15 - :29 is quadrant 1, etc., yielding quadrants 0-3. mydb> SELECT (time / (15 * 60)) AS quadrant, SUM(ifinoctets) FROM mytable GROUP BY quadrant ORDER BY quadrant; Divide the time by 60 to get minutes and 15 to get quadrants. You can see how to extend this for other intervals. -- Jonathan Daugherty Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] tablename as function parameter
# I'm trying to create a function (language 'sql') with table name as
# single input parameter, but I always get syntax errors at
# "$1". Probably I am use wrong types.
#
# simple example:
#
# create function testfct (text) language sql as 'select count(*) from
# $1'
This doesn't work because the value of $1 isn't literally substituted
into the SQL function that you've created; it's treated as a value
token that can only be used in certain contexts.
If you want to select records from an arbitrary table, you can use the
table name parameter to build and execute a cursor in plpgsql. In my
experience, return values can't be quite so polymorphic without a lot
of pain and suffering but, then again, using SELECT * FROM $table
inside a function and expecting to return all of the results is
probably not something you'll need very often.
But here's a way, to be didactic:
CREATE OR REPLACE FUNCTION test(text) RETURNS SETOF record AS '
DECLARE
_table ALIAS FOR $1;
_mycursor refcursor;
_row record;
BEGIN
OPEN _mycursor FOR EXECUTE ''SELECT * FROM '' || _table;
FETCH _mycursor INTO _row;
WHILE FOUND LOOP
RETURN NEXT _row;
FETCH _mycursor INTO _row;
END LOOP;
RETURN;
END
' LANGUAGE plpgsql;
If you return SETOF RECORD, you'll need to be explicit about how the
return value is treated, depending on what you expect to get back from
the function:
mydb> SELECT * FROM test('mytable') AS (col1 integer, col2 text, col3
date);
--
Jonathan Daugherty
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] C function extending postgres
# ERROR: could not load library "/usr/lib/pgsql/processinfo.so": # /usr/lib/pgsql/processinfo.so: undefined symbol: proc_cpu_idle My guess that you're not linking against the library that containts 'proc_cpu_idle' when you build your .so. What's the build platform? -- Jonathan Daugherty Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] C function extending postgres
# I'm actually trying to use a system metrics library from # http://www-usr.inf.ufsm.br/~veiga/liblproc/index-en.html Have you added the path of the lproc library to your /etc/ld.so.conf? -- Jonathan Daugherty http://www.parsed.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
