[SQL] loading a file into a field
I am looking to play around with the postgres XML functions, but can't seem to find a way to load my data. I have a number of XML files: file1.xml, file2.xml, etc All files follow the same xml format (DTD?) I would like to create the following table: CREATE TABLE configs ( filenamevarchar(80) PRIMARY KEY, config xml ); and load each file into the xml field, but can't seem to find a way. (I think I need something similiar to lo_import, but have not found anything) Any help is appreciated. Thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] running scripts like oracle sqlplus
Have you tried nextval & currval? http://www.postgresql.org/docs/8.4/interactive/functions-sequence.html Something like this: begin insert into user ( user_id, customer_id, create_user, update_user ) values ( nextval(user_seq), nextval(customer_seq), currval(user_seq), currval(user_seq) ); insert into customer ( customer_id, create_user, update_user ) values ( currval(customer_seq), currval(user_seq), currval(user_seq) ); commit; end; On Fri, Jun 18, 2010 at 6:24 PM, Steven Dahlin wrote: > I have been trying to figure out how I can run a pgsql script like I can > run a plsql script with oracle's sqlplus. Here is a sample script file for > what I want to run: > > declare > sysuserid integer := 0; > hwcustid integer := 0; > begin > > select nextval( 'user_seq' ) into sysuserid; > select nextval( 'customer_seq' ) into hwcustid; > > insert into user > ( > user_id, > customer_id, > create_user, > update_user > ) > values > ( > sysuserid, > hwcustid, > sysuserid, > sysuserid > ); > > insert into customer > ( > customer_id, > create_user, > update_user > ) > values > ( > hwcustid, > sysuserid, > sysuserid > ); > > commit; > > end; > > I try to run the script in psql and thru pgadmin and cannot seem to make > them work. I do not want to turn it into a function. I just want it to > execute the block in a fashion similar to Oracle'sqlplus running > @scriptfile.sql. > > Thanks >
[SQL] Sorting router interfaces
I am trying to sort router interface names. The problem is that I am doing a text sort and need to do a numerical sort. I want the interfaces to be in numerical order: GigabitEthernet1/0/1 | 1/0/1 | {1,0,1} GigabitEthernet1/0/2 | 1/0/2 | {1,0,2} GigabitEthernet1/0/3 | 1/0/3 | {1,0,3} etc. What I get instead is the following text ordering: GigabitEthernet1/0/1 | 1/0/1 | {1,0,1} GigabitEthernet1/0/10 | 1/0/10 | {1,0,10} GigabitEthernet1/0/11 | 1/0/11 | {1,0,11} GigabitEthernet1/0/12 | 1/0/12 | {1,0,12} GigabitEthernet1/0/13 | 1/0/13 | {1,0,13} GigabitEthernet1/0/14 | 1/0/14 | {1,0,14} GigabitEthernet1/0/15 | 1/0/15 | {1,0,15} GigabitEthernet1/0/16 | 1/0/16 | {1,0,16} GigabitEthernet1/0/17 | 1/0/17 | {1,0,17} GigabitEthernet1/0/18 | 1/0/18 | {1,0,18} GigabitEthernet1/0/19 | 1/0/19 | {1,0,19} GigabitEthernet1/0/2 | 1/0/2 | {1,0,2} GigabitEthernet1/0/20 | 1/0/20 | {1,0,20} GigabitEthernet1/0/21 | 1/0/21 | {1,0,21} GigabitEthernet1/0/22 | 1/0/22 | {1,0,22} GigabitEthernet1/0/23 | 1/0/23 | {1,0,23} GigabitEthernet1/0/24 | 1/0/24 | {1,0,24} GigabitEthernet1/0/25 | 1/0/25 | {1,0,25} GigabitEthernet1/0/26 | 1/0/26 | {1,0,26} GigabitEthernet1/0/27 | 1/0/27 | {1,0,27} GigabitEthernet1/0/28 | 1/0/28 | {1,0,28} GigabitEthernet1/0/29 | 1/0/29 | {1,0,29} GigabitEthernet1/0/3 | 1/0/3 | {1,0,3} GigabitEthernet1/0/30 | 1/0/30 | {1,0,30} GigabitEthernet1/0/31 | 1/0/31 | {1,0,31} GigabitEthernet1/0/32 | 1/0/32 | {1,0,32} GigabitEthernet1/0/33 | 1/0/33 | {1,0,33} FYI: I also have entries like the following: lc-5/2/0.32769 | 5/2/0.32769 | {5,2,0.32769} irb.5 | .5 | {.5} irb.51 | .51 | {.51} irb.52 | .52 | {.52} ae6 | 6 | {6} ae7 | 7 | {7} lo0.0 | 0.0 | {0.0} Vlan710 | 710 | {710} Vlan760 | 760 | {760} Vlan910 | 910 | {910} Vlan910 | 910 | {910} gre | | {""} tap | | {""} dsc | | {""} The above listings are produced with the following: SELECT interface, regexp_replace(interface,'[A-Za-z -]+','','g') as "sort_col1", regexp_split_to_array(regexp_replace(interface,'[A-Za-z -]+','','g'),E'/') as "sort_col" FROM all_ports ORDER BY devicename,sort_col I have tried to break out the interface number to a separate array column to sort on and was hoping to cast the array to a float[], but no luck: SELECT interface, regexp_replace(interface,'[A-Za-z -]+','','g') as "sort_col1", regexp_split_to_array(regexp_replace(interface,'[A-Za-z -]+','','g'),E'/')::float as "sort_col" FROM all_ports psql:-:15: ERROR: cannot cast type text[] to double precision LINE 5: ...gexp_replace(interface,'[A-Za-z -]+','','g'),E'/')::float as... Can anyone suggest a better approach or help with this approach? Thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] select xpath ...
Boris, Can you send me your final solution? I am trying to do something similar and I think I am stuck at the namespace. Thanks On Mon, Sep 19, 2011 at 11:49 AM, boris wrote: > On 09/19/2011 10:49 AM, Rob Sargent wrote: >> >> Having a name space in the doc requires it's usage in the query. > > yeah, I got it... I was using wrong one... > thanks. > > >> >> >> On 09/17/2011 11:48 AM, boris wrote: >>> >>> hi all, >>> I've inserted xml file : >>> >>> >>> >> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";> >>> >>> zz >>> .. >>> >>> >>> to a table: >>> >>> CREATE TABLE "temp".tempxml >>> ( >>> record xml >>> ) >>> >>> I can get it using: >>> select * from temp.tempxml >>> >>> >>> but, I can't get any values using xpath. ex: >>> >>> >>> select (xpath('/document/title/text()', record ))[1] from temp.tempxml >>> >>> >>> am I doing it right? >>> >>> thanks. >>> >>> >>> >>> >> > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] help with xpath namespace
I am trying to use xpath to extract some information from a XML document. (if it matters, It's the router config for a Juniper router) I believe I am having problems with the namespace. I am trying to extract the serial-number, but I am not getting anything. Here is the script I have been testing with: BEGIN; CREATE TABLE "xml_test" ( data_xmlxml ); INSERT INTO xml_test (data_xml) VALUES (' http://xml.juniper.net/junos/9.6R4/junos-chassis";> Chassis JN11 MX960 '); select data_xml from xml_test where data_xml is document; select (xpath('/chassis-inventory/chassis/serial-number/text()', data_xml, ARRAY[ARRAY['junos', 'http://xml.juniper.net/junos/9.6R4/junos-chassis']] )) from xml_test; ROLLBACK; This gives me the following: BEGIN CREATE TABLE INSERT 0 1 data_xml --- http://xml.juniper.net/junos/9.6R4/junos-chassis";>+ + Chassis+ JN11 + MX960+ + (1 row) xpath --- {} (1 row) ROLLBACK Can anyone suggest how I would go about getting the serial-number with xpath? Thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] help with xpath namespace
Yep, that did it. Thanks! 2011/9/23 Filip Rembiałkowski : > > > 2011/9/22 Brian Sherwood >> >> select (xpath('/chassis-inventory/chassis/serial-number/text()', >> data_xml, >> ARRAY[ARRAY['junos', >> 'http://xml.juniper.net/junos/9.6R4/junos-chassis']] >> )) from xml_test; >> >> Can anyone suggest how I would go about getting the serial-number with >> xpath? >> > > > http://www.postgresql.org/docs/9.1/static/functions-xml.html#FUNCTIONS-XML-PROCESSING > - see "mydefns". > > This will work: > > select xpath( > '/junos:chassis-inventory/junos:chassis/junos:serial-number/text()', > data_xml, > ARRAY[ARRAY['junos', > 'http://xml.juniper.net/junos/9.6R4/junos-chassis']] > ) > from xml_test; > > > > cheers, Filip > > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to right justify text in psql?
I am running postgresql 9.2. I am assuming it would be a function of psql to right justify text, but I can't find any way to do this. Is there a way to right justify just one text column? Thanks Brian