Re: [GENERAL] pgsql question

[Steven Klassen]

# I need to to change an ip addres in a plpgsql function from
# aaa.bbb.ccc.ddd to an sring containing aaa bbb ccc ddd upload.

sklassen= select replace('216.237.145.1'::text, '.', ' ') || ' upload';
   ?column?   
--
 216 237 145 1 upload
(1 row)

-- 
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication  Support Services, (503) 667-4564

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

[GENERAL] pgsql question

[Joost Kraaijeveld]

Hi all,

I need to to change an ip addres in a plpgsql function from aaa.bbb.ccc.ddd to 
an sring containing aaa bbb ccc ddd upload.

The following code does not work. It complaines about the replace line (or is 
it the assignment?): 

ERROR:  syntax error at or near replace at character 1
CONTEXT:  PL/pgSQL function getcustomerdownload line 14 at SQL statement) :


CREATE OR REPLACE FUNCTION getcustomerdownload(text, timestamp, timestamp)
  RETURNS int4 AS
'
DECLARE
my_ipaddress inet;
my_ipaddress_as_text text;
BEGIN
select into my_ipaddress customertable.ipaddress
from
(
select customer.objectid, getcustomername(customer.objectid) as customername, 
customer.ipaddress from customer  
)
as customertable 
where customertable.customername like ''$1%'';

my_ipaddress_as_text := host(my_ipaddress);
replace(my_ipaddress_as_text ,''.'','' '');

return cast( (sum(bytes)/(1024*1024)) as int8) from logs
where
rule_name =  my_ipaddress_as_text | '' upload''
and
that_time between cast( abstime($2) as int4) and cast( abstime($3) as int4);
END
'
LANGUAGE 'plpgsql' VOLATILE;

Does anyone have any idea of how to do this?

TIA


Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: [EMAIL PROTECTED]
web: www.askesis.nl 

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

Re: [GENERAL] pgsql question

[Mike Rylander]

You need to replace into something.  Try

my_ipaddress_as_text := replace(my_ipaddress_as_text,''.'','' '');

Also, the concat operator is ||, not just one |.

Hope that helps!

On Tue, 28 Dec 2004 22:32:47 +0100, Joost Kraaijeveld
[EMAIL PROTECTED] wrote:
 Hi all,
 
 I need to to change an ip addres in a plpgsql function from aaa.bbb.ccc.ddd 
 to an sring containing aaa bbb ccc ddd upload.
 
 The following code does not work. It complaines about the replace line (or is 
 it the assignment?):
 
 ERROR:  syntax error at or near replace at character 1
 CONTEXT:  PL/pgSQL function getcustomerdownload line 14 at SQL statement) :
 
 CREATE OR REPLACE FUNCTION getcustomerdownload(text, timestamp, timestamp)
   RETURNS int4 AS
 '
 DECLARE
 my_ipaddress inet;
 my_ipaddress_as_text text;
 BEGIN
 select into my_ipaddress customertable.ipaddress
 from
 (
 select customer.objectid, getcustomername(customer.objectid) as customername, 
 customer.ipaddress from customer
 )
 as customertable
 where customertable.customername like ''$1%'';
 
 my_ipaddress_as_text := host(my_ipaddress);
 replace(my_ipaddress_as_text ,''.'','' '');
 
 return cast( (sum(bytes)/(1024*1024)) as int8) from logs
 where
 rule_name =  my_ipaddress_as_text | '' upload''
 and
 that_time between cast( abstime($2) as int4) and cast( abstime($3) as int4);
 END
 '
 LANGUAGE 'plpgsql' VOLATILE;
 
 Does anyone have any idea of how to do this?
 
 TIA
 
 Groeten,
 
 Joost Kraaijeveld
 Askesis B.V.
 Molukkenstraat 14
 6524NB Nijmegen
 tel: 024-3888063 / 06-51855277
 fax: 024-3608416
 e-mail: [EMAIL PROTECTED]
 web: www.askesis.nl
 
 ---(end of broadcast)---
 TIP 4: Don't 'kill -9' the postmaster
 


-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

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