Best practices for writing / debugging complex PL/pgSQL queries

Question

In short: What is the best way to write and debug PostgreSQL queries that make use of procedural programming techniques (e.g. using pl/pgsql)? I'm pretty new to writing these types of procedural queries so apologies in advance if there is a really simple solution I haven't found.

Specifics: I am writing some (relatively) complex SQL queries that make use of existing pl/pgsql functions and ideally would make use of lots of variables to make the query easy to debug and modify. (The example code below is deliberately simple; my actual use cases are much more complicated.)

My first solution was to embed everything inside a function, but this is clunky. Since I have to declare the return type explicitly, it makes modifying the code a pain:

CREATE OR REPLACE FUNCTION 
my_schema.my_fcn()
RETURNS TABLE(user_id integer, 
              id integer, 
              created_at timestamp) AS
$BODY$
DECLARE
    _id_select integer = 10;
BEGIN
    RETURN QUERY
        SELECT 
            user_id, 
            id,
            created_at
        WHERE id = _id_selector
        -- possibly a ton of other complicated stuff involving other functions and variables
        FROM my_schema.my_other_fcn()

END;
$BODY$
LANGUAGE plpgsql;

(Edit: Made the query slightly more nontrivial.)

Now suppose I want to add order_total to my query and get rid of id: Then I have to modify the return type. This is going to happen a lot, and its annoying to have to change the return type over and over.

The next solution I tried was to NOT use a function, and try something along these lines: How can I execute pl/pgsql code without creating a function? This does not work either:

DO $$ 
DECLARE
    _id_select integer = 10;
BEGIN
  SELECT 
      user_id, 
      id,
      created_at
  WHERE id = _id_selector
  -- possibly a ton of other complicated stuff involving other functions and variables
  FROM my_schema.my_other_fcn()
END $$;

The resulting 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 4 at SQL statement
********** Error **********

ERROR: query has no destination for result data

I do not want to use PERFORM because, actually, I do want to see the result of the query.

Is there a graceful and/or standard solution to this type of SQL programming?

Answer 1

Why you don't call plpgsql function directly?

do only:

 SELECT 
      user_id, 
      id,
      created_at
  -- possibly a ton of other complicated stuff involving other functions and variables
  FROM my_schema.my_other_fcn()

I don't like a this style of plpgsql programming - wrapping queries. You can block very effective planner - because plpgsql function is black box for optimizer - so you can expect terrible performance issues on large data. PLpgSQL functions should not to substitute a views.

Statement DO should not to return any result. Simply - PostgreSQL functions should not to substitute PostgreSQL views.