Return results from anonymous code block on Postgresql

One of the nice things about MS Sql Server procedural language is that it doesn't support multiple languages, T-SQL only, and as such it can facilitate on-the-fly testing of code logic:

declare @person_id int;

set @person_id = 1;

select * from person where person_id = @person_id;

On Postgres 9.0, though it already supports anonymous code block, it's constrained by the fact that the anonymous code block is encapsulated in an invisible void function(Postgres supports many procedural languages, not just its own flavor of procedural language(PL/pgSQL), e.g. Python, Java, Perl, LOLCODE, etc), well you can think of it that way. Hence displaying query results on anonymous code block won't work out-of-the-box on Postgres. This won't work:

do
$$
declare 
 _person_id int; 
begin
 _person_id = 1;

 select * from person
 where person_id = _person_id;
end;
$$;

That will produce this error:

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 "inline_code_block" line 8 at SQL statement

********** Error **********

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "inline_code_block" line 8 at SQL statement

Neither this will work:

do
$$
declare 
 _person_id int; 
begin
 _person_id = 1;

 return query select * from person
 where person_id = _person_id;
end;
$$;

That code will produce this error:

ERROR:  cannot use RETURN QUERY in a non-SETOF function
LINE 13:  return query select * from person where person_id = _person...
          ^

********** Error **********

ERROR: cannot use RETURN QUERY in a non-SETOF function
SQL state: 42804
Character: 170

So if you want to test logic on-the-fly on Postgres, create temporary table on anonymous code block:

do
$$
declare 
 _person_id int; 
begin
 _person_id = 1;

 drop table if exists _x;
 create temporary table _x as 
 select * from person
 where person_id = _person_id;
end;
$$;

select * from _x; -- the temporary table will continue to exist on individual Postgres session

---

Wish for Postgres, make this happen, to make adhoc-y stuff happen :-)

do returns table (person_id int, lastname text) as
$$
declare
 _person_id int; 
begin
 _person_id = 1;
 
 return query select person_id, lastname from person
 where person_id = _person_id;
end;
$$;

---

UPDATE: March 27, 2019

Made the code shorter:

do
$$
declare 
 _person_id int; 
begin
 _person_id = 1;

 create temporary table _x on commit drop
 as 
 select * from person
 where person_id = _person_id;
end;
$$;

select * from _x; -- the temporary table will continue to exist on individual Postgres session

With that code, there's no need to explicitly drop the table. Postgres has an implicit commit whenever queries are executed. on commit drop ensures that the temporary table will not stay in session longer than necessary.


Found out on commit drop here: https://stackoverflow.com/questions/22929365/create-a-temp-table-if-not-exists-for-use-into-a-custom-procedure/22932917#22932917