Postgres Query has no desination for result data despite RETURN statement in function definition-CodePudding

I've created a code that generates a HH:MM timestamp and saved it as a function. But when I try to call it with SELECT random_timestamp(); an error is returned -> Query has no destination for result data

CREATE OR REPLACE FUNCTION random_timestamp() 
RETURNS text AS 
'
BEGIN
SELECT
         CASE
                WHEN Length(s1.hours) = 1
                AND    Length(s1.minutes) = 1 THEN Concat(''0'',s1.hours, '':'',''0'', s1.minutes)
                WHEN Length(s1.hours) = 1
                AND    Length(s1.minutes) = 2 THEN Concat(''0'',s1.hours, '':'', s1.minutes)
                WHEN Length(s1.hours) = 2
                AND    Length(s1.minutes) = 1 THEN Concat(s1.hours, '':'', ''0'', s1.minutes)
                ELSE Concat(s1.hours, '':'', s1.minutes)
         END
  FROM   (SELECT floor(Random() * (23-0-1)   0)::text AS hours ,
                 floor(random() * (59-0-1)   0)::text AS minutes) AS s1;
RETURN(SELECT random_timestamp());
END;
' 
language 'plpgsql';

CodePudding user response：

Your code is incorrect.

Corrected:

CREATE OR REPLACE FUNCTION random_timestamp() 
RETURNS text
LANGUAGE plpgsql
AS $function$
declare 
    v_return text; 
BEGIN
        SELECT
        CASE
                WHEN Length(s1.hours) = 1
                AND    Length(s1.minutes) = 1 THEN Concat('0',s1.hours, ':','0', s1.minutes)
                WHEN Length(s1.hours) = 1
                AND    Length(s1.minutes) = 2 THEN Concat('0',s1.hours, ':', s1.minutes)
                WHEN Length(s1.hours) = 2
                AND    Length(s1.minutes) = 1 THEN Concat(s1.hours, ':', '0', s1.minutes)
                ELSE Concat(s1.hours, ':', s1.minutes)
        end into v_return
        FROM   (SELECT floor(Random() * (23-0-1)   0)::text AS hours ,
                 floor(random() * (59-0-1)   0)::text AS minutes) AS s1;
        
        return v_return;
 
END;
$function$
;

CodePudding user response：

You don't actually need PL/pgSQL for this. A simple SQL function will do:

CREATE OR REPLACE FUNCTION random_timestamp() 
RETURNS text
LANGUAGE sql
AS 
$function$
  SELECT
  CASE
      WHEN Length(s1.hours) = 1
      AND    Length(s1.minutes) = 1 THEN Concat('0',s1.hours, ':','0', s1.minutes)
      WHEN Length(s1.hours) = 1
      AND    Length(s1.minutes) = 2 THEN Concat('0',s1.hours, ':', s1.minutes)
      WHEN Length(s1.hours) = 2
      AND    Length(s1.minutes) = 1 THEN Concat(s1.hours, ':', '0', s1.minutes)
      ELSE Concat(s1.hours, ':', s1.minutes)
  end
  FROM   (SELECT floor(Random() * (23-0-1)   0)::text AS hours ,
       floor(random() * (59-0-1)   0)::text AS minutes) AS s1;
$function$
;

But you are over complicating this. If you want to generate a random time value, you can use:

select time '00:00'   make_interval(mins => (random() * 1440)::int)

You can put that into a function if you want:

create or replace function random_time()
   returns time
as
$$
  select time '00:00'   make_interval(mins => (random() * 1440)::int);
$$
language sql;

If you really need a text value, you can format that using to_char()

select to_char(random_time(), 'hh24:mi')

Of your