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

PostgreSQL dynamic query "query has no destination for result data" returning results

Seems like I’m missing something very basic here. Trying to run a dynamic SQL query that will return a resultset.
if I run this, it will return run successfully, but return nothing:

DO $$
<<outerblock>>
DECLARE 
    p_sql text;
BEGIN
    p_sql := 'Select * from public.tbl_song;';
    Execute p_sql ;
END $$

if I run this, it will complain that the query has no destination for result data

DO $$
<<outerblock>>
DECLARE 
    p_sql text;
BEGIN
    p_sql := 'DO 
                $inner$ 
                BEGIN
                Select * from public.tbl_song;
                END; 
                $inner$;';
    Execute p_sql ;
END $$

When I look at the documentation I don’t see anything about returning a resultset. Just using a record variable for results, but that’s limited to one row.
what am I missing?

Tags:

2

Answers


  1. 0

    You’re getting the error because Postgres expects the function to return something of type text, but your function doesn’t return anything. The stored procedure won’t just return the result of the last SELECT.
    You need to actually return the value:

    CREATE OR REPLACE FUNCTION fun() RETURNS text AS $$

    BEGIN
    — ….
    RETURN(SELECT dblink_disconnect());
    END
    $$ LANGUAGE plpgsql;

    Login or Signup to reply.
  2. 0

    I’m not sure why you would want to nest do blocks, but you can avoid the error by using perform instead of select in the outer block:

    do $$
begin
perform 'do $_$ begin select 1; end $_$;';
end $$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search