Re: [SQL] help with xpath namespace

2011-09-26 Thread Brian Sherwood 
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

Re: [SQL] handling duplicate row exception

2011-09-26 Thread Filip Rembiałkowski 
W dniu 22 września 2011 08:11 użytkownik Amar Dhole napisał:

> **
>
> Hi Filip,
>
> ** **
>
> No not sure 100% when this can happen. This approach will not be possible
> as in our application we are programmatically handling these cases and going
> in other route to add the record with increased key. 
>
> **
>

I am almost sure this approach will be possible, for more details see:

http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql



> **
>
> I am using 9.0.4 version.
>
> ** **
>
> Thanks
> Amar 
>
> **
>

[SQL] how to calculate differences of timestamps?

2011-09-26 Thread Andreas 

How could I calculate differences of timestamps in a log-table?

Table log ( user_id integer, login boolean, ts timestamp )

So login = true would be a login-event and login = false a logout.
Is there a way to find the matching login/logout to calculate the 
difference?


Or is there a better table "design" to do this?

--
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] how to calculate differences of timestamps?

2011-09-26 Thread Tim Landscheidt 
(anonymous) wrote:

> How could I calculate differences of timestamps in a log-table?

> Table log ( user_id integer, login boolean, ts timestamp )

> So login = true would be a login-event and login = false a logout.
> Is there a way to find the matching login/logout to
> calculate the difference?

This is similar to the question Dianna asked some time ago:

| SELECT user_id,
|prev_ts AS login_ts,
|ts AS logout_ts
|FROM (SELECT user_id,
| LAG(login) OVER (PARTITION BY user_id ORDER BY ts) AS 
prev_login,
| LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) AS 
prev_ts,
| login,
| ts FROM log) AS SubQuery
|WHERE prev_login AND NOT login;

> Or is there a better table "design" to do this?

That depends on your requirements and your application de-
sign. The query above requires a full table scan which may
kill performance in some circumstances.

  Of course, any design has to deal with the possibility of
an event not having been logged, multiple logins, etc. The
query above just forms pairs based on temporal proximity.

Tim


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql