Re: [SQL] exclusion constraint for ranges of IP
On 22/08/2011, at 01:19, Harald Fuchs wrote: > In article , > Herouth Maoz writes: > >> Hi, >> I'm designing a new database. One of the table contains allowed IP ranges >> for a customer (Fields: customer_id, from_ip, to_ip) which is intended to >> check - if an incoming connection's originating IP number falls within the >> range, it is identified as a particular customer. > >> Naturally, I'd like to have constraints on the table that prevent entering >> of ip ranges that overlap. Is there a way to do that with exclusion >> constraints? Or do I have to define a new type for this? > > This "new type" already exists: ip4r, which can be found in pgfoundry. > With it you can do > > CREATE TABLE mytbl ( > iprange ip4r NOT NULL, > ..., > CONSTRAINT range_check CHECK ((NOT overlap(iprange))) > ); Thank you. I assume you can't use a CHECK constraint for between-rows constraints. Wouldn't this be CONSTRAINT EXCLUDE ( iprange WITH && ) ? Basically, though, I'm not too happy about using compound types - that's why I asked if I have to. I'm not sure what my application will have to send and what it will receive when querying a compound type. I use PHP/ZF. I have just now posted a question on the pgsql-php list about this. I suspect I'll be getting a string which I'll have to parse, which would make the application more complicated to read and understand. Herouth -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] exclusion constraint for ranges of IP
Hi, you can do the identification of customer by ip in many ways. IMHO, first of all, you have to put the allowed IPs into your table. The simpler way is to write all IPs allowed, of course. The simpler way to do range check is to have 2 columns in table, IP-range-starts and IP-range-ends, so the SQL could be easy using every data types. I prefer to use string data type and not compound ip4r type because can check using reg.exp. in SQL select, writing IPs range like 1.2.3.100-1.2.3.150 or just single 2.3.4.5. When I did something like that, I wrote IP in this form : 001002003100 and I use the two columns of range-starts and range-ends, so it became very easy (and I think very performant) doing check even with large table. The worse way is to remand check to PHP, because you have to load entire table during select and manually select target row there. I hope these suggests help 2011/8/22 Herouth Maoz > > On 22/08/2011, at 01:19, Harald Fuchs wrote: > > > In article , > > Herouth Maoz writes: > > > >> Hi, > >> I'm designing a new database. One of the table contains allowed IP > ranges for a customer (Fields: customer_id, from_ip, to_ip) which is > intended to check - if an incoming connection's originating IP number falls > within the range, it is identified as a particular customer. > > > >> Naturally, I'd like to have constraints on the table that prevent > entering of ip ranges that overlap. Is there a way to do that with exclusion > constraints? Or do I have to define a new type for this? > > > > This "new type" already exists: ip4r, which can be found in pgfoundry. > > With it you can do > > > > CREATE TABLE mytbl ( > > iprange ip4r NOT NULL, > > ..., > > CONSTRAINT range_check CHECK ((NOT overlap(iprange))) > > ); > > > Thank you. > > I assume you can't use a CHECK constraint for between-rows constraints. > Wouldn't this be > > CONSTRAINT EXCLUDE ( iprange WITH && ) > > ? > > Basically, though, I'm not too happy about using compound types - that's > why I asked if I have to. I'm not sure what my application will have to send > and what it will receive when querying a compound type. I use PHP/ZF. I have > just now posted a question on the pgsql-php list about this. I suspect I'll > be getting a string which I'll have to parse, which would make the > application more complicated to read and understand. > > Herouth > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Simone
Re: [SQL] exclusion constraint for ranges of IP
On 2011-08-21, Herouth Maoz wrote: > Hi, > > I'm designing a new database. One of the table contains allowed IP ranges for > a customer (Fields: customer_id, from_ip, to_ip) which is intended to check - > if an incoming connection's originating IP number falls within the range, it > is identified as a particular customer. > > Naturally, I'd like to have constraints on the table that prevent entering of > ip ranges that overlap. Is there a way to do that with exclusion constraints? > Or do I have to define a new type for this? > > Herouth if you can use CIDR instead of ranges it should be relatively simple -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] exclusion constraint for ranges of IP
Hi Herouth,
I think you are right about exclusion...
If you are getting 'string' I think then command would be:
INSERT INTO customer_ip_range(cutomer_id, ip4r) VALUES('customeridstring',
ip4r('iprangestring'))
Kind Regards,
Misa
2011/8/22 Herouth Maoz
>
> On 22/08/2011, at 01:19, Harald Fuchs wrote:
>
> > In article ,
> > Herouth Maoz writes:
> >
> >> Hi,
> >> I'm designing a new database. One of the table contains allowed IP
> ranges for a customer (Fields: customer_id, from_ip, to_ip) which is
> intended to check - if an incoming connection's originating IP number falls
> within the range, it is identified as a particular customer.
> >
> >> Naturally, I'd like to have constraints on the table that prevent
> entering of ip ranges that overlap. Is there a way to do that with exclusion
> constraints? Or do I have to define a new type for this?
> >
> > This "new type" already exists: ip4r, which can be found in pgfoundry.
> > With it you can do
> >
> > CREATE TABLE mytbl (
> > iprange ip4r NOT NULL,
> > ...,
> > CONSTRAINT range_check CHECK ((NOT overlap(iprange)))
> > );
>
>
> Thank you.
>
> I assume you can't use a CHECK constraint for between-rows constraints.
> Wouldn't this be
>
> CONSTRAINT EXCLUDE ( iprange WITH && )
>
> ?
>
> Basically, though, I'm not too happy about using compound types - that's
> why I asked if I have to. I'm not sure what my application will have to send
> and what it will receive when querying a compound type. I use PHP/ZF. I have
> just now posted a question on the pgsql-php list about this. I suspect I'll
> be getting a string which I'll have to parse, which would make the
> application more complicated to read and understand.
>
> Herouth
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
[SQL] sorting months according to fiscal year
Hi
I have to rearrange the months according to the fiscal year i.e from April
to march and use the same in the order by clause of a query.
I have written the following postgresql function for the same, but to_number
is returning an error.
Can you please tell me where i'm going wrong?
Instead of the function to_number can you suggest any other function that
will convert a particular month to its corresponding month number(ex:
april=4 or jan=1)
CREATE FUNCTION sort_month(to_number('April','MM')) RETURNS numeric AS $$
DECLARE
BEGIN
CASE WHEN 4 THEN 1
WHEN 5 THEN 2
WHEN 6 THEN 3
WHEN 7 THEN 4
WHEN 8 THEN 5
WHEN 9 THEN 6
WHEN 10 THEN 7
WHEN 11 THEN 8
WHEN 12 THEN 9
WHEN 1 THEN 10
WHEN 2 THEN 11
WHEN 3 THEN 12
ELSE 0
END;
$$ LANGUAGE plpgsql;
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/sorting-months-according-to-fiscal-year-tp4723409p4723409.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sorting months according to fiscal year
Hello
2011/8/22 Enzen user :
> Hi
> I have to rearrange the months according to the fiscal year i.e from April
> to march and use the same in the order by clause of a query.
> I have written the following postgresql function for the same, but to_number
> is returning an error.
> Can you please tell me where i'm going wrong?
> Instead of the function to_number can you suggest any other function that
> will convert a particular month to its corresponding month number(ex:
> april=4 or jan=1)
>
>
> CREATE FUNCTION sort_month(to_number('April','MM')) RETURNS numeric AS $$
you cannot use to_number function inside param's list
> DECLARE
>
> BEGIN
>
> CASE WHEN 4 THEN 1
> WHEN 5 THEN 2
> WHEN 6 THEN 3
> WHEN 7 THEN 4
> WHEN 8 THEN 5
> WHEN 9 THEN 6
> WHEN 10 THEN 7
> WHEN 11 THEN 8
> WHEN 12 THEN 9
> WHEN 1 THEN 10
> WHEN 2 THEN 11
> WHEN 3 THEN 12
> ELSE 0
> END;
>
>
>
> $$ LANGUAGE plpgsql;
>
CREATE OR REPLACE FUNCTION sort_month(int)
RETURNS int -- is wrong to use numeric here
AS $$
...
$$ LANGUAGE sql;
SELECT sort_ month(to_nuber(...))
Regards
Pavel Stehule
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/sorting-months-according-to-fiscal-year-tp4723409p4723409.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] exclusion constraint for ranges of IP
In article <[email protected]>, Herouth Maoz writes: > On 22/08/2011, at 01:19, Harald Fuchs wrote: >> In article , >> Herouth Maoz writes: >> >>> Hi, >>> I'm designing a new database. One of the table contains allowed IP ranges >>> for a customer (Fields: customer_id, from_ip, to_ip) which is intended to >>> check - if an incoming connection's originating IP number falls within the >>> range, it is identified as a particular customer. >> >>> Naturally, I'd like to have constraints on the table that prevent entering >>> of ip ranges that overlap. Is there a way to do that with exclusion >>> constraints? Or do I have to define a new type for this? >> >> This "new type" already exists: ip4r, which can be found in pgfoundry. >> With it you can do >> >> CREATE TABLE mytbl ( >> iprange ip4r NOT NULL, >> ..., >> CONSTRAINT range_check CHECK ((NOT overlap(iprange))) >> ); > Thank you. > I assume you can't use a CHECK constraint for between-rows constraints. > Wouldn't this be > CONSTRAINT EXCLUDE ( iprange WITH && ) > ? You're right. In the old PostgreSQL version I had to use I defined a helper function CREATE FUNCTION overlap(ip4r) RETURNS boolean LANGUAGE sql AS $_$ SELECT count(*) > 0 FROM mytbl WHERE iprange != $1 AND iprange && $1 $_$; for the CHECK CONSTRAINT, but in more recent PostgreSQL versions you can just say EXCLUDE (iprange WITH &&) (without CONSTRAINT). -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Confused about writing this stored procedure/method.
Hi All, I'm trying to write a stored procedure /function to re-order a set of calendar months.I have a set of calendar months stored from January to December in my tables. And as of now when I do order by on this column , the data is ordered alphabetically , starting April, august etc. and so on I want to order these months starting from April through March in order to sync with the financial calendar . I'm trying to write a stored procedure to do the same (I'm not aware of any other method that Postgres offers this reordering , if there's any , please do let me know!). I intend pass the number of the month(say 1 for January , 2 for February etc>) as the parameter to this method and return a number that corresponds to the index of the month in the new order , say I pass 8 for August , I return 11 , in order to get the index of August in he financial year calendar somewhat like this CREATE FUNCTION getNMonth(to_number(domain.description,'MM') int as num) RETURNS int AS $$ DECLARE qty int; BEGIN SELECT qty, CASE WHEN num=4 THEN 1-- Set August to the first month etc. WHEN num=5 THEN 2 ELSE 'other' END FROM DOMAIN; RETURN qty; END; $$ LANGUAGE plpgsql; However, this throws a syntax error on to_number. This my first attempt at a stored procedure in Postgres .Thank you for your time. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Confused-about-writing-this-stored-procedure-method-tp4723656p4723656.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Confused about writing this stored procedure/method.
Hello 2011/8/22 JavaNoobie : > Hi All, > I'm trying to write a stored procedure /function to re-order a set of > calendar months.I have a set of calendar months stored from January to > December in my tables. And as of now when I do order by on this column , > the data is ordered alphabetically , starting April, august etc. and so on > I want to order these months starting from April through March in order to > sync with the financial calendar . I'm trying to write a stored procedure to > do the same (I'm not aware of any other method that Postgres offers this > reordering , if there's any , please do let me know!). > I intend pass the number of the month(say 1 for January , 2 for February > etc>) as the parameter to this method and return a number that corresponds > to the index of the month in the new order , say I pass 8 for August , I > return 11 , in order to get the index of August in he financial year > calendar somewhat like this > > CREATE FUNCTION getNMonth(to_number(domain.description,'MM') int as num) this is wrong - you can use only a variable name and type name in parameter's list > RETURNS int AS $$ > DECLARE > qty int; > BEGIN > SELECT qty, > CASE WHEN num=4 THEN 1-- Set August to the first month etc. > WHEN num=5 THEN 2 > ELSE 'other' > END > FROM DOMAIN; probably you would to use a SELECT INTO ... > RETURN qty; > END; > $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION getNMonth(num int) RETURNS int AS $$ DECLARE qty int; BEGIN SELECT CASE num WHEN 4 THEN 1 WHEN 5 THEN 2 .. INTO qty; RETURN qty; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; try to read a documentation first, please http://www.postgresql.org/docs/9.0/interactive/plpgsql.html Regards Pavel Stehule > However, this throws a syntax error on to_number. This my first attempt at a > stored procedure in Postgres .Thank you for your time. > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Confused-about-writing-this-stored-procedure-method-tp4723656p4723656.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sorting months according to fiscal year
On 23/08/11 01:27, Enzen user wrote:
Hi
I have to rearrange the months according to the fiscal year i.e from April
to march and use the same in the order by clause of a query.
I have written the following postgresql function for the same, but to_number
is returning an error.
Can you please tell me where i'm going wrong?
Instead of the function to_number can you suggest any other function that
will convert a particular month to its corresponding month number(ex:
april=4 or jan=1)
CREATE FUNCTION sort_month(to_number('April','MM')) RETURNS numeric AS $$
DECLARE
BEGIN
CASE WHEN 4 THEN 1
WHEN 5 THEN 2
WHEN 6 THEN 3
WHEN 7 THEN 4
WHEN 8 THEN 5
WHEN 9 THEN 6
WHEN 10 THEN 7
WHEN 11 THEN 8
WHEN 12 THEN 9
WHEN 1 THEN 10
WHEN 2 THEN 11
WHEN 3 THEN 12
ELSE 0
END;
$$ LANGUAGE plpgsql;
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/sorting-months-according-to-fiscal-year-tp4723409p4723409.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
You might find the following faster...
DROP FUNCTION IF EXISTS
sort_mont
(
to_number int
) ;
CREATE FUNCTION
sort_mont
(
to_number int
)
RETURNS numeric
LANGUAGE plpgsql
AS
$$
BEGIN
RETURN 1 + (to_number + 8) % 12;
END;
$$;
SELECT sort_mont(1);
SELECT sort_mont(12);
/// output..
gavin=> \i modulus_stored_proc.sql
DROP FUNCTION
CREATE FUNCTION
sort_mont
---
10
(1 row)
sort_mont
---
9
(1 row)
gavin=>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
