Re: [SQL] find all tables with a specific column name?
Is there a reasonable way to extract a list of all tables which contain a specific column name from the system views on 8.1? For instance, I might want to enumerate all tables with a column named last_modified. This is nothing new but if I may, may I add for this thread's completeness a try from internal tables? select a.relkind, a.relname from pg_class a inner join pg_attribute b on a.relfilenode = b.attrelid group by a.relkind, a.relname, a.relfilenode,b.attname having b.attname='IID'; The result didn't match the one from the information_schema.tables - the above query included indexes too (relkind=i) while information_schema.tables included only tables and views (r,v). Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] LinkedList
I have a table that I created that implements a linked list. I am not an expert SQL developer and was wondering if there are known ways to traverse the linked lists. Any information that can point me in the direction to figure this out would be appreciated. The table contains many linked lists based upon the head of the list and I need to extract all of the nodes that make up a list. The lists are simple with a item and a link to the history item so it goes kind of like: It may not be exactly suitable, but this one does only traversal (assuming the list is not clsoed) create table linkedlist(prevnode int, nextnode int, val int); -- HEAD insert into linkedlist values(null,1,0); insert into linkedlist values(1,2,10); insert into linkedlist values(2,3,20); insert into linkedlist values(3,4,30); insert into linkedlist values(4,5,40); -- TAIL insert into linkedlist values(5,null,50); -- TRAVERSE begin; declare mc cursor for select * from linkedlist order by nextnode; fetch 1 from mc; fetch 1 from mc; ... close mc; commit; which is nothing more than, select * from linkedlist order by nextnode; Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] LinkedList
On Fri, 28 Apr 2006, Guy Fraser wrote: -- HEAD insert into linkedlist values(null,1,0); insert into linkedlist values(1,2,10); insert into linkedlist values(2,3,20); insert into linkedlist values(3,4,30); insert into linkedlist values(4,5,40); -- TAIL insert into linkedlist values(5,null,50); Bad example of a double linked list, you also need an id for the current node and the values of prevnode and nextnode do not need to be ordered or contiguous as the example shows. Wow. Interesting... I am willing to be corrected, but to me the "node" field seems redundant, since it does not add any information. (Since each item in the list is already uniquely identifiable without the "node".) Certainly so, for traversing, which was the OP's intention. It may save some steps in case of other operations but at the expense of one more field. Please see below. create table linkedlist(node int,prevnode int, nextnode int, val int); insert into linkedlist values(1,null,2,0); insert into linkedlist values(2,1,3,10); insert into linkedlist values(3,2,4,30); insert into linkedlist values(4,3,5,20); insert into linkedlist values(5,4,6,40); insert into linkedlist values(6,5,null,50); If we now wanted to reorder an item in the set you need make some updates in a block, which I have not done before but should be something like this: Move node 4 between 2 and 3 so that the values from head to tail are ordered. update linkedlist set prevnode = '2',nextnode = '3' where node = '4'; update linkedlist set nextnode = '4' where node = '2'; update linkedlist set prevnode = '4' where node = '3'; If the intention is to change it from 0-10-30-20-40-50 to 0-10-20-30-40-50, it would have been (in my design) exchanging node 3 and node 4 below. null,1,0 1,2,10 <-- node 2 2,3,30 <-- node 3 3,4,20 <-- node 4 4,5,40 5,null,50 Now, it can be done by: begin; update linkedlist set prevnode=2 where prevnode=3; -- node 4 = (2,4,20) update linkedlist set prevnode=3 where nextnode=3; -- node 3 = (3,3,30) update linkedlist set nextnode=3 where prevnode=2; -- node 4 = (2,3,20) update linkedlist set nextnode=4 where nextnode=3; -- node 3 = (3,4,30) commit; achieving the same. ... 2,3,20 <-- node 4, originally 3,4,30 <-- node 3, originally ... "node" will be more cost efficient if we insert an item at the beginning of a long list, for example insert (2,3,100) before node 3 (2,3,20), but at least the sql is simple; update linkedlist set prevnode = prevnode + 1 where prevnode > 1; update linkedlist set nextnode = nextnode + 1 where nextnode > 2; and then do insert (2,3,xxx) This method can also be used for reordering. The usefulness of the "node" will depend on the economics of these update operations over keeping one more field. But I think this is more of an exercise, and functions would be the proper way for complex operations. 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]Linked List
I have a table that I created that implements a linked list. I am not an expert SQL developer and was wondering if there are known ways to traverse the linked lists. The table contains many linked lists based upon the head of the list and I need to extract all of the nodes that make up a list. The lists are simple with a item and a link to the history item so it goes kind of like: 1, 0 3, 1 7, 3 9, 7 ... I missed "The table contains many linked lists", so wanted to do another try. I guess there should be a better way, but what if you do this? 1) Assuming your table has two columns (n int, p int), do create table tmplist (n int, p int); 2) drop function traverse(integer); create or replace function traverse (integer) returns integer as $$ declare x int; begin x := $1; while x is not null loop select n into x from linkedlist where p = x; insert into tmplist (select * from links where p=x); -- or do any processing end loop; return 1 ; end; $$ language plpgsql; 3) select traverse(0); select * from tmplist; 0 - 1 - 4 - 8 - 12 ... delete from tmplist; select traverse(2); select * from tmplist; 2 - 3 - 5 - 6 - ... (where 0 or 2 is the heads of the linked lists in the table, which you want to traverse) I'd appreciate any insight if there's a better way but somehow it was not possible to return setof int from within while loop whereas it was possible from within a for loop. I didn't find a way to deliver the templist table name as argument. (Somehow there seemed to be a bug(?) there) Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL]Linked List
The pgsql function is compiled and wouldn't know how to handle a table name as a variable. If you rewrite the SQL to use the 'EXECUTE' statement I think you could do this, something along the lines of (untested): EXECUTE ''INSERT INTO '' || quote_ident(tmplist) || '' (SELECT * FROM links WHERE p=x)''; Thanks. Yet, if I give the table name as the argument, I get the same error. drop function traverse(integer, text); create or replace function traverse (integer, text) returns integer as $$ declare x int; tname alias for $2; begin x := $1; while x is not null loop select n into x from links where p = x; insert into tmplink (select * from links where p=x); EXECUTE ''INSERT INTO '' || quote_ident(tname) || '' (SELECT * FROM links WHERE p=x)''; end loop; return 1 ; end; $$ language plpgsql; The above gives the following error. Please note that the first and second args are mixed up now. I tested it only on 8.0 (FC 4) and didn't on 8.1 and wonder whether it's a bug or I may be doing something wrong. Using tname or $2 doesn't change the result. # select traverse(0, 'links2'); ERROR: syntax error at or near "INSERT" at character 11 QUERY: SELECT ''INSERT INTO '' || quote_ident( $1 ) || '' (SELECT * FROM links WHERE p= $2 )'' CONTEXT: PL/pgSQL function "traverse" line 10 at execute statement LINE 1: SELECT ''INSERT INTO '' || quote_ident( $1 ) || '' (SELECT ... Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Sorting aggregate column contents
It works fine. But I wouldn't like using subselect's, then if somebody else knows about an operator or something like that to put on the aggregator, please tell me. I think the nature of the f_concat makes it difficult to sort, since it simply adds the next value, so if the source table gives value in the order of 'a','c','d','b' there's no way to handle them within f_concat unless you modify and rearrange the previous result string from within f_concat. So the source table (city) should be sorted. I don't know if this is a standard way, but this one seems to do that. == select s.name, ag_concat(c.name) from state s inner join (select * from city order by name desc) as c on c.idstate=s.idstate group by s.name order by 1; OR select s.name, ag_concat(c.name) from state s, (select * from city order by name desc) as c where c.idstate = s.idstate group by s.name order by 1; == I'm just reordering the source table on the fly. Curiously, if you don't have 'desc' you'll get a reverse ordered list. (z,...,a) I think your needs may also be met without any aggregator as well (there may be marginal cases which I haven't thought of, but I assume they can be handled if needed) == select s.name, array_to_string(array(select name from city where idstate = s.idstate order by name),',') from state s; == name | array_to_string --+- RP | Gramado,Port Alegre SP | Osasco * I see normalization issue here but guess it's not important. Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] LinkedList
The problem is that your way, there is no indicated way to determine which node is which. For instance is you update any of your nodes then the node list would be out of order and your list would not work. I think the thinking is different here. The OP's list is ordered and has prev-next only, and there can be lists that are read only and/or ordered (like clickstream or a data stream out of multi-stream packets) and do not require insert. That's why I mentioned it's for traverse-only in my original post. (But I disagree with you about not being able to determine a node - since in sql it's possible to identify a row as long as it has unique values in fields, however they are named) After I posted the message I realized there is another way to do this without adding an extra field, and it would be a closer example to how it is done in C. If you assigned the OID of the previous and next nodes rather than arbitrary integer, you could access each node independent of the order they are listed. I have not messed around much with OIDs. I am not sure if OIDs change if an entry is updated. I understand oid doesn't change with update. But tables may or may not have oids. (can be created "without oids") I also came to appreciate the difference with C. In sql, there is a way to identify a row like I did, but in C it'd not be possible without the address (of course it's not like "impossible" but ...), so the linked list as in strict C-like sense would be perfect but may carry a different value here. (Since we already have the added layer of sql engines.) I agree your method would be better if we want to scale when insert or delete is needed. It'd be interesting to compare how the normal O() applies to sql - would updating n rows with one sql statement be equivalent to O(n) in C? Maybe a silly question but it came to my mind... In C you would use a pointer to storage location of previous and next "node" which is similar to using the OID. In some cases it can be necessary to use pointers to pointers when accessing variable length relocatable data, but that goes way past what this thread is about. The example I provided, is still feasible and alleviates all unknowns at the expense of 4 bytes of storage for one integer used as a fixed address for each node. As long as it works in real world use. Without some way of addressing each node, the idea of a linked list seems wrong, since a linked is supposed to hold the address of the previous and or next item in the list, assuming the data is always going to be correctly sorted so that you can locate the next item by tupple number seems overly assumptive. If it works for you great, your example may then be useful as a short cut, but I don't believe in leaving things to chance when programming. Regards, Ben K. Developer http://benix.tamu.edu ---(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] i am getting error when i am using copy command
file '/tmp/penchal.out' for writing: no such file or directory.. _IF_ you're on linux it could be due to selinux. setenforce 0 might solve the problem tempoarily. I would assume there should've been some existing discussion threads. (setenforce 1 afterwards.) Regards, Ben K. ---(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] audit table containing Select statements submitted
Current_user Timestamp "The Select Statement Submitted by the User" http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE might be close to what you want. Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Returning String as Integer
Jorge Godoy <[EMAIL PROTECTED]> wrote: numbers. Is there any way to select a value from this column and return it as an integer? My twopence. I just happened to have the same problem with iReports for a 10 digit number - it may be case specific but in my case # select ... int4(id) ...; worked. I tried int8(id) but java didn't take it as Integer. (It took int8 as something like Long.) Regards, Ben ---(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] Returning String as Integer
My twopence. I just happened to have the same problem with iReports for a 10 digit number - it may be case specific but in my case Please, take care with your quote attributions. I've never asked such a question here and, in fact, I was answering it. The person who asked such a question was Kashmira Patel. Sorry, I apologize. Regards, Ben K. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Most efficient way to hard-sort records
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
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] keeping last 30 entries of a log table
I need to write a function which inserts a log entry in a log table and only
keeps the last 30 records. I was thinking of using a subquery similar to the
following:
insert into log (account_id, message) values (1, 'this is a test);
delete from log where account_id = 1 and id not in ( select id from log
where account_id = 1 order by timestamp desc limit 30);
I'm wondering if there is a more performance oriented method of doing the
delete that I'm not thinking of.
Just for the sake of alternatives -
create sequence cy30 maxvalue 30 cycle;
insert into log values(select generate_series(1,30), 'dummy');
INSERT 0 30
update log set des='' where account_id=(select nextval('cy30'));
UPDATE 1
There are details to consider I guess. For example what if an update fails
and the sequence already advanced... Also, since we cycle the id, for
sorting, we'll need to add timestamp or something similar.
My 2 pence...
P.S.
This A) failed me and I wonder if this is supposed to be so or if it's
just a place where no one treaded on ??
B) works fine except it doesn't advance the sequence.
A) update tc set des='b' where id=nextval('cy30')::int;
UPDATE 30
B) update tc set des='c' where id=currval('cy30');
UPDATE 1
Regards,
Ben K.
Developer
http://benix.tamu.edu
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
