[HACKERS] operator suggest interval / interval = numeric

[Ilya A. Kovalenko]

I suggest one more standard date/time operator, to divide one interval
by another with numeric (or float, for example) result.
I.e. something like that:

database=# SELECT '5400 seconds'::interval / '1 hour'::interval;

 ?column?
--
  1.5
(1 row)

Ilya A. Kovalenko


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

   http://archives.postgresql.org

Re: [HACKERS] inet increment w/ int8

[Ilya A. Kovalenko]

BM Would you modify this so it can go in /contrib or pgfoundry?  Is there
BM general interest for this?

  Actually, I suggested to do such or similar function as internal.
PostgreSQL has inet/cidr - excellent data type and good facilities to
examine and compare inet values, but has no facilities to modify
them (i.e. get-change-return operations).

  There is place for many useful operators and functions to do but
they not invented enough yet (to supplement with existing facilites
and each other).

   Only facility that has no doubt is increment/decrement inet
address value with numeric value. It nicely supplements existing
inet compare operators (that compares two addresses as numeric
values). Also, it can be used to override + and - operators
between inet and numeric values.

  I understand, that you have more important things to do, so I write
this function, to save your time.

  Maybe, it is good idea, to implement such functions separatelly as
/contrib ... for a start ...

  Live example for inet_inc() (as + operator)

(PL/pgSQL)
 -- (try to) Peek address from group pool
SELECT next INTO next_ip FROM peer_ranges WHERE
   group_id = (SELECT id FROM peer_groups WHERE name = $3) AND
   next  last
   ORDER BY first LIMIT 1;

 -- Return NULL if pool is empty
IF NOT FOUND THEN
   RAISE NOTICE ''Group address pool is empty'';
   RETURN NULL;
END IF;

 -- Update pool
UPDATE peer_ranges SET next = next_ip + 1 WHERE next = next_ip;

RETURN next_ip;

where, peer_ranges is:

CREATE TABLE peer_ranges
( group_id  bigint NOT NULL REFERENCES peer_groups (id),
  first inet   NOT NULL UNIQUE,
  next  inet   NOT NULL UNIQUE,
  last  inet   NOT NULL UNIQUE
);

Thank you,

Ilya A. Kovalenko  (mailto:[EMAIL PROTECTED])


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [HACKERS] inet increment w/ int8

[Ilya A. Kovalenko]

BM Greg Stark wrote:
 
 Bruce Momjian pgman@candle.pha.pa.us writes:
 
  am thinking we should support only inet + inet, like this:
  
 SELECT '1.2.3.4'::inet + '0.0.1.2'::inet;
 
 I don't think inet+inet makes any sense.
 
 I think inet+int4 should work by adding to the host address and overflowing 
 if
 it exceeds the network mask.
 
 Ie, 
 
 10.0.0.0/24   + 1 = 10.0.0.1/24
 10.0.0.255/24 + 1 = overflow
 
 Or
 
 10.1/16 + 1  = 10.1.0.1/16
 10.1/16 + 16384  = 10.1.64.0/16
 10.1/16 + 65536  = overflow

BM So, do not overflow?  We can do that.  Another idea Tom had was creating
BM a function that increments/decrements the address or the network portion
BM of the address, and if you increment past the non-network portion that
BM overflows too.

  Hmm, actually, you can do several functions to increase/decrease
network address with different overflow models (octet-overflow,
host part overflow, full address overflow, or without overflow
as special case), for flexibility.
  Another question, what model choose for '+/-' ...

  BTW, why 'inet + int4' (not int8), what about v6 ?

  Few words for 'inet + inet'. It's can be useful for IPv6 addresses
(because you don't have 128-bit numeric type, except, maybe, 'numeric'
one).
  But, there is another way to reach higher octets - use existing
inet_{send|receive} functions.

disclaimer text='raw ideas and thoughts'

Or invent something new like this:

-- src  index  value
FUNCTION extract_octet(inet, integer) RETURNS integer
FUNCTION extract_word (inet, integer) RETURNS int2
FUNCTION extract_dword(inet, integer) RETURNS int4
FUNCTION extract_qword(inet, integer) RETURNS int8

--src   index  value
FUNCTION replace_octet(inet, integer, integer) RETURNS inet
FUNCTION replace_word (inet, integer, int2)RETURNS inet
FUNCTION replace_dword(inet, integer, int4)RETURNS inet
FUNCTION replace_qword(inet, integer, int8)RETURNS inet

(not established with signed 'int%')

/disclaimer

Ilya A. Kovalenko


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

Re: [HACKERS] inet increment w/ int8

[Ilya A. Kovalenko]

GS I see a use case for of generating addresses based on a sequence or some
GS primary key from the database.

GS Something like

GS CREATE SEQUENCE hosts_ip_seq MAXVALUE 65536;
GS ALTER TABLE hosts ALTER ip SET DEFAULT '10.0.0.0/16'::inet + 
nextval(hosts_ip_seq')

hmm, not quite good idea - SEQUENCEs, by design, does not rollback next
value on transation rollback, so you'll have holes on address range when
other values will break some constraints or concurrent sessions
appears.




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[HACKERS] inet increment w/ int8

[Ilya A. Kovalenko]

   Greetings,

  I suggest function for inet increment w/ int8 (signed).

FUNCTION inet_inc(int, int8) RETURNS inet

Function, useful for making address pools (using also
existing inet compare functions to trap boundaries).

Notes:
  This version lets address wrap around 0-*ff boundary.
  Uses couple of non-POSIX functions - betoh64() and htobe64()
  Tested on i386 with OpenBSD 3.7
  PostgreSQL 8.0.2

-
#include sys/types.h
#include sys/socket.h
#include netinet/in.h
#include arpa/inet.h

#include postgres.h   /* general Postgres declarations */

#include fmgr.h   /* for argument/result macros */
#include utils/inet.h

Datum   inet_inc(PG_FUNCTION_ARGS);

//-- stolen from backend/utils/adt/network.c 

#define ip_family(inetptr) \
(((inet_struct *)VARDATA(inetptr))-family)
#define ip_bits(inetptr) \
(((inet_struct *)VARDATA(inetptr))-bits)
#define ip_type(inetptr) \
(((inet_struct *)VARDATA(inetptr))-type)
#define ip_addr(inetptr) \
(((inet_struct *)VARDATA(inetptr))-ipaddr)
#define ip_maxbits(inetptr) \
(ip_family(inetptr) == PGSQL_AF_INET ? 32 : 128)

static int
ip_addrsize(inet *inetptr)
{
switch (ip_family(inetptr))
{
case PGSQL_AF_INET:
return 4;
case PGSQL_AF_INET6:
return 16;
default:
return 0;
}
}
//---

PG_FUNCTION_INFO_V1(inet_inc);

Datum
inet_inc(PG_FUNCTION_ARGS)
{
   inet*src = PG_GETARG_INET_P(0);
   int64arg = PG_GETARG_INT64(1);
   inet*dst;
   uint64   wsp;

// allocate destination structure
   dst = (inet *) palloc0(VARHDRSZ + sizeof(inet_struct));

// copy to destination
   *((inet_struct *)VARDATA(dst)) = *((inet_struct *)VARDATA(src));

   if (ip_family(dst) == PGSQL_AF_INET)
   {
// Increment v4 address w/ item truncated to 32 bits
  *((uint32*)(ip_addr(dst))) =
  htonl(ntohl(*((int32*)(ip_addr(dst + (int32)arg);
   }
   else
   {
// Increment v6 address low qword (store to workspace)
  wsp = htobe64(betoh64(*((int64*)(ip_addr(dst) + 8))) + arg);
  *((uint64*)(ip_addr(dst) + 8)) = wsp;

// Carry/borrow high qword
  if ( arg  0  wsp  *((uint64*)(ip_addr(src) + 8)) )
  {  *((int64*)(ip_addr(dst))) =
 htobe64(betoh64(*((int64*)(ip_addr(dst + 1);
  }
  else
 if ( arg  0  wsp  *((uint64*)(ip_addr(src) + 8)) )
 {  *((int64*)(ip_addr(dst))) =
htobe64(betoh64(*((int64*)(ip_addr(dst - 1);
 }
   }

// Return result
   VARATT_SIZEP(dst) = VARHDRSZ
  + ((char *) ip_addr(dst) - (char *) VARDATA(dst))
  + ip_addrsize(dst);

   PG_RETURN_INET_P(dst);

}
-

Thank you

Ilya A. Kovalenko (mailto:[EMAIL PROTECTED])
SpecialEQ SW section
JSC Oganer-Service

P.S. Treat as Public Domain


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

Re: [HACKERS] inet increment w/ int8

[Ilya A. Kovalenko]

oops

- FUNCTION inet_inc(int, int8) RETURNS inet
+ FUNCTION inet_inc(inet, int8) RETURNS inet


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