[SQL] cast type bytea to double precision

[Amila Jayasooriya]

HI All,

I have a database column which type is bytea. It contains floats converted
as byte array (4 bytes per one float) and encoding is Escape. I would be
able to get corresponding bytea string using substring function.

My question is how can I convert bytea string to float inside a SQL
function. Earlier I converted to float in C# side. I used
dataReader.getByte method to retrieve bytes and then Converted to float
using BitConverter.ToSingle (.Net build in class) method.

Now I can't use intermediate component as Npqsql driver. I want SQL to
directly convert bytea into floats and return the corresponding number when
execute a query from 3rd party application.

Thanks and Regards
 Amila Jayasooriya

[SQL] How can I use this subselect properly in an update?

[John Tuliao]

select
substring(t1.called_number from t2.offset),
t1.called_number
from
calls_join as t1,
john_prefix as t2
where
strpos(t1.called_number, t2.prefix) = '1'
order by length(t2.prefix) desc limit '1' ;

I'm having trouble using this as a sub-select in an UPDATE. Most of the 
time it works. Then sometimes it doesn't.
Is there an issue with possible NULL values that can be found on table1 
(t1)?



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

[SQL] hstore and populate_record

[Jan Eskilsson]

Hi All


Background, I have a table that i need to audit changes for
tracability etc. I found an example on one of the postgres lists to
use hstore:s to store the audit data. I find this to be a really good
idea since its really simple to implement and it works really well.

But now comes next step, how to get the data back from the hstore into
a record in the original format.

My two tables looks like this, first the table to be audited,
CREATE TABLE commissionbase
(
  commissionbase_pkey integer NOT NULL,
  salesorder character varying(20) NOT NULL,
  salesmanid character varying(50) NOT NULL,
  stockitem character varying(50) NOT NULL,
  quantity double precision,
  price numeric(15,2),
  commission character varying(10),
  commissionsum numeric(15,2),
 CONSTRAINT commissionbase_pkey PRIMARY KEY (commissionbase_pkey ),
)


And then the audit table,
CREATE TABLE audit.audit_log
(
  log_operation text,
  log_old_values hstore,
  log_new_values hstore,
  log_table text,
  CONSTRAINT audit_log_pkey PRIMARY KEY (log_id )
)


My problem,
SELECT * FROM public.populate_record(null::commissionbase,
   (select log_new_values from
audit.audit_log where log_table = 'commissionbase' LIMIT 1));

This select works really well as long as i limit it to one record,
when i remove the limit restriction it dont work because the subquery
returns more then one record. Is it possible to rewrite to handle
multiple records, my sql knowledge is a bit to limited to figure out
how?

All input is greatly apreciated !

Thank you in advance !

Best Regards
Jan Eskilsson

-- 
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

Please consider the environment before you print this email.

(1) The contents of this transmission are privileged and confidential
and intended solely for the use of the addressee. Any disclosure,
distribution or copying of the contents, other than by the addressee,
is strictly prohibited. If you receive this transmission in error,
please notify us immediately and destroy the material received.
(2) All incoming and outgoing emails and any attachments are subjected
to a virus scanner and are believed to be free of any virus, or any
other defect which might affect any computer or IT system into which
they are received and opened. Therefore, it is the responsibility of
the recipient to ensure that they are virus free and no responsibility
is accepted by Jan Eskilsson  for any loss or damage arising in any
way from receipt or use thereof.

-- 
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] Window function frame clause

[vpapavas]

Thank you David for your fast response. 

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Window-function-frame-clause-tp5491171p5494599.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

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