Обсуждение: pgsql question

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: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

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
<J.Kraaijeveld@askesis.nl> 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: J.Kraaijeveld@Askesis.nl
> web: www.askesis.nl
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org

# 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