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 - (oc开发者_如何学Gotet2 * 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.
There are two errors here:
- PostgreSQL uses the standard string concatenation operator
||
like almost all other databases - 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?
Additionally to what a_horse_with_no_name already pointed out:
- Use
language plpgsql
instead oflanguage internal
- MySQL and others have
tinyint
. In PostgreSQL usesmallint
orint2
(synonym) instead. See the manual about data types. Better yet, make all variablesbigint
in this particular case and save an extra conversion. - Use the assignment operator
:=
instead of=
.=
is undocumented and may stop working without notice in a future version. See here: The forgotten assignment operator "=" and the commonplace ":=" - Move
END;
to the end. - Output is guaranteed to be the same for the same input, so make the function
IMMUTABLE
to speed it up in certain situations.
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
精彩评论