How to return value from stored procedure?

Question

CREATE OR REPLACE PROCEDURE distances(IN flight_num1 Integer)
LANGUAGE 'plpgsql'
AS
 DECLARE
BEGIN
    SELECT 
        *,
        CASE
            WHEN distance_miles >= 0 AND distance_miles <= 2000 THEN 'short distance travel (SDT)'
            WHEN distance_miles >= 2000 AND distance_miles <= 6500 THEN 'Intermediate distance travel (IDT)'
            WHEN distance_miles > 6500 THEN 'Long distance travel (LDT)'
        END AS category
    FROM
        "routes" 
    WHERE 
        flight_num = flight_num1;
END;
$BODY$;

call distances(1111);

ERROR: query has no destination for result data

HINT: If you want to discard the results of a SELECT, use PERFORM instead.

CONTEXT: PL/pgSQL function distances(integer) line 4 at SQL statement

SQL state: 42601

I tried returning the value as well but still facing the issue.

Does this answer your question? [How to return a value from a stored procedure (not function)?](https://stackoverflow.com/questions/62981538/how-to-return-a-value-from-a-stored-procedure-not-function) — Luuk, Sep 01 '23 at 17:28
1) It is explained in the docs(which should have been your first stop) here [Control Structures](https://www.postgresql.org/docs/current/plpgsql-control-structures.html): *43.6.1. Returning from a Function* and *43.6.2. Returning from a Procedure* 2) The error is a separate issue and is explained here [Executing SQL Commands](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL). — Adrian Klaver, Sep 01 '23 at 17:32
@AndrewMorton: or more..... It depends also on the data type of the column `distance_miles`. When it is an INTEGER you might have more than 1 result, But when it's a FLOAT changes are getting smaller.... — Luuk, Sep 01 '23 at 17:42

score 0 · Answer 1 · answered Sep 02 '23 at 18:58

You procedure has 2 major problems. In order to return values a procedure must include an OUT or INOUT parameter. Secondly a Select inside a plpgsql block must include the into clause. Further this parameter must match number of columns and data type of each column. For what you have this means creating a user defined data type (UDT). The results then becomes: (Assumed a definition of table routes since it was not provided.)

create type routes_category as (flight_num     integer
                               ,distance_miles integer
                               ,from_city      text
                               ,to_city        text
                               ,flight_time    interval
                               ,category       text
                               );

create or replace procedure distances(in  flight_num1 integer
                                     ,out flight_dtl  routes_category
                                     )
language 'plpgsql'
as $body$
begin
    select 
        *,
        case
            when distance_miles >= 0 and distance_miles <= 2000    then 'short distance travel (SDT)'
            when distance_miles >= 2000 and distance_miles <= 6500 then 'Intermediate distance travel (IDT)'
            when distance_miles > 6500                             then 'Long distance travel (LDT)'
        end as category
    into  flight_dtl 
    from
        routes 
    where 
        flight_num = flight_num1;
end;
$body$;

But why are you using a Procedure at all, it does nothing but retrieve data. If this is actually the case you should use a function. You still need to define the appropriate return type, either as above or as the return type as table. If it contains just SQL then define it as a SQL function.

create or replace function distances_fun(in  flight_num1 integer)
  returns  table(flight_num     integer
                ,distance_miles integer
                ,from_city      text
                ,to_city        text
                ,flight_time    interval
                ,category       text
                )
  language sql
as $body$
    select 
        *,
        case
            when distance_miles >= 0 and distance_miles <= 2000    then 'short distance travel (SDT)'
            when distance_miles >= 2000 and distance_miles <= 6500 then 'Intermediate distance travel (IDT)'
            when distance_miles > 6500                             then 'Long distance travel (LDT)'
        end as category
    from
        routes 
    where 
        flight_num = flight_num1;
$body$;

Demo for each here. Unfortunately, db<>fiddle does not show results from raise notice .... That would have been:

Flight 1111 is short distance travel (SDT) 
Flight 1122 is short distance travel (SDT)

How to return value from stored procedure?

1 Answers1