Re: [SQL] Looking for a way to sum integer arrays....
You would definitely want to look into using pl/R for this. Also, other
procedure languages (perl, for example) work well with arrays so may be
easier to use for this situation. As for the aggregate, I don't know how to
make that more dynamic in terms of return value.
Sean
- Original Message -
From: "Tony Wasson" <[EMAIL PROTECTED]>
To:
Sent: Thursday, April 21, 2005 9:21 PM
Subject: [SQL] Looking for a way to sum integer arrays
I'd like to be able to sum up an integer array. Like so:
{3,2,1}
+ {0,2,2}
---
{3,4,3}
The following solution I've been hacking on works, although I think it
is far from "ideal". Is there a built in way to sum up arrays? If not,
is there a better way than my crude method? I have tested this on 7.4
and 8.0. I'd also be appreciate if any insight on why my aggregate
fails to work when I have an empty initcondition. P.S. I have never
written an aggregate and I was lost trying to follow the complex_sum
example in the docs.
-
CREATE OR REPLACE FUNCTION sum_intarray(INTEGER[],INTEGER[]) RETURNS
INTEGER[] LANGUAGE 'plpgsql' AS '
/*
|| Author: Tony Wasson
||
|| Overview: Experiment with arrays and aggregates
|| 3,2,1
||+ 0,2,2
|| ---
|| 3,4,3
||
|| Revisions: (when, who, what)
|| 2005/04/21 -- TW - Create function
*/
DECLARE
inta1 ALIAS FOR $1;
inta2 ALIAS FOR $2;
out_arr INTEGER[];
out_arr_textTEXT := ;
i INTEGER;
nextnum INTEGER;
BEGIN
FOR i IN array_lower(inta1, 1)..array_upper(inta1, 1)
LOOP
RAISE NOTICE ''looking at element %'',i;
nextnum := COALESCE(inta1[i],0) + COALESCE(inta2[i],0);
RAISE NOTICE ''nextnum %'',nextnum;
out_arr_text := out_arr_text || nextnum::TEXT || '','';
RAISE NOTICE ''text %'',out_arr_text;
END LOOP;
RAISE NOTICE ''text %'',out_arr_text;
--drop the last comma
IF SUBSTRING(out_arr_text,length(out_arr_text),1) = '','' THEN
out_arr_text := substring(out_arr_text,1,length(out_arr_text)-1);
END IF;
out_arr_text := ''{'' || out_arr_text || ''}'';
RAISE NOTICE ''text %'',out_arr_text;
out_arr := out_arr_text;
RAISE NOTICE ''out_arr %'',out_arr;
RETURN out_arr;
END
';
SELECT sum_intarray('{1,2}','{2,3}');
SELECT sum_intarray('{3,2,1}','{0,2,2}');
--- Now I make a table to demonstrate an aggregate on
CREATE TABLE arraytest (
id character varying(10) NOT NULL,
somearr integer[]
);
INSERT INTO arraytest (id, somearr) VALUES ('a', '{1,2,3}');
INSERT INTO arraytest (id, somearr) VALUES ('b', '{0,1,2}');
CREATE AGGREGATE sum_integer_array (
sfunc = sum_intarray,
basetype = INTEGER[],
stype = INTEGER[],
initcond =
'{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}'
);
--
# SELECT sum_integer_array(somearr) FROM arraytest;
sum_integer_array
-
{1,3,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}Thanks
in advance to anyone who reads this far.Tony [EMAIL PROTECTED](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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [despammed] [SQL] Insert psql commands inside a script
Andreas Kretschmer wrote: am 21.04.2005, um 17:19:23 -0500 mailte Letnes, David G. folgendes: I have used the psql -f /tmp/SelectCommands.sql before, but now I want to put the sql statement right in the shell script. I haven't had any luck. Is there a command I can use that will not point to a file for the sql instructions but right on the same line. I use very short psql commands and would like to do it all with 1 file. echo "select bla from fasel" | psql -U database You can also use here documents, shell-variables and so on. Or you can do the following: psql database < sqlcode where sqlcode contains your sql. -- Until later, Geoffrey ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [despammed] [SQL] Insert psql commands inside a script
On Sat, Apr 23, 2005 at 08:15:48AM -0400, Geoffrey wrote: > > Or you can do the following: > > psql database < sqlcode > > where sqlcode contains your sql. That command redirects the standard input from the file named sqlcode; the requirement says NOT to use an external file. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [despammed] [SQL] Insert psql commands inside a script
Michael Fuhr wrote: On Sat, Apr 23, 2005 at 08:15:48AM -0400, Geoffrey wrote: Or you can do the following: psql database < sqlcode where sqlcode contains your sql. That command redirects the standard input from the file named sqlcode; the requirement says NOT to use an external file. My bad, I misread the post as indicating he wanted to use an external file. Too early on a Saturday with a late Friday and not enough coffee yet... -- Until later, Geoffrey ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] weird SQL statement question
Ok, so I installed the latest version of Postresql (8.02) on
a WinXP SP2 machine..
My question is why won't the statement SELECT * FROM table
work, whereas the statement SELECT * FROM »table« works!
And, to explain a bit more, here's another example:
INSERT INTO TestTable (Name, Surname) VALUES ('name',
'surname'); .. this DOESN'T work
INSERT INTO »TestTable« (»Name«, »Surname«) VALUES ('name',
'surname'); .. this DOES work
Any ideas? J
Regards,
Tadej
Re: [SQL] can a function return a virtual table?
Kai, > It would be much nicer to have to write something like: SELECT xyz, abc > FROM active(tablex); where the function 'active(x)' returns a virtual > table with all entries from table x where status is > -1. But sadly I > have no idea how write such a function. Good old O'reilly can't help (or > i'm to dumb *g*). http://techdocs.postgresql.org/guides/SetReturningFunctions Beware, though, that query plan estimation for SRFs is less accurate than for regular subqueries, so you could end up with unnecessarily slow query execution. Test! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] weird SQL statement question
At 01:55 PM 4/23/05, Tadej Kanizar wrote:
Ok, so I installed the latest version of Postresql (8.02) on a WinXP SP2
machine..
My question is why won't the statement SELECT * FROM table work, whereas
the statement SELECT * FROM »table« works!
And, to explain a bit more, here's another example:
INSERT INTO TestTable (Name, Surname) VALUES ('name', 'surname'); .. this
DOESN'T work
INSERT INTO »TestTable« (»Name«, »Surname«) VALUES ('name', 'surname'); ..
this DOES work
If you create a table using quotes and mixed-case, you will need quotes
every time you reference the table. Don't use quotes when you create the
table and you won't need them when you acces the table with other commands.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to select from many database ??
Oops! [EMAIL PROTECTED] (Art - Feping) was seen spray-painting on a wall: > i want to select from many databases, for example , i want to select table > master in database A, and table master in database B. > can i do it like this : " select * from A.Master, B.Master " just like > in SQL Server ?? > or can somebody help me how to select many database in postgresSQL ?? Thank > u There is a module called "dblink" in the contrib area that can do this. You obviously don't get to maintain all the "goodies" of integrity constraints and MVCC 'synchronization' of data. If you find you have applications where you are accessing multiple databases like this, you should consider whether or not they could perhaps be joined into one database by putting the data into several namespaces or schemas. That _may_ not be possible, but it's worth investigating, as it is likely to be _way_ more efficient... -- let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;; http://linuxfinances.info/info/lsf.html PASCAL is not a language. It was an experiment combining the flexibilty of C with that of a drug-crazed penguin. It is also the 'language' of choice of many CS professors who aren't up to handling REAL programming. Hence, it is not a language. ---(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] weird SQL statement question
Quoting Frank Bax <[EMAIL PROTECTED]>: > At 01:55 PM 4/23/05, Tadej Kanizar wrote: > > >Ok, so I installed the latest version of Postresql (8.02) on a WinXP > SP2 > >machine.. > > > >My question is why won't the statement SELECT * FROM table work, > whereas > >the statement SELECT * FROM »table« works! > If you create a table using quotes and mixed-case, you will need > quotes > every time you reference the table. Don't use quotes when you create > the > table and you won't need them when you acces the table with other > commands. I don't suppose that the original poster actually named the table "table"? In which case, being a keyword, it will ALWAYS need quotes. "Dreams come true, not free." -- S.Sondheim, ITW ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
