Question 385074 in PostgreSQL Question posted in
The official documentation can be found here.

How to correctly call a function in PostgreSQL

I have created a function that does not give any errors itself but when calling it I get an error. I know I technically don’t need a function but for my scenario I need to be using one.

The error:

QUERY "SELECT (show_date) FROM show" returned more than one row.
CONTEXT: PL/PGSql function change_data_type() line 3 at RETURN

CREATE OR REPLACE FUNCTION change_data_type()
RETURNS date as $$
BEGIN
    RETURN DATE(show_date) FROM show;
END;
$$ LANGUAGE PLPGSQL;

SELECT change_data_type();
Tags:

2

Answers


  1. 0 
    create table show(show_date DATE);
INSERT INTO show values (now());

    with one record:

    SELECT change_data_type();

    will not return an error

    but with more than 1 record:

    INSERT INTO show values (now());
SELECT change_data_type();

    it will, see: DBFIDDLE

    ERROR:  query returned more than one row
CONTEXT:  query: DATE(show_date) FROM show
PL/pgSQL function change_data_type() line 3 at RETURN
    Login or Signup to reply.
  2. 0 
    CREATE OR REPLACE FUNCTION change_data_type()
RETURNS TABLE(dt date) 
LANGUAGE PLPGSQL
as $$
BEGIN
    RETURN QUERY SELECT show_date::date FROM show;
END;
$$ ;

create table show(show_date timestamp);
insert into show values (now()), (now() - '1 day'::interval), (now() + '45 mins'::interval);

select * from show;
         show_date          
----------------------------
 2024-05-04 08:37:09.334147
 2024-05-03 08:37:09.334147
 2024-05-04 09:22:09.334147


select * from change_data_type();
 dt     
------------
 2024-05-04
 2024-05-03
 2024-05-04

    See:

    https://www.postgresql.org/docs/current/plpgsql-control-structures.html

    When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. In that case, the individual items to return are specified by a sequence of RETURN NEXT or RETURN QUERY commands, and then a final RETURN command with no argument is used to indicate that the function has finished executing. RETURN NEXT can be used with both scalar and composite data types; with a composite result type, an entire “table” of results will be returned. RETURN QUERY appends the results of executing a query to the function’s result set. RETURN NEXT and RETURN QUERY can be freely intermixed in a single set-returning function, in which case their results will be concatenated.

    RETURN NEXT and RETURN QUERY do not actually return from the function — they simply append zero or more rows to the function’s result set. Execution then continues with the next statement in the PL/pgSQL function. As successive RETURN NEXT or RETURN QUERY commands are executed, the result set is built up. A final RETURN, which should have no argument, causes control to exit the function (or you can just let control reach the end of the function).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search