Re: [SQL] Most efficient way to hard-sort records
Is it possible to do this : CREATE TABLE sorted (order_no SERIAL PRIMARY KEY, other columns...) INSERT INTO sorted (columns) SELECT * FROM main_table INNER JOIN key_table ON main_table.id = key_table.main_table_id WHERE key = 'param' ORDER BY value SELECT The SERIAL will automatically generate the order_no you want, which corresponds to the position in the sorted set. Then, to get the records in-order : SELECT * FROM sorted ORDER BY order_no As the records have been inserted in-order in the "sorted" table, this table is, in fact, clustered, so a full table scan using the index on "order_no" will be very fast. Of course this is only interesting if this data is quite static, because you'll have to re-generate the table when the data changes. There is another solution : CREATE INDEX on key_table( key, value ) Now, the index can optimize ordering by (key,value), which is equivalent to ordering by value if key = constant. A bit of query manipulation might get you what you want ; I suppose all rows in "key_table" reference a row in "main_table" ; so it is faster to sort (and limit) first on key_table, then grab the rows from main_table : SELECT k.value, m.* FROM key_table k LEFT JOIN main_table m ON m.id=k.main_table_id WHERE k.key='param' ORDER BY k.key, k.value If key_table REFERENCES main_table, LEFT JOIN is equivalent to INNER JOIN ; however if the planner is smart enough, it might notice that it can index-scan key_table in key,value order, grabbing rows from main_table in order and skip the sort entirely. On Sun, 07 May 2006 08:53:46 +0200, Ben K. <[EMAIL PROTECTED]> wrote: main_table: id, name, position key_table: id, main_table_id, key, value Here is how I need to sort the records: SELECT * FROM main_table INNER JOIN key_table ON main_table.id = key_table.main_table_id WHERE key = 'param' ORDER BY value I currently collect all ids from main_table in sorted order and then update the position field for each row in the main_table one-by-one. Is there a better/faster/more efficient solution? A cheap solution if you don't care about the position value as long as sort order is ok. 1) # SELECT main_table.id into temp_table FROM main_table INNER JOIN key_table ON main_table.id = key_table.main_table_id ORDER BY value; 2) # update main_table set position = (select oid from temp_table where id = main_table.id ); I guess I'll get a set of consecutive oids by this. You can make the number begin at arbitrary number, by 2-a) # update main_table set position = ( (select oid::int4 from temp_table where id = main_table.id ) - (select min(oid::int4) from temp_table) + 1) ; I read that oid wraps around (after ~ billions) so you might want to check your current oid. Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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] Returning String as Integer
Hi, Ben, Ben K. schrieb: > I tried int8(id) but java didn't take it as Integer. (It took int8 as > something like Long.) Yes, and that's good, as PostgreSQL int8 and java long actually are the same datatype (64-bit signed two's-complement). PostgreSQL int4 and Java int are the same (32-bit), as well as int2 and short. HTH, Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Most efficient way to hard-sort records
CREATE TABLE sorted (order_no SERIAL PRIMARY KEY, other columns...)
INSERT INTO sorted (columns) SELECT * FROM main_table INNER JOIN
key_table ON main_table.id = key_table.main_table_id WHERE key = 'param' ORDER
BY value SELECT
The SERIAL will automatically generate the order_no you want, which
corresponds to the position in the sorted set.
Then, to get the records in-order :
SELECT * FROM sorted ORDER BY order_no
Good ... I just got myself into the habit of not recreating a table since
I have to clean up permissions and what not. I guess it depends.
Another version along that line ?
# create sequence counterseq start 1;
-- (set/reset whenever a counter is needed)
# select main_table.*, nextval('counterseq') as position2
into sorted_main_table
from main_table, keytable where main_table.id =
keytable.main_table_id
order by value;
Regards,
Ben K.
Developer
http://benix.tamu.edu
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Most efficient way to hard-sort records
Another version along that line ?
# create sequence counterseq start 1;
-- (set/reset whenever a counter is needed)
# select main_table.*, nextval('counterseq') as position2
into sorted_main_table
from main_table, keytable where main_table.id =
keytable.main_table_id
order by value;
You could also use generate_series(), but I don't know if it can generate
unbounded series...
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Creating nested functions with plpgsql
Jorge Godoy wrote: > I have some real case examples where this could be useful, if it is needed. > I haven't pasted them here because the smallest one has 176 LOC, after > refactoring with nested functions. > > If it is not possible, are there any plans to allow this kind of thing? > (Even with a different syntax it would be good to have it.) We don't support nested functions at present, but you can create a separate function and invoke it as you would call any external function. This is arguably better, because you may then use inner_function in any "outer function", not just the current one. Something like: CREATE FUNCTION inner_function(OUT output_day DATE) AS $_$ DECLARE output_day DATE; BEGIN -- do something to calculate output_day RETURN output_day; END $_$; CREATE FUNCTION outer_function(param1 DATE) RETURNS date AS $$ DECLARE output_day DATE; BEGIN; output_day = SELECT inner_function(params_to_create_date); -- do something in main function that uses inner function several -- times. END; $$ language plpgsql; -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Creating nested functions with plpgsql
Em Domingo 07 Maio 2006 20:33, Alvaro Herrera escreveu: > > We don't support nested functions at present, but you can create a > separate function and invoke it as you would call any external function. Yeah, I know it and that's how I use some things today, but even so, having nested functions help to write some kinds of code. > This is arguably better, because you may then use inner_function in any > "outer function", not just the current one. It depends. There are cases where it is better -- at least for legibility -- to have nested functions. Having a procedural approach is not always the best algorithm, but, as I said, it solves almost all problems. Thanks, -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
