Convert SQLserver function to postgres

Question

I have the following code written in SQL Server but I am migrating to PostgreSQL in this way I have errors.

This is my SQLserver code:

CREATE FUNCTION Teltonika_FunElement(
     @Raw   VARCHAR(MAX)
    ,@Tipo  INT
)
RETURNS @Elements2 TABLE(ID INT, Nombre VARCHAR(MAX), Value VARCHAR(MAX))
AS
BEGIN
    DECLARE @Elements TABLE(ID INT, Nombre VARCHAR(MAX), Value VARCHAR(MAX))

    DECLARE
         @i     INT=1
        ,@y     INT=1
        ,@Len   INT
    ---------------------------------------------------------------------------
    IF @Tipo=1
    BEGIN
        SET @Len = LEN(@Raw)/4

        WHILE @i<=@Len
        BEGIN
            INSERT INTO @Elements
            SELECT
                 dbo.Mux_FUN_HexadecimalToDec_v2(SUBSTRING(@Raw, @y, 2))
                ,NULL
                ,SUBSTRING(@Raw, @y+2, 2)

            SET @y=@y+4

            SET @i = @i+1
        END
    END
    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------
    IF @Tipo=2
    BEGIN
        SET @Len = LEN(@Raw)/6
        
        WHILE @i<=@Len
        BEGIN
            INSERT INTO @Elements
            SELECT
                 dbo.Mux_FUN_HexadecimalToDec_v2(SUBSTRING(@Raw, @y, 2))
                ,NULL
                ,SUBSTRING(@Raw, @y+2, 4)

            SET @y=@y+6

            SET @i = @i+1
        END
    END
    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------
    IF @Tipo=4
    BEGIN
        SET @Len = LEN(@Raw)/10
        
        WHILE @i<=@Len
        BEGIN
            INSERT INTO @Elements
            SELECT
                 dbo.Mux_FUN_HexadecimalToDec_v2(SUBSTRING(@Raw, @y, 2))
                ,NULL
                ,SUBSTRING(@Raw, @y+2, 8)

            SET @y=@y+10

            SET @i = @i+1
        END
    END
    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------
    IF @Tipo=8
    BEGIN
        SET @Len = LEN(@Raw)/18
        
        WHILE @i<=@Len
        BEGIN
            INSERT INTO @Elements
            SELECT
                 dbo.Mux_FUN_HexadecimalToDec_v2(SUBSTRING(@Raw, @y, 2))
                ,NULL
                ,SUBSTRING(@Raw, @y+2, 16)

            SET @y=@y+18

            SET @i = @i+1
        END
    END
    ---------------------------------------------------------------------------

    INSERT INTO @Elements2
    SELECT
         E.ID
        ,PropertyName
        ,CASE
            WHEN (TypeParser = 'INT' AND Active=1)                  THEN CONVERT(VARCHAR(255), dbo.Mux_FUN_HexadecimalToDec_v2(Value))
            WHEN (TypeParser = 'DECIMAL(18,1)/1000' AND Active=1)   THEN CONVERT(VARCHAR(255), (dbo.Mux_FUN_HexadecimalToDec_v2(Value)/1000))
         ELSE Value
         END Value
    FROM @Elements E
    LEFT JOIN Mux_Teltonika_Tbl_ElementsConf TE ON TE.PropertyID=E.ID
    
    RETURN
END

so far I managed to write this in postgres:

CREATE OR REPLACE FUNCTION Teltonika_FunElement(
     Raw    CHARACTER VARYING,
     Tipo   INTEGER
)
RETURNS Elements2 TEMP TABLE(ID INT, Nombre CHARACTER VARYING, Value1 CHARACTER VARYING)
AS
BEGIN
    DECLARE Elements TEMP TABLE(ID INT, Nombre CHARACTER VARYING, Value1 CHARACTER VARYING)

    DECLARE
         i      INT=1;
         y      INT=1;
         Len1   INT;
    ---------------------------------------------------------------------------
    IF Tipo=1 THEN
    
        Len1    := LENGTH(Raw)/4;

        WHILE i<=Len1
        LOOP
            INSERT INTO Elements
            SELECT
                 hex_to_int(SUBSTRING(Raw FROM y FOR 2))
                ,NULL
                ,SUBSTRING(Raw FROM y+2 FOR 2);

                 y  :=  y+4;

                 i  :=  i+1;
        END LOOP;
    END IF;
    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------
    IF Tipo = 2 THEN 
    BEGIN
        Len1 := LENGTH(Raw)/6;
        
        WHILE i<=Len1
        LOOP
            INSERT INTO Elements
            SELECT
                 hex_to_int(SUBSTRING(Raw FROM y FOR 2))
                ,NULL
                ,SUBSTRING(Raw FROM y+2 FOR 4);

                y := y+6;

                i := i+1;
        END LOOP;
    END IF;
    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------
    IF Tipo = 4 THEN
    
        Len1 := LENGTH(Raw)/10;
        
        WHILE i<=Len1
        LOOP
            INSERT INTO Elements
            SELECT
                 hex_to_int(SUBSTRING(Raw FROM y FOR 2))
                ,NULL
                ,SUBSTRING(Raw FROM y+2 FOR 8);

                y := y+10;

                i := i+1;
        END LOOP;
    END IF;
    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------
    IF Tipo = 8 THEN
    
        Len1 := LENGTH(Raw)/18;
        
        WHILE i<=Len1
        LOOP
            INSERT INTO Elements
            SELECT
                 hex_to_int(SUBSTRING(Raw FROM y FOR 2))
                ,NULL
                ,SUBSTRING(Raw FROM y+2 FOR 16);

                y := y+18;

                i := i+1;
        END LOOP;
    END IF;
    ---------------------------------------------------------------------------

    INSERT INTO Elements2
    SELECT
         E.ID
        ,PropertyName
        ,CASE
            WHEN (TypeParser = 'INT' AND Active=1);                 THEN (hex_to_int(Value1):: VARCHAR)
            WHEN (TypeParser = 'DECIMAL(18,1);/1000' AND Active=1); THEN (hex_to_int(Value1)/1000)::VARCHAR)
         ELSE Value
         END Value
    FROM Elements E
    LEFT JOIN Teltonika_Tbl_ElementsConf TE ON TE.PropertyID=E.ID;
    
    RETURN
END;

I get the following error:

ERROR: syntax error at or near "TEMP"
LINE 5: RETURNS Elements2 TEMP TABLE (ID INT, Name CHARACTER VARYIN ...

Thank you very much for your help

What does it do? It looks like it splits a string into rows? That can be done with built-in functions — user330315
– user330315, Commented Oct 14, 2020 at 5:35

Pavel Stehule · Accepted Answer · 2020-10-14 05:04:02Z

1

Postgres has not table type variables. Instead you can use a arrays. The arguments of functions are probably arrays too with own reimplementation in MSSQL. Inside Postgres you can pass arguments in array format - without bad readable own code.

When array is of composite type, then you should to define composite type first:

CREATE TYPE element_type AS (id int, nombre varchar, value varchar);

CREATE OR REPLACE FUNCTION teltonika_funelement(raw varchar, tipo int)
RETURNS element_type[] AS $$
DECLARE elements element_type[] DEFAULT '{}';
BEGIN
  ...
  elements := elements ||
                ROW(hex_to_int(SUBSTRING(raw FROM y FOR 2))
                   ,NULL
                   ,SUBSTRING(Raw FROM y+2 FOR 8));

  ...
  RETURNS elements;
END;
$$ LANGUAGE plpgsql;

Using camel case notation for variables, arguments is bad practice (due case insensitivity of identifiers).

Programming stored procedures in PostgreSQL is pretty different than in T-SQL in MS SQL. It is much more similar to programming in classical procedural languages (and it is much more similar to Oracle PL/SQL). Please, forgot all what you know, and start from ground. PLpgSQL documentation is good (and not too long - less than 50 pages), start by reading this doc.

You can use temp table

BEGIN
  CREATE TEMP TABLE IF NOT EXISTS elements(id int, nombre varchar, value1 varchar)
  DELETE FROM elements;
  ...

But using temp tables for few values (less than thousands) is pretty expensive and slow.

There is another error. DECLARE clause is before block (before BEGIN keyword). So good to start with documentation - PL/pgSQL is level better language than T-SQL, but it is different language, different environment.

edited Oct 14, 2020 at 5:04

answered Oct 14, 2020 at 4:57

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Roller Fernandez Over a year ago

I understood the explanation, I only generated a question as I do to make an inner join with table teltonika_tbl_elementsconf and then I return this, here is the last part of the code INSERT INTO Elements2 SELECT E.ID ,PropertyName ,CASE WHEN (TypeParser = 'INT' AND Active=1) THEN (hex_to_int(Value1):: VARCHAR) WHEN (TypeParser = 'DECIMAL(18,1);/1000' AND Active=1) THEN (hex_to_int(Value1)/1000)::VARCHAR) ELSE Value END Value FROM Elements E LEFT JOIN Teltonika_Tbl_ElementsConf TE ON TE.PropertyID=E.ID;

Pavel Stehule Over a year ago

@RollerFernandez You can use a function unnest - this transform a array to table, or you can return a array again.

Roller Fernandez Over a year ago

Thanks for help;

Collectives™ on Stack Overflow

Convert SQLserver function to postgres

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related