[SQL] Sybase Connection_Property('number') equivalent in PostGre ?
I am trying to find out the PostGre equivalent to the Sybase function Connection_Property (which returns the connection id, given the parameter 'number') ,but without success so far. Can anyone tell me how to retrieve the connection id in PostGre? Thanks in advance, Emil __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Sybase Connection_Property('number') equivalent in PostGre
O Emil Rachovsky έγραψε στις Dec 2, 2005 : > > I am trying to find out the PostGre equivalent to > the Sybase function Connection_Property (which returns > the connection id, given the parameter 'number') ,but > without success so far. Can anyone tell me how to > retrieve the connection id in PostGre? > Thanks in advance, > Emil I think pg_backend_pid() and pg_stat_get_backend_pid(integeger) are your best bets. > > > > __ > Start your day with Yahoo! - Make it your home page! > http://www.yahoo.com/r/hs > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Just 1 in a series...
I currently have a working SQL that SELECTs all records whose 'river_stage' column exceeds the 'flood_stage' column. (Very simple -- no applause needed.) Typically, if I get one record, I get a consecutive series of them since rivers rise and fall in a continuous fashion, and usually respond lethargically when this much water is in the rivers. This time-series of river stages all have (another column called) 'event_id' set to the same integer value, so long as the river has not fallen below flood stage (which will trigger the event_ID to be incremented). However, I only want the first occurrence of a such a series (where the event_id is the same), what SQL syntax should I use to do this? I tried playing with different combinations using DISTINCT, GROUP BY, and LIMIT 1, but I have had no success getting the results I'm looking for, thus far. So I figured I might get farther faster by asking the group. I must be misunderstanding the "GROUP BY" clause because I get an error essentially stating that I need to list every column in the SELECT list in the GROUP BY list (which makes it ineffective)... My knots are tangled. Can someone please send advice regarding this issue? Mark ---(end of broadcast)--- TIP 1: 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] Just 1 in a series...
Mark Fenbers <[EMAIL PROTECTED]> writes: > ... However, I only want the first occurrence of a such a series (where the > event_id is the same), what SQL syntax should I use to do this? You might find the "DISTINCT ON" syntax does just what you want --- see the "weather report" example on the SELECT reference page. It's not standard SQL though. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Just 1 in a series...
You might find the "DISTINCT ON" syntax does just what you want --- see the "weather report" example on the SELECT reference page. It's not standard SQL though. This works! Thanks! What would have to be done if I needed a standard SQL solution? Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] rename idx's with table; avoid confusing idx names?
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
After tearing out some hair over the following sequence of events:
[a few weeks ago]
alter table foo rename to old_foo;
create table foo();
insert into foo select blahblahblah from old_foo;
[today]
cluster foo_pkey on foo;
ERROR: "foo_pkey" is not an index for table "foo"
What? Why does \d say the primary key idx is foo_pkey1
[light dawns]
Aha! "alter table rename to" did not rename the table's indexes!
I put together a plpgsql function to rename a table and it's indexes
correspondingly[see below]. I would like to know:
Is there a more robust/portable/clear way to do this?
Is this a bad idea for some subtle reason?
Is there any way to get a less cumbersome interface than "select
rename_table_and_indexes('foo','old_foo')?
Does this look useful enough for me to package more formally?
-- George Young
CREATE or REPLACE FUNCTION rename_table_and_indexes(old_name text, new_name
text) returns void AS $$
declare
prefix_len integer;
r record;
begin
prefix_len = length(old_name);
for r in select indexrelname from pg_stat_user_indexes where
relname=old_name loop
execute 'alter index ' || r.indexrelname || ' rename to ' ||
quote_ident(new_name) || substr(r.indexrelname, prefix_len + 1);
raise NOTICE 'renamed index % to %', r.indexrelname, new_name ||
substr(r.indexrelname, prefix_len + 1);
end loop;
execute 'alter table ' || quote_ident(old_name) || ' rename to ' ||
quote_ident(new_name);
raise NOTICE 'alter table % rename to %', old_name, new_name;
end;
$$
LANGUAGE plpgsql;
^^
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] rename idx's with table; avoid confusing idx names?
george young writes: > I put together a plpgsql function to rename a table and it's indexes > correspondingly[see below]. I would like to know: > Is there a more robust/portable/clear way to do this? > Is this a bad idea for some subtle reason? It won't work if the table and column names are so long as to require truncation to form an index name. Also there are some corner cases in which you'll collide with existing index names. (The underlying backend index-name creation logic goes to some effort to generate nonconflicting index names, but this code isn't doing any such thing.) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
