Re: [SQL] Select Maths

[Phillip Smith]

Beautiful – Works a treat. Thanks
Aaron.

 

A follow-on problem now… I have the
below column in the select, but I need to validate the value across all 3 rules
–  I need to assign it to a variable!!

 

Example – my pqty function
calculates a value less than the suppliers minimum order qty (and therefore
fails the first CASE below), I need to set the column to a new value (stock.purchase_unit)
– That’s all OK. But I need to check this new value against the
remaining 2 CASE’s…

 



CASE  WHEN pqty(stock.code)
< stock.purchase_unit THEN stock.purchase_unit

    --^^^--
Check that our suggested purchase qty is greater than then suppliers minimum
order qty

  WHEN
MOD(pqty(stock.code), stock.box_qty) > 0 THEN stock.box_qty * ROUND(CAST(pqty(stock.code)
AS DOUBLE PRECISION) / stock.box_qty)

    --^^^--
Check that our suggested purchase qty is a multiple of the box qty

  WHEN
pqty(stock.code) < (urate(stock.code) * creditors.review_cycle) THEN urate(stock.code)
* creditors.review_cycle

    --^^^--
Check that our suggested purchase qty is greater than our Usage Rate x Creditor
Review Cycle

END AS "pqty",



 

Thanks again for all your help guys,

-p

 

-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Aaron
Bono
Sent: Friday,
 7 July 2006 18:37
To: Phillip Smith
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Select Maths

 

On 7/7/06, Phillip Smith <[EMAIL PROTECTED]>
wrote:











Same SELECT query as before, different area
of it… I have a function that calculates the recommended purchase order
quantity for a stock item based off various other values and functions:

pqty(stock.code) AS "pqty"

 

This needs to be rounded up / down to the
nearest multiple of the purchase unit quantity for that product –
It's Friday afternoon and my head has refused to help me work out the maths all
afternoon!

 

Example:


Pqty = 60


Purchase Unit = 25

Pqty needs to be rounded down to 50.

 

I guess I'm also asking if I should do this
in the Pqty function or in the SELECT query to optimize the result?












select 25 * round(cast(60 as double precision) / 25) 



 







***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] SELECT substring with regex

[T E Schmitz]

Emils wrote:

2006/7/7, T E Schmitz <[EMAIL PROTECTED]>:



valid entries would be:
"28mm F2.8" (prime lens)
"30-70mm F4" (zoom lens)
"30-70mm F2.8" (zoom lens)
"30-100mm F4.5-5.6" (zoom lens with variable speed)



If these are the cases, wouldn't the regex be simply:

"^[\d\-]+mm" for BASE

"^[\d\-]+mm (.+)$" for SUFFIX

Or are you having to deal with malformatted data too (like "30 -70mm"
or "28 mm ")?


There were quite a few malformed MODEL.NAMEs (upper/lower case, 
additional blanks, missing mm) and therefore a precise regexp was required.


I did this as a 2-stage process:

WHERE !~ '^((\\d+(-\\d+)?)mm F((\\d+(\.\\d+)?)+(\.(\\d+(\.\\d+)?)+)?))$'
selected all malformed MODEL.NAMEs
I corrected the malformed basenames, which left me with the BASE/SUFFIX 
problem only.



I selected all of those and /knowing/ that none of them were malformed, 
I used a simplified pattern without round brackets to produce the BASE 
and SUFFIX result columns:


 substring (MODEL.MODEL_NAME, '^[-\\d]+mm F[-\\d\.]+'   ) as BASE,
 substring (MODEL.MODEL_NAME, '^[-\\d]+mm F[-\\d\.]+ *(.*)$') as SUFFIX

exported that together with PKs as CSV and generated SQL from it.

Basically, my problem had been that I needed to express the SUFFIX 
pattern without using brackets for the BASE.



I would like to thank everyone who contributed to this thread.

--


Regards/Gruß,

Tarlika Elisabeth Schmitz

---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] MS-SQL<->Postgres sync

[Kevin Bednar]

  Looking to keep 2 databases in 
sync, at least semi-realtime if possible, although running a batch update 
every x mins wouldn't be out of the question. One db is postgres and the 
other is ms-sql. It's to keep inventory in sync from 2 seperate locations, 
one being a brick and mortar store and the other an ecommerce system 
for a web site. Any and all help is appreciated since I can't find much 
of anything on syncing these 2 database systems!  
Kevin BednarSystems SupportStockwell Design Group
http://www.stockwelldesigngroup.com 
[EMAIL PROTECTED]

Re: [SQL] MS-SQL<->Postgres sync

[Forums @ Existanze]

 
We are looking for the exact thing but with two PostgreSQL databases
 

  
  
  From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED] On Behalf Of Kevin 
  BednarSent: 10 July 2006 18:28To: 
  pgsql-sql@postgresql.orgSubject: [SQL] MS-SQL<->Postgres 
  sync
  
  Looking to keep 2 databases in sync, at least semi-realtime if possible, 
  although running a batch update every x mins wouldn't be out of the question. 
  One db is postgres and the other is ms-sql. It's to keep inventory in sync 
  from 2 seperate locations, one being a brick and mortar store and the 
  other an ecommerce system for a web site. Any and all help is 
  appreciated since I can't find much of anything on syncing these 2 database 
  systems! 
  Kevin BednarSystems SupportStockwell Design Grouphttp://www.stockwelldesigngroup.com
  [EMAIL PROTECTED]

Re: [SQL] MS-SQL<->Postgres sync

[Andrew Sullivan]

On Mon, Jul 10, 2006 at 11:27:52AM -0400, Kevin Bednar wrote:
> Looking to keep 2 databases in sync, at least semi-realtime if possible, 
> although running a batch update every x mins wouldn't be out of the 
> question. One db is postgres and the other is ms-sql. It's to keep inventory 
> in sync from 2 seperate locations, one being a brick and mortar store and 
> the other an ecommerce system for a web site. Any and all help is 
> appreciated since I can't find much of anything on syncing these 2 database 
> systems!

I don't think there's actually any product that does this right now,
unless you're willing to use some sort of statement replica system
(which means you have to deal with failures in one database and not
another).  Is the idea that this is multi-master?

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Select Maths

[Aaron Bono]

On 7/10/06, Phillip Smith <[EMAIL PROTECTED]> wrote:
















Beautiful – Works a treat. Thanks
Aaron.

 

A follow-on problem now… I have the
below column in the select, but I need to validate the value across all 3 rules
–  I need to assign it to a variable!!

 

Example – my pqty function
calculates a value less than the suppliers minimum order qty (and therefore
fails the first CASE below), I need to set the column to a new value (stock.purchase_unit)
– That's all OK. But I need to check this new value against the
remaining 2 CASE's…

 



CASE  WHEN pqty(stock.code)
< stock.purchase_unit THEN stock.purchase_unit

    --^^^--
Check that our suggested purchase qty is greater than then suppliers minimum
order qty

  WHEN
MOD(pqty(stock.code), stock.box_qty) > 0 THEN stock.box_qty * ROUND(CAST(pqty(stock.code)
AS DOUBLE PRECISION) / stock.box_qty)

    --^^^--
Check that our suggested purchase qty is a multiple of the box qty

  WHEN
pqty(stock.code) < (urate(stock.code) * creditors.review_cycle) THEN urate(stock.code)
* creditors.review_cycle

    --^^^--
Check that our suggested purchase qty is greater than our Usage Rate x Creditor
Review Cycle

END AS "pqty",

Can you provide example values and show where it is and is not working?  I am not quite sure what you are trying to do here.


-Aaron 

Re: [SQL] MS-SQL<->Postgres sync

[Aaron Bono]

On 7/10/06, Kevin Bednar <[EMAIL PROTECTED]> wrote:

  
  
 Looking to keep 2 databases in 
sync, at least semi-realtime if possible, although running a batch update 
every x mins wouldn't be out of the question. One db is postgres and the 
other is ms-sql. It's to keep inventory in sync from 2 seperate locations, 
one being a brick and mortar store and the other an ecommerce system 
for a web site. Any and all help is appreciated since I can't find much 
of anything on syncing these 2 database systems!  
Kevin BednarCan you provide more information about what data is needing to be fed back and forth?  Are both databases being updated or can you set one as the system of record?  Why can't you have the web site read directly from MS SQL Server?
With a little more information we can provide more useful advice.Thanks,Aaron

Re: [SQL] MS-SQL<->Postgres sync

[Aaron Bono]

Please reply to all when replying on the list...On 7/10/06, Kevin Bednar <[EMAIL PROTECTED]
> wrote:
  
  
 
Thanks Aron. What I'm actually trying to do is this:   
Postgress in physical store, being used by POS system as the back 
end. MS-SQL being used on web server by ecommerce system. 
  Table structures are different of course, but some 
common fields. What I want to do is when an item is sold in the store, 
update the quantity field for that sku number on the web site and vice 
versa. Only 2 fields basically need to be updated on each side, the SKU 
number and quantity. This is to keep the product table in sync and try to 
avoid selling product that isnt in stock and setting a flag on the web 
system stating such. Thanks for your help. For something this simple you are probably better off doing some custom coding.If you have the ability to modify the databases, I would recommend putting a trigger on each database so when there is a product sold, that sale is recorded in a temp table (which serves as a queue of data that needs to be synched).  Then have a process read from these temp tables and feed the data back to the other database.  Of course, I am assuming you have full control to change the databases - some vendors do not allow that.
You may be able to connect the databases - MS SQL Server will definitely allow you to connect via ODBC to another database and feed data back and forth.  I think there are add on modules for PostgreSQL but I have not tried to have PostgreSQL talk to other databases before.
-Aaron

Re: [SQL] MS-SQL<->Postgres sync

[Kevin Bednar]

  
Yeah, I was kind of thinking that myself. I do have control over both DB's, 
and postgres does have an ODBC connector available as well. Perhaps using a 
trigger to watch a temp table on each site and having it replicate to the 
opposite side and then remove the record from the temp table would work. 
I'll have to look into it. Thanks!   Kevin 
  
-Original Message-From: "Aaron Bono" 
<[EMAIL PROTECTED]>To: "Kevin Bednar" 
<[EMAIL PROTECTED]>, pgsql-sql@postgresql.orgDate: 
Mon, 10 Jul 2006 12:15:34 -0500Subject: Re: [SQL] 
MS-SQL<->Postgres syncPlease reply to all when replying on the 
list... On 7/10/06, Kevin Bednar <[EMAIL PROTECTED] 
> wrote:  
  Thanks Aron. What I'm actually trying to do is this: 
  Postgress in physical store, being used by POS system 
as the back end. MS-SQL being used on web server by ecommerce 
system.   Table structures are different of 
course, but some common fields. What I want to do is when an item is sold in 
the store, update the quantity field for that sku number on the web site and 
vice versa. Only 2 fields basically need to be updated on each side, the SKU 
number and quantity. This is to keep the product table in sync and try to 
avoid selling product that isnt in stock and setting a flag on the web 
system stating such. Thanks for your help. 
 For something this simple you are probably better off 
doing some custom coding.If you have the ability to modify the 
databases, I would recommend putting a trigger on each database so when 
there is a product sold, that sale is recorded in a temp table (which serves 
as a queue of data that needs to be synched).  Then have a process read 
from these temp tables and feed the data back to the other database.  
Of course, I am assuming you have full control to change the databases - 
some vendors do not allow that. You may be able to connect the 
databases - MS SQL Server will definitely allow you to connect via ODBC to 
another database and feed data back and forth.  I think there are add 
on modules for PostgreSQL but I have not tried to have PostgreSQL talk to 
other databases before. -Aaron

Re: [SQL] MS-SQL<->Postgres sync

[Richard Broersma Jr]

> On 7/10/06, Kevin Bednar <[EMAIL PROTECTED]> wrote:
> >
> >  Thanks Aron. What I'm actually trying to do is this:
> >
> > Postgress in physical store, being used by POS system as the back end.
> > MS-SQL being used on web server by ecommerce system.
> >
> > Table structures are different of course, but some common fields. What I
> > want to do is when an item is sold in the store, update the quantity field
> > for that sku number on the web site and vice versa. Only 2 fields basically
> > need to be updated on each side, the SKU number and quantity. This is to
> > keep the product table in sync and try to avoid selling product that isnt in
> > stock and setting a flag on the web system stating such. Thanks for your
> > help.
> >
> 
> 
> For something this simple you are probably better off doing some custom
> coding.
> 
> If you have the ability to modify the databases, I would recommend putting a
> trigger on each database so when there is a product sold, that sale is
> recorded in a temp table (which serves as a queue of data that needs to be
> synched).  Then have a process read from these temp tables and feed the data
> back to the other database.  Of course, I am assuming you have full control
> to change the databases - some vendors do not allow that.
> 
> You may be able to connect the databases - MS SQL Server will definitely
> allow you to connect via ODBC to another database and feed data back and
> forth.  I think there are add on modules for PostgreSQL but I have not tried
> to have PostgreSQL talk to other databases before.

I am not sure if this applys directly to the problem here, but this link my be 
useful also.

http://archives.postgresql.org/pgsql-general/2006-07/msg00298.php

Regards,

Richard Broersma Jr.

---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] Can function results be used in WHERE?

[Bryce Nesbitt]

I have a function, the results of which seem to apply to ORDER BY and
HAVING, but not to WHERE.  Is this expected?



-- Return distance in some mystery units (TODO: convert to miles or
kilometers)
CREATE FUNCTION calculate_distance(double precision, double precision,
double precision, double precision) RETURNS double precision
AS '
BEGIN
RETURN (3963 * acos( sin($1/57.2958) * sin($3/57.2958) +
cos($1/57.2958) * cos($3/57.2958) * cos($4/57.2958 - $2/57.2958) ));
END;
'
LANGUAGE plpgsql;



demo=# select
pod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) from
eg_pod where 4 > 5::double precision order by 4 limit 10;
 pod_code |lat| lon | calculate_distance
--+---+-+
   44 | 0 |   0 |  0
   45 | 0 |   0 |  0
   69 | 37.789629 | -122.422082 |  0
   51 | 37.788166 | -122.421488 |  0.106273303754946
   71 | 37.794228 | -122.421382 |  0.320393524437476
   73 | 37.787878 | -122.411644 |  0.583267064983836
   37 | 37.791736 | -122.411604 |  0.590977027054446
   46 | 37.784929 | -122.412782 |  0.603416307249032
   50 | 37.780329 | -122.418482 |  0.672685350683496
   30 | 37.780419 | -122.417764 |  0.679355355047995
(10 rows)

sdemo=# select
pod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) from
eg_pod having calculate_distance(lat,lon,37.789629,-122.422082) > 5
order by 4;
 pod_code |lat| lon | calculate_distance
--+---+-+
   21 | 37.710581 | -122.468864 |   6.03655070159813
   77 | 37.805427 |  -122.29528 |   7.01595024232628
   29 | 37.802684 | -122.275976 |8.0364304687727
   12 | 37.806133 | -122.273827 |   8.18282157050301
   23 | 37.797327 |  -122.26598 |   8.54878571904839
   57 | 37.829592 | -122.266347 |   8.94791199923289
   35 | 37.809327 |  -122.25448 |   9.26077996779577
   47 | 37.851957 | -122.270376 |   9.34292370436932



demo=# select version();
  version
---
 PostgreSQL 7.4.12 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-20)
(1 row)


-- 

Visit http://www.obviously.com/


---(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] MS-SQL<->Postgres sync

[Scott Marlowe]

Look at slony.

On Mon, 2006-07-10 at 11:06, Forums @ Existanze wrote:
>  
> We are looking for the exact thing but with two PostgreSQL databases
>  
> 
> __
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Kevin
> Bednar
> Sent: 10 July 2006 18:28
> To: pgsql-sql@postgresql.org
> Subject: [SQL] MS-SQL<->Postgres sync
> 
> 
> Looking to keep 2 databases in sync, at least semi-realtime if
> possible, although running a batch update every x mins
> wouldn't be out of the question. One db is postgres and the
> other is ms-sql. It's to keep inventory in sync from 2
> seperate locations, one being a brick and mortar store and the
> other an ecommerce system for a web site. Any and all help is
> appreciated since I can't find much of anything on syncing
> these 2 database systems!
>  
> Kevin Bednar
> Systems Support
> Stockwell Design Group
> http://www.stockwelldesigngroup.com
> [EMAIL PROTECTED]
> 

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] MS-SQL<->Postgres sync

[Kevin Bednar]

  Took 
a quick glance but were running windows on both sides and it didnt look like 
that would work. Didnt look real hard though.   
Kevin     -Original Message-From: 
Scott Marlowe <[EMAIL PROTECTED]>To: "Forums @ Existanze" 
<[EMAIL PROTECTED]>Cc: pgsql-sql@postgresql.orgDate: Mon, 
10 Jul 2006 13:31:35 -0500Subject: Re: [SQL] MS-SQL<->Postgres 
sync Look 
at slony.On Mon, 2006-07-10 at 11:06, Forums @ Existanze wrote:
>  > We are looking for the exact thing but with two 
PostgreSQL databases>  >         
>         
__> 
        From: [EMAIL PROTECTED]> 
        [mailto:[EMAIL PROTECTED] On 
Behalf Of Kevin>         Bednar>   
      Sent: 10 July 2006 18:28>       
  To: pgsql-sql@postgresql.org>         
Subject: [SQL] MS-SQL<->Postgres sync>       
  >         >       
  Looking to keep 2 databases in sync, at least semi-realtime if
>         possible, although running a batch update 
every x mins>         wouldn't be out of the 
question. One db is postgres and the>         
other is ms-sql. It's to keep inventory in sync from 2>     
    seperate locations, one being a brick and mortar store and 
the>         other an ecommerce system for a web 
site. Any and all help is>         appreciated 
since I can't find much of anything on syncing>       
  these 2 database systems!>         
 >         Kevin Bednar>   
      Systems Support>         
Stockwell Design Group>         
http://www.stockwelldesigngroup.com>         
[EMAIL PROTECTED]>         
---(end of broadcast)---
TIP 4: Have you searched our list archives?      
         http://archives.postgresql.org

Re: [SQL] MS-SQL<->Postgres sync

[Scott Marlowe]

On Mon, 2006-07-10 at 11:25, Andrew Sullivan wrote:
> On Mon, Jul 10, 2006 at 11:27:52AM -0400, Kevin Bednar wrote:
> > Looking to keep 2 databases in sync, at least semi-realtime if possible, 
> > although running a batch update every x mins wouldn't be out of the 
> > question. One db is postgres and the other is ms-sql. It's to keep 
> > inventory 
> > in sync from 2 seperate locations, one being a brick and mortar store and 
> > the other an ecommerce system for a web site. Any and all help is 
> > appreciated since I can't find much of anything on syncing these 2 database 
> > systems!
> 
> I don't think there's actually any product that does this right now,
> unless you're willing to use some sort of statement replica system
> (which means you have to deal with failures in one database and not
> another).  Is the idea that this is multi-master?

I wonder if it would be possible to write a set of triggers for MSSQL
that would allow you to run slony daemons that connected to it and a
postgresql server and did replication.   Just mad scientisting for a
second.

---(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] SELECT substring with regex

[Alvaro Herrera]

T E Schmitz wrote:

> valid entries would be:
> "28mm F2.8" (prime lens)
> "30-70mm F4" (zoom lens)
> "30-70mm F2.8" (zoom lens)
> "30-100mm F4.5-5.6" (zoom lens with variable speed)

I think you already got all the help you needed, but I want to point out
that the Nikon 18-70mm that was used to take this picture

http://people.planetpostgresql.org/mha/index.php?/archives/97-Conference-is-overhtml

seems to have a lot of aberration (I don't know the technical term,
sorry).  Notice that grid on the floor?  It looked very straight on the
real life.  I'd expect a bit of curvature but I think that's too much.

Sorry for the offtopic :-)

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(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] Can function results be used in WHERE?

[Aaron Bono]

On 7/10/06, Bryce Nesbitt <[EMAIL PROTECTED]> wrote:
I have a function, the results of which seem to apply to ORDER BY andHAVING, but not to WHERE.  Is this expected?-- Return distance in some mystery units (TODO: convert to miles orkilometers)CREATE FUNCTION calculate_distance(double precision, double precision,
double precision, double precision) RETURNS double precisionAS 'BEGINRETURN (3963 * acos( sin($1/57.2958) * sin($3/57.2958) +cos($1/57.2958) * cos($3/57.2958) * cos($4/57.2958 - $2/57.2958) ));
END;'LANGUAGE plpgsql;demo=# selectpod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) fromeg_pod where 4 > 5::double precision order by 4 limit 10; pod_code |lat| lon | calculate_distance
--+---+-+   44 | 0 |   0 |  0   45 | 0 |   0 |  0   69 | 37.789629 | -122.422082 |  0
   51 | 37.788166 | -122.421488 |  0.106273303754946   71 | 37.794228 | -122.421382 |  0.320393524437476   73 | 37.787878 | -122.411644 |  0.583267064983836   37 | 37.791736 | -122.411604 |  
0.590977027054446   46 | 37.784929 | -122.412782 |  0.603416307249032   50 | 37.780329 | -122.418482 |  0.672685350683496   30 | 37.780419 | -122.417764 |  0.679355355047995(10 rows)sdemo=# select
pod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) fromeg_pod having calculate_distance(lat,lon,37.789629,-122.422082) > 5order by 4; pod_code |lat| lon | calculate_distance
--+---+-+   21 | 37.710581 | -122.468864 |   6.03655070159813   77 | 37.805427 |  -122.29528 |   7.01595024232628   29 | 37.802684 | -122.275976 |
8.0364304687727   12 | 37.806133 | -122.273827 |   8.18282157050301   23 | 37.797327 |  -122.26598 |   8.54878571904839   57 | 37.829592 | -122.266347 |   8.94791199923289   35 | 37.809327 |  -
122.25448 |   9.26077996779577   47 | 37.851957 | -122.270376 |   9.34292370436932demo=# select version();  version---
 PostgreSQL 7.4.12 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.320030502 (Red Hat Linux 3.2.3-20)(1 row)First I recommend making your function IMMUTABLE since, given the same arguments, it gives the same result - this will allow PostgreSQL to optimize the function call and cache the results.  Then, don't use "4", use "calculate_distance(lat,lon,
37.789629,-122.422082)".  That use is very ambiguous and subject to breaking if you change the columns in your select.  It may also be the reason you have a problem though I don't use that syntax so cannot be sure.
The only difference between HAVING and WHERE is that WHERE occurs before a GROUP BY and HAVING occurs after.  Since you have no GROUP BY there should be no difference in the queries.  The only other difference is the "4 > 5::double precision" so that is where I would start.
-Aaron

Re: [SQL] MS-SQL<->Postgres sync

[Alvaro Herrera]

Kevin Bednar wrote:
> Took a quick glance but were running windows on both sides and it didnt look 
> like that would work. Didnt look real hard though.

I think the to-be-released stuff is supposed to have Windows support.
They were supposed to release Real Soon Now some time ago, so I guess
they are going to release Real Soon Now.

Maybe you could help them with the beta testing if that's what they
need.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] MS-SQL<->Postgres sync

[Scott Marlowe]

I think there's a version with windows support due out soon.  Not sure,
since I don't run windows...  just remember seeing it mentioned.

On Mon, 2006-07-10 at 13:59, Kevin Bednar wrote:
> Took a quick glance but were running windows on both sides and it
> didnt look like that would work. Didnt look real hard though.
>  
> Kevin
>  
>  
> -Original Message-
> From: Scott Marlowe <[EMAIL PROTECTED]>
> To: "Forums @ Existanze" <[EMAIL PROTECTED]>
> Cc: pgsql-sql@postgresql.org
> Date: Mon, 10 Jul 2006 13:31:35 -0500
> Subject: Re: [SQL] MS-SQL<->Postgres sync
> 
> Look at slony.
> 
> On Mon, 2006-07-10 at 11:06, Forums @ Existanze wrote:
> >  
> > We are looking for the exact thing but with two PostgreSQL
> databases
> >  
> > 
> >
> __
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] On Behalf Of
> Kevin
> > Bednar
> > Sent: 10 July 2006 18:28
> > To: pgsql-sql@postgresql.org
> > Subject: [SQL] MS-SQL<->Postgres sync
> > 
> > 
> > Looking to keep 2 databases in sync, at least
> semi-realtime if
> > possible, although running a batch update every x
> mins
> > wouldn't be out of the question. One db is postgres
> and the
> > other is ms-sql. It's to keep inventory in sync from
> 2
> > seperate locations, one being a brick and mortar
> store and the
> > other an ecommerce system for a web site. Any and
> all help is
> > appreciated since I can't find much of anything on
> syncing
> > these 2 database systems!
> >  
> > Kevin Bednar
> > Systems Support
> > Stockwell Design Group
> > http://www.stockwelldesigngroup.com
> > [EMAIL PROTECTED]
> > 
> 
> ---(end of
> broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org
> 

---(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] MS-SQL<->Postgres sync

[Dave Page]

 

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe
> Sent: 10 July 2006 20:03
> To: Kevin Bednar
> Cc: Forums @ Existanze; pgsql-sql@postgresql.org
> Subject: Re: [SQL] MS-SQL<->Postgres sync
> 
> I think there's a version with windows support due out soon.  
> Not sure,
> since I don't run windows...  just remember seeing it mentioned.

Yes, full support for windows has been written already for the upcoming
1.2 release.

Regards, Dave

---(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] Can function results be used in WHERE?

[Bryce Nesbitt]

Aaron Bono wrote:
> First I recommend making your function IMMUTABLE since, given the same
> arguments, it gives the same result - this will allow PostgreSQL to
> optimize the function call and cache the results. 
Will do!

> Then, don't use "4", use "calculate_distance(lat,lon,
> 37.789629,-122.422082)".  That use is very ambiguous and subject to
> breaking if you change the columns in your select.  It may also be the
> reason you have a problem though I don't use that syntax so cannot be
> sure.
I think it is ugly also, but no other syntax seems to work:

stage=# select
pod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) as
dist from eg_pod where dist < 1 order by dist desc limit 10;
ERROR:  column "dist" does not exist

stage=# select
pod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) as
dist from eg_pod where 4 < 1 order by dist desc limit 10;

 pod_code |lat| lon |   dist
--+---+-+--
   20 | 1 |   1 | 7962.56837300854
   36 |  37.39424 | -122.077673 | 33.2296275931303
   45 | 37.426929 | -122.161922 | 28.8542985664155
   44 | 37.422813 | -122.172403 | 28.8253772580912
   22 | 37.444638 | -122.156875 | 27.9378660315883
   34 | 37.875915 | -122.257427 | 10.7947710258918
   81 | 37.903325 |  -122.29963 |  10.323500058406
   33 | 37.868001 | -122.261818 | 10.2977353566856
   17 | 37.873002 |  -122.26968 | 10.1277713471574
   14 | 37.869574 | -122.267937 | 10.0742861708283
(10 rows)


> The only difference between HAVING and WHERE is that WHERE occurs
> before a GROUP BY and HAVING occurs after.  Since you have no GROUP BY
> there should be no difference in the queries.  The only other
> difference is the "4 > 5::double precision" so that is where I would
> start.
WHERE does nothing in my example.
HAVING filters the results according to distance.
So there's got to be more to it.


-- 

Visit http://www.obviously.com/


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Can function results be used in WHERE?

[Aaron Bono]

On 7/10/06, Bryce Nesbitt <[EMAIL PROTECTED]> wrote:
I think it is ugly also, but no other syntax seems to work:stage=# selectpod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) asdist from eg_pod where dist < 1 order by dist desc limit 10;
ERROR:  column "dist" does not existSELECT   pod_code,   lat,   lon,   calculate_distance(lat,lon,37.789629,-122.422082) as dist FROM eg_pod WHERE calculate_distance(lat,lon,
37.789629,-122.422082) < 1 ORDER BY calculate_distance(lat,lon,37.789629,-122.422082) desc limit 10; 

Re: [SQL] Can function results be used in WHERE?

[Bryce Nesbitt]

Aaron Bono wrote:
>
>
> On 7/10/06, *Bryce Nesbitt* <[EMAIL PROTECTED]
> > wrote:
>
>
> I think it is ugly also, but no other syntax seems to work:
>
> stage=# select
> pod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) as
> dist from eg_pod where dist < 1 order by dist desc limit 10;
> ERROR:  column "dist" does not exist
>
>
> SELECT
>pod_code,
>lat,
>lon,
>calculate_distance(lat,lon,37.789629,-122.422082) as dist
> FROM eg_pod
> WHERE calculate_distance(lat,lon, 37.789629,-122.422082) < 1
> ORDER BY calculate_distance(lat,lon,37.789629,-122.422082) desc limit 10;
Yep, that works.  I guess with IMMUTABLE it's even effecient.
But I have to pass 6 arguments, not 2.  Is there a way to make it look
cleaner?
I had expected using the column label (e.g. "dist") to work with WHERE,
just as it does with ORDER BY.

   -Bryce


-- 

Visit http://www.obviously.com/


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Select Maths

[Phillip Smith]

Example:

Funcation pqty(stock.code) calculates a value of 0 for a particular product. This fails the
last CASE that makes sure the pqty() value is greater than
our Usage Rate *
Review Cycle – in this case is
3. But that is less than our Minimum Order Qty (First CASE) and not a multiple
of our Box Qty (Second CASE)

Another example could be
that pqty() calculates less than the Minimum Order Qty (fails first CASE) so
we raise it to the Minimum Order Qty, but that new value could fail either or
both of the second CASE’s.

Minimum Order Qty
= stock.purchase_unit
Box Qty = stock.box_qty

I guess
a better way to word it is that because pqty() returns a calculated
value each time and I can’t take that value and assign it to a variable,
then use that variable. If I was writing VB or similar I’d want something
like:

intPurchaseQty = pqty(stock.code)

CASE  WHEN intPurchaseQty < stock.purchase_unit THEN
intPurchaseQty = stock.purchase_unit

WHEN MOD(intPurchaseQty, stock.box_qty) > 0 THEN intPurchaseQty
= stock.box_qty * ROUND(CAST(intPurchaseQty AS DOUBLE PRECISION) /
stock.box_qty)

WHEN intPurchaseQty < (urate(stock.code) *
creditors.review_cycle) THEN intPurchaseQty = urate(stock.code) * creditors.review_cycle

END

COLUMN = intPurchaseQty AS
"pqty",

I hope
that makes it a lighter shade of mud!!

 

-Original
Message-
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Aaron Bono
Sent: Tuesday,
 11 July 2006 02:36
To: Phillip Smith
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Select Maths






Can you provide example values and show where it is and is not working?  I
am not quite sure what you are trying to do here. 

-Aaron 



 







***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] MS-SQL<->Postgres sync

[Chris Browne]

[EMAIL PROTECTED] (Scott Marlowe) writes:
> On Mon, 2006-07-10 at 11:25, Andrew Sullivan wrote:
>> On Mon, Jul 10, 2006 at 11:27:52AM -0400, Kevin Bednar wrote:
>> > Looking to keep 2 databases in sync, at least semi-realtime if possible, 
>> > although running a batch update every x mins wouldn't be out of the 
>> > question. One db is postgres and the other is ms-sql. It's to keep 
>> > inventory 
>> > in sync from 2 seperate locations, one being a brick and mortar store and 
>> > the other an ecommerce system for a web site. Any and all help is 
>> > appreciated since I can't find much of anything on syncing these 2 
>> > database 
>> > systems!
>> 
>> I don't think there's actually any product that does this right now,
>> unless you're willing to use some sort of statement replica system
>> (which means you have to deal with failures in one database and not
>> another).  Is the idea that this is multi-master?
>
> I wonder if it would be possible to write a set of triggers for MSSQL
> that would allow you to run slony daemons that connected to it and a
> postgresql server and did replication.   Just mad scientisting for a
> second.

Someone went through the "mental gyrations" and figured it ought to be
able to work for Oracle...

It otta be able to work; the only thing is that I would think the only
nodes that could be origins would be PostgreSQL nodes, because the way
the data extraction scheme works is very much attuned to PostgreSQL's
MVCC model...
-- 
output = ("cbbrowne" "@" "cbbrowne.com")
http://cbbrowne.com/info/lisp.html
"I don't plan to maintain it, just to install it." -- Richard M. Stallman

---(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] Can function results be used in WHERE?

[Aaron Bono]

On 7/10/06, Bryce Nesbitt <[EMAIL PROTECTED]> wrote:
Aaron Bono wrote:>>> On 7/10/06, *Bryce Nesbitt* <[EMAIL PROTECTED]> [EMAIL PROTECTED]>> wrote:
>>> I think it is ugly also, but no other syntax seems to work:>> stage=# select> pod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) as> dist from eg_pod where dist < 1 order by dist desc limit 10;
> ERROR:  column "dist" does not exist>>> SELECT>pod_code,>lat,>lon,>calculate_distance(lat,lon,37.789629,-122.422082) as dist> FROM eg_pod
> WHERE calculate_distance(lat,lon, 37.789629,-122.422082) < 1> ORDER BY calculate_distance(lat,lon,37.789629,-122.422082) desc limit 10;Yep, that works.  I guess with IMMUTABLE it's even effecient.
But I have to pass 6 arguments, not 2.  Is there a way to make it lookcleaner?I had expected using the column label (e.g. "dist") to work with WHERE,just as it does with ORDER BY.
You can also try

SELECT

   pod_code,   lat,   lon,   dist 
FROM (
   SELECT
      pod_code,      lat,      lon,      calculate_distance(lat,lon,37.789629,-122.422082) as dist 
   FROM eg_pod 
) eg_prodWHERE dist < 1 ORDER BY dist desc limit 10; 

If the 37.789629 and -122.422082 are static values you
can create a view for it.  Otherwise you can create a function or
stored procedure that takes 2 arguments and returns the results of the
subquery.

Just some options.  Not sure which you would prefer.  I am sure there are more ways to do it.

-Aaron Bono

 

Re: [SQL] Can function results be used in WHERE?

[Tom Lane]

Bryce Nesbitt <[EMAIL PROTECTED]> writes:
> stage=# select
> pod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) as
> dist from eg_pod where 4 < 1 order by dist desc limit 10;
> [ allegedly returns 10 rows ]

I'm having a real hard time believing any of this: "WHERE 4 < 1" is
a constant FALSE condition and cannot possibly return any rows ...
unless basic integer arithmetic is broken on your platform?  4 is
not less than 1.  I think you're showing us a heavily edited version
of your query rather than what you actually typed.

But as far as the underlying misconception goes, you seem to think that
"4" in the WHERE clause might somehow be taken as referring to the
fourth SELECT result column (why you don't think that the "1" would
likewise refer to the first result column isn't clear).  This is not so.
"4" means the numeric value four.  There is a special case in ORDER BY
and GROUP BY that an argument consisting of a simple integer literal
constant will be taken as a reference to an output column.  This is an
ugly kluge IMHO, but it's somewhat defensible on the grounds that
neither ordering nor grouping by a simple constant has any possible
real-world use; so the special case doesn't break anything of interest.
This would certainly not be so if we were to randomly replace integer
constants in general WHERE conditions with non-constant values.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Select Maths

[Aaron Bono]

On 7/10/06, Phillip Smith <[EMAIL PROTECTED]> wrote:
















Example:

Funcation 
pqty(stock.code) calculates a value of 0 for a particular product. This fails the
last CASE that makes sure the pqty()
 value is greater than
our Usage Rate *
Review Cycle – in this case is
3. But that is less than our Minimum Order Qty (First CASE) and not a multiple
of our Box Qty (Second CASE)

Another example could be
that pqty()
 calculates less than the Minimum Order Qty (fails first CASE) so
we raise it to the Minimum Order Qty, but that new value could fail either or
both of the second CASE's.

Minimum Order Qty
= stock.purchase_unit
Box Qty = stock.box_qty

I guess
a better way to word it is that because pqty()
 returns a calculated
value each time and I can't take that value and assign it to a variable,
then use that variable. If I was writing VB or similar I'd want something
like:

intPurchaseQty
 = pqty(stock.code)

CASE  WHEN intPurchaseQty < stock.purchase_unit THEN
intPurchaseQty = stock.purchase_unit

WHEN MOD(intPurchaseQty, stock.box_qty) > 0 THEN intPurchaseQty

= stock.box_qty * ROUND(CAST(intPurchaseQty AS DOUBLE PRECISION) /
stock.box_qty)

WHEN intPurchaseQty < (urate(stock.code) *
creditors.review_cycle) THEN intPurchaseQty = urate(stock.code) * creditors.review_cycle

END

COLUMN = intPurchaseQty AS
"pqty",

I hope
that makes it a lighter shade of mud!!Why wouldn't you be able to do this in a function?  Pass in stock.code, stock.purchase_unit, stock.box_qty and creditors.review_cycle
.  You can then use variables in the function, right?-Aaron

Re: [SQL] Select Maths

[Phillip Smith]

It ties back to my other post about the “FLAGS”
column – I need to be able to find out if the original pqty() calculation has
needed to be modified.

 

I guess what you’re saying is to have 2
functions – one that calculates the figure I have at the moment, then a second
to return the adjusted figure?

 

Then I can use the first function when I’m
working out what the flags need to be, then the second to give the actual
adjusted figure….

 

-p

 

-Original
Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Aaron Bono
Sent: Tuesday,
 11 July 2006 13:42
To: Phillip Smith
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Select Maths


Why wouldn't you be able to do this in a function?  Pass in stock.code,
stock.purchase_unit, stock.box_qty and creditors.review_cycle .  You can
then use variables in the function, right?

-Aaron 





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Can function results be used in WHERE?

[Aaron Bono]

On 7/10/06, Tom Lane <[EMAIL PROTECTED]> wrote:
But as far as the underlying misconception goes, you seem to think that"4" in the WHERE clause might somehow be taken as referring to thefourth SELECT result column (why you don't think that the "1" would
likewise refer to the first result column isn't clear).  This is not so."4" means the numeric value four.  There is a special case in ORDER BYand GROUP BY that an argument consisting of a simple integer literal
constant will be taken as a reference to an output column.  This is anugly kluge IMHO, but it's somewhat defensible on the grounds thatneither ordering nor grouping by a simple constant has any possiblereal-world use; so the special case doesn't break anything of interest.
This would certainly not be so if we were to randomly replace integerconstants in general WHERE conditions with non-constant values.I agree whole heartedly with Tom, using the number in the ORDER BY is ugly and not recommended.  Using column names is much easier to read and is much more maintainable by team members.  I have to admit the 4 < 1 did confuse me at first.
-Aaron Bono