[SQL] Accessing objects over db-borders
Hi, Consider one postmaster that manages multiple databases (logical names) Is it possible (and how) to access a stored procedure or view/Table which resides in DB aaa if your DB-connection has currently the context on DB bbb? So: I login on db bbb as a user who has rights on both DBs (aaa,bbb). Can I do a select on a table of DB aaa? If this is possible I suspect referential integrity not to work, does it? Thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [despammed] [SQL] Accessing objects over db-borders
am 04.02.2005, um 12:00:17 +0100 mailte KÖPFERL Robert folgendes: > Hi, > > Consider one postmaster that manages multiple databases (logical names) > > Is it possible (and how) to access a stored procedure or view/Table which > resides in DB aaa if your DB-connection has currently the context on DB bbb? Yes, take a look at dblink (contrib-package) Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] getting back autonumber just inserted
I don't work with M$ DBs, but saw that "autonumber" is an M$ concept.
Purely for my own edification, how do you get the most resent value of an
autonumber in M$? I was helping someone out who was using M$ stuff and was
amazed that there was no currval function.
Thanks,
Rick
Scott Marlowe
<[EMAIL PROTECTED]To: lorid <[EMAIL
PROTECTED]>
orks.com> cc:
pgsql-sql@postgresql.org
Sent by: Subject: Re: [SQL] getting
back autonumber just inserted
[EMAIL PROTECTED]
tgresql.org
02/03/2005 05:16 PM
On Thu, 2005-02-03 at 16:16, lorid wrote:
> I could have sworn I kept a copy of prior emails that discussed how to
> get back a value that was just inserted into a autonumber (or in
> postgresql case a sequence number)
If you know the name of the sequence the number came from you can use
currval():
insert into table1 (info) values ('abc');
select currval('table1seq');
Assuming table1seq is the name of the sequence here.
In 8.0 there's a function to do this (I'm not sure of the name, but a
quick search of the 8.0 docs should turn it up.)
---(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 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] getting back autonumber just inserted
> I don't work with M$ DBs, but saw that "autonumber" is an M$ concept. > Purely for my own edification, how do you get the most resent > value of an autonumber in M$? I was helping someone out who > was using M$ stuff and was amazed that there was no currval function. I beleive they call it IDENTITY and not autonumber. You get it using either SELECT @@IDENTITY or SELECT SCOPE_IDENTITY() depending on if you want the very latest identity or the latest int he current scope (if you have a trigger inserting records in a different table, they will differ - @@IDENTITY will return from the table affected by the trigger, SCOPE_IDENTITY() will return it for the table *you* updated) //Magnus ---(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] Determining Rank
Michael,
That's an excellent solution, but on my table, the explain plan sucks
and the query time is over 3 minutes when implemented. Is there a
simple way to get a row_num without using a temporary sequence?
Thanks for your help.
-Don
On Fri, 4 Feb 2005 14:02:20 +0900, Michael Glaesemann
<[EMAIL PROTECTED]> wrote:
>
> On Feb 4, 2005, at 12:06, Don Drake wrote:
>
> > I have a query that shows the top N count(*)'s.
> >
> > So it's basically:
> >
> > select some_val, count(*)
> > from big_table
> > group by some_val
> > order by count(*)
> > limit 50
> >
> > Now, I would like to have the rank included in the result set. The
> > first row would be 1, followed by 2, etc. all the way to 50.
>
> There are a couple of different ways to go about this. One is just to
> append an extra column that's basically a line number, but I find it
> doesn't handle ties very elegantly. The following example uses a
> correlated subquery using HAVING to determine the rank as "the number
> of items that have a total quantity greater than the current item + 1".
> Note that items bar and baz have exactly the same totals and are tied,
> while the rank of bat shows that there are 3 items that have totals
> greater than bat.
>
> Joe Celko's "SQL for Smarties" has a bunch of things like this in it.
> I've found it quite helpful.
>
> Hope this helps.
>
> Michael Glaesemann
> grzm myrealbox com
>
> create table items (
> item text not null
> , qty integer not null
> ) without oids;
>
> insert into items (item, qty) values ('foo', 1);
> insert into items (item, qty) values ('foo', 2);
> insert into items (item, qty) values ('foo', 1);
> insert into items (item, qty) values ('foo', 3);
> insert into items (item, qty) values ('foo', 3);
> insert into items (item, qty) values ('foo', 20);
> insert into items (item, qty) values ('foo', 1);
> insert into items (item, qty) values ('bar', 3);
> insert into items (item, qty) values ('bar', 1);
> insert into items (item, qty) values ('bar', 3);
> insert into items (item, qty) values ('bar', 13);
> insert into items (item, qty) values ('baz', 2);
> insert into items (item, qty) values ('baz', 4);
> insert into items (item, qty) values ('baz', 14);
> insert into items (item, qty) values ('bat', 3);
> insert into items (item, qty) values ('bat', 4);
>
> select item, sum(qty) as tot_qty
> from items
> group by item
> order by tot_qty desc;
>
> item | tot_qty
> --+-
> foo | 31
> bar | 20
> baz | 20
> bat | 7
> (4 rows)
>
> select i1.item
> , i1.tot_qty
> , ( select count(*)
> from (
> select item
> , sum(qty) as tot_qty
> from items
> group by item
> having sum(qty) > i1.tot_qty
> ) as i2
> ) + 1 as rank
> from (
> select item
> , sum(qty) as tot_qty
> from items
> group by item
> ) as i1
> order by i1.tot_qty desc;
>
> item | tot_qty | rank
> --+-+--
> foo | 31 |1
> bar | 20 |2
> baz | 20 |2
> bat | 7 |4
> (4 rows)
>
>
--
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
http://www.MailLaunder.com/
312-560-1574
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Determining Rank
Michael, That's an excellent solution, but on my table, the explain plan sucks and the query time is over 3 minutes when implemented. Is there a simple way to get a row_num without using a temporary sequence? Thanks for your help. -Don Make your query a set returning function which iterates over the query results and returns a number as well ? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
