Re: [SQL] accessing multiple database
mohammad izwan ibrahim wrote: hi all, im finding some ideas, method, advice, n etc on accessing multiple database.The system that i'm currently develop is real time update and the data reside on few different db at different db site. Tq in advance. Obviously you can access more than one database from your client software. From within PostgreSQL itself, look into the dblink package in the contrib/ directory of the source distribution. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] HELP: aggregating insert rule for multirow inserts.
What about this as a rule. It seems to work for me.
create or replace rule critter_counter as
on INSERT to critter do (
insert into zoostats
select distinct new.legs
where new.legs not in (select legs from zoostats);
update zoostats set headcount = (
select count(*)
from critter
where critter.legs = zoostats.legs
) where zoostats.legs = new.legs
);
===
OUTPUT (from entire script)
===
CREATE TABLE
psql:pglist-problem.1.sql:4: NOTICE: CREATE TABLE / PRIMARY KEY will
create implicit index 'zoostats_pkey' for table 'zoostats'
CREATE TABLE
CREATE RULE
INSERT 17347 1
INSERT 17349 1
INSERT 17351 1
INSERT 17353 1
name | legs
--+--
cat |4
starfish |5
ant |6
dog |4
(4 rows)
legs | headcount
--+---
5 | 1
6 | 1
4 | 2
(3 rows)
INSERT 0 4
legs | headcount
--+---
4 | 4
5 | 2
6 | 2
(3 rows)
psql:pglist-problem.1.sql:32: NOTICE: Drop cascades to rule
critter_counter on table critter
DROP TABLE
DROP TABLE
=
End OUTPUT
=
(oops forgot to send to list)
--Mike
On 5/4/05, Mischa Sandberg <[EMAIL PROTECTED]> wrote:
> I'm having a problem with the use of the NEW rowset,
> in a rule intended to aggregate across inserts.
>
> I've never really grokked how NEW and OLD really work,
> syntactically, other than that they seem to be implicit
> in every top-level FROM clause, and any mention elsewhere
> gets an error: '42P01: relation "*NEW*" does not exist'.
>
> I've tried different flavours of the UPDATE command,
> in the following rule, and they either produce syntax errors
> or the wrong results.
>
> Any suggestions much appreciated ...
>
> == CODE
> "How many critters are in the zoo, of the 4,5,6...-legged varieties?"
>
> create table critter(name text, legs int);
> create table zoostats(legs int, headcount int default 0,
> primary key(legs));
>
> create or replace rule critter_counter as
> on INSERT to critter do (
>
> insert into zoostats
> select distinct new.legs
> where new.legs not in (select legs from zoostats);
>
> update zoostats
> setheadcount = headcount + (select count(*)) -- "from new"
> where new.legs = zoostats.legs
> );
>
> insert into critter values('cat',4);
> insert into critter values('starfish',5);
> insert into critter values('ant',6);
> insert into critter values('dog',4);
>
> insert into critter select * from critter; -- double everything.
>
> select * from zoostats;
>
> drop table zoostats cascade;
> drop table critter;
> == EXPECTED OUTPUT
> legs headcount
> -
>4 4
>5 2
>6 2
> == ACTUAL OUTPUT
> legs headcount
> -
>4 3 -- !?
>5 2
>6 2
> == OTHER ATTEMPT:
> This version of the update looks syntactically right to me,
> but makes CREATE RULE fail on a syntax error:
>
> ...
>
> update zoostats
> set headcount = headcount + tally
> from (select new.legs, count(new.legs) as tally -- from new !?
> group by new.legs) as poll
> where poll.legs = zoostats.legs;
>
> ERROR: 'Subquery in FROM may not refer to other relations
> of same query level'.
> --
> Engineers think equations approximate reality.
> Physicists think reality approximates the equations.
> Mathematicians never make the connection.
>
> ---(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
>
--
Time is my Nemesis!
But, if I stopped time for one year,
how would I know when that year was finished?...
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] accessing multiple database
Hi Richard Huxton, Tq for your advice, Hope you can help me on this 1.can I create view from dblink query 2.can I fetch the data simultaneous from multiple database using dblink function, how the syntax would be? tq -- Original message -- From: "Richard Huxton" To: "mohammad izwan ibrahim" <[EMAIL PROTECTED]> Date: Thursday 5 May 2005 15:06:24 Subject: Re: [SQL] accessing multiple database > mohammad izwan ibrahim wrote: > > hi all, > > > > im finding some ideas, method, advice, n etc on accessing multiple > > database.The system that i'm currently develop is real time update > > and the data reside on few different db at different db site. Tq in > > advance. > > Obviously you can access more than one database from your client > software. From within PostgreSQL itself, look into the dblink package in > the contrib/ directory of the source distribution. > > -- >Richard Huxton >Archonet Ltd > ---(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] accessing multiple database
mohammad izwan ibrahim wrote: Hi Richard Huxton, Tq for your advice, Hope you can help me on this 1.can I create view from dblink query I believe so - see the dblink docs for details. 2.can I fetch the data simultaneous from multiple database using dblink function, how the syntax would be? Not in one function-call, but I don't see why you couldn't have several sub-queries, each to a different database. I recommend testing it to see. -- Richard Huxton Archonet Ltd ---(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
[SQL] Select of a function that returns a array
Hi guys
I'm trying to figure out a way to make this select work even for a wrong value:
select mean_and_variancepop('golf', 'temperature');
or
select CAST (mean_and_variancepop('golf', 'temperature') AS float(2));
This function return an array
the function is:
CREATE or replace FUNCTION mean_and_variancepop(var_table varchar,
var_col varchar) RETURNS float(2) AS $$
DECLARE
curs refcursor;
a float;
b float;
c float;
retvals float[2];
sum float:=0;
BEGIN
open curs FOR EXECUTE 'SELECT ' || quote_ident(var_col) || ' FROM
' || quote_ident(var_table);
b:=0;
LOOP
-- some computations
FETCH curs INTO a;
IF NOT FOUND THEN
EXIT; -- exit loop
END IF;
b:=b+1;
c:=a;
sum:= sum + c;
END LOOP;
CLOSE curs;
retvals[0]:= sum / b;
retvals[1] :=4;
RETURN retvals;
END;
$$ LANGUAGE plpgsql;
Thanks
-Jr
---(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] multi-column unique constraints with nullable columns
It may be possible to bypass the NULL != NULL by using coalesce. Here is an example: create table foo2 ( a integer not null, b integer not null, c integer null, UNIQUE (a,b,c) ); create function foo2_unique_func() RETURNS trigger AS ' DECLARE isfound integer = 0; BEGIN isfound = (select count(*) from foo2 where (new.a,new.b,coalesce(new.c::TEXT,''EmPtY'')) in (select a,b,coalesce(c::TEXT,''EmPtY'') from foo2)); RAISE NOTICE ''isfound: %'', isfound; IF isfound > 0 THEN RAISE EXCEPTION ''Columns a,b,c Must Be Unique values (%,%,%)'', new.a, new.b, new.c; ELSE RETURN NEW; END IF; END; ' language 'plpgsql'; CREATE TRIGGER foo2_unique BEFORE INSERT OR UPDATE ON foo2 FOR EACH ROW EXECUTE PROCEDURE foo2_unique_func(); insert into foo2 values (1,300, null); insert into foo2 values (1,300, null); select * from foo2; select * from foo2 where (1,300,coalesce(null::TEXT,'EmPtY')) in (select a,b,coalesce(c::TEXT,'EmPtY') from foo2); drop table foo2 cascade; drop function foo2_unique_func() cascade; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
