CODEWITHSUNDEEP
postgresqlplpgsqlbigint
Javier
Javier

Reputation: 27

PostgreSQL ERROR: there is no built-in function named " (SQL state 42883)

I'm trying to add to my PostgreSQL a very simple function, to convert IP addresses from an integer to a text format.

This is the code of the function:

CREATE FUNCTION  custom_int_to_ip(ip BIGINT)
RETURNS TEXT
AS
$$
DECLARE
octet1 BIGINT;
octet2 TINYINT;
octet3 TINYINT;
octet4 TINYINT;
restofip BIGINT;
BEGIN
octet1 = ip / 16777216;
restofip = ip - (octet1 * 16777216);
octet2 = restofip / 65536;
restofip  = restofip - (octet2 * 65536);
octet3 = restofip / 256;
octet4 = restofip - (octet3 * 256);
END; 
RETURN(CONVERT(TEXT, octet1) + '.' +
CONVERT(TEXT, octet2) + '.' +
CONVERT(TEXT, octet3) + '.' +
CONVERT(TEXT, octet4));
$$
LANGUAGE internal;

As replay I'm obtaining the following error:

ERROR: there is no built-in function named "

And some lines below...

SQL state: 42883

Please let me know if anyone can see my mistake here, I've been trying different syntaxes and search information for the specific SQL state but no clue about what's going on.

Thanks in advance.

Upvotes: 0

Views: 4832

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657002

Additionally to what a_horse_with_no_name already pointed out:

To sum it all up:

CREATE OR REPLACE FUNCTION custom_int_to_ip(ip bigint)
RETURNS inet AS
$$
DECLARE
   octet1   bigint;
   octet2   bigint;
   octet3   bigint;
   octet4   bigint;
   restofip bigint;
BEGIN

octet1   := ip / 16777216;
restofip := ip - (octet1 * 16777216);
octet2   := restofip / 65536;
restofip := restofip - (octet2 * 65536);
octet3   := restofip / 256;
octet4   := restofip - (octet3 * 256);

RETURN (octet1::text || '.'
     || octet2::text || '.'
     || octet3::text || '.'
     || octet4::text);

END; 
$$
LANGUAGE plpgsql IMMUTABLE;

Call:

SELECT custom_int_to_ip(1231231231);

Output:

73.99.24.255

Upvotes: 0

user330315
user330315

Reputation:

There are two errors here:

  1. PostgreSQL uses the standard string concatenation operator || like almost all other databases
  2. There is no convert function in PostgreSQL, you should use to_char() to convert a number to a string

Btw: are you aware that there is a native IP data type available in PostgreSQL?

Upvotes: 4

Related Questions