Query has no destination for result data in function PostgreSQL

Question

I have a function that returns results to me, but the result is not what I expected and capturing the error message I get this:

query has no destination for result data

this is my code postgresql:

CREATE OR REPLACE FUNCTION teltonika_funelement(
    raw character varying,
    tipo integer)
    RETURNS TABLE(id integer, nombre character varying, value1 character varying) 
    LANGUAGE 'plpgsql'

AS $BODY$
DECLARE     i     INT=1;
            y     INT=1;
            Len1  INT;
    ---------------------------------------------------------------------------
    
BEGIN

     CREATE TEMP TABLE Elements(ID Integer, Nombre CHARACTER VARYING, Value2 CHARACTER VARYING);

    
    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 
    
        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;
    ---------------------------------------------------------------------------

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

ALTER FUNCTION public.teltonika_funelement(character varying, integer)
    OWNER TO postgres;

this is the error message:

query has no destination for result data

I can not find the cause of the error I have tried changing the return like this:

RETURNS TEXT AS

How could I solve it, I really appreciate your help

Modify `SELECT E.ID ...` to `RETURN QUERY SELECT E.ID...` per docs [RETURN TABLE](https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS) — Adrian Klaver, Jul 01 '21 at 20:21
I already made the change this is the error message relation "elements" already exists — Rogelio Fernandez Aybar, Jul 01 '21 at 21:36
Either add a `DROP TABLE IF EXISTS elements` just before the `CREATE TEMP TABLE Elements ...` or use `ON COMMIT DROP` in the `CREATE TEMP TABLE ...` per docs here [CREATE TABLE](https://www.postgresql.org/docs/current/sql-createtable.html). — Adrian Klaver, Jul 01 '21 at 22:05

Jim Jones · Accepted Answer · 2021-07-03T18:36:14.380

A non void function expects a RETURN corresponding to the data type (or structure) declared at RETURNS. In your case a table containing an integer and two character varying. Take a look at the following test function that returns a table with an int and a text column:

CREATE OR REPLACE FUNCTION myfunc(int)
RETURNS TABLE(val int, txt text)  LANGUAGE 'plpgsql'
AS $$
BEGIN
  CREATE TEMPORARY TABLE tmp(id int,res text) ON COMMIT DROP;

  IF $1=0 THEN 
    INSERT INTO tmp VALUES ($1,'Invalid');
  ELSE 
    FOR i IN 1..$1 LOOP
     INSERT INTO tmp VALUES (i,'txt '||i);
    END LOOP;
  END IF;

  RETURN QUERY SELECT id,res FROM tmp;
END;
$$;

Test

SELECT * FROM myfunc(2);
 val |  txt  
-----+-------
   1 | txt 1
   2 | txt 2
(2 rows)

Demo: db<>fiddle

A few thoughts:

In plpgsql it is possible to have multiple RETURN statements, so that different parts of your code return something different without exiting the function. But in case you prefer to have an intermediate table to collect the information and return it only once in the end, make sure the table is either manually dropped after the function is completed (or if it raises an error!) or simply create it as ON COMMIT DROP. If you prefer the former, take a look at UNLOGGED TABLES.
Consider using text over character varying.
Your WHILE loop uses an incremental value of 1 until it reaches a certain limit. You could use a FOR loop and get rid of the variable i in the DECLARE clause. It's no big deal but depending on your function size it might make things cleaner.
You can use the parameter order instead of its name, e.g. $1. It is also no big deal but it avoids conflicts and/or confusion with columns that might have the same name.

Query has no destination for result data in function PostgreSQL

1 Answers1