[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
