How to Perform a Select Query in a Do Block

How to perform a select query in a DO block?

DO command vs. PL/pgSQL function

A DO command does not return rows. You can send NOTICES or RAISE other messages (using default LANGUAGE plpgsql), or you can write to a (temporary) table and later SELECT from it to get around this.

But really, create a function instead, where you can define a return type with the RETURNS clause and/or OUT and INOUT parameters and return from the function in various ways. Related:

• Returning from a function with OUT parameter

If you don't want a function saved and visible for other connections, consider a "temporary" function, which is an undocumented but well established feature:

• How to create a temporary function in PostgreSQL?

generate_series() for problem at hand

For the problem at hand you don't seem to need any of this. Use this simple query instead:

SELECT row_number() OVER ()    AS running_month
     , extract('year'  FROM m) AS year
     , extract('month' FROM m) AS month
FROM   generate_series(timestamp '2012-04-01'
                     , timestamp '2016-01-01'
                     , interval '1 month') m;

db<>fiddle here

Why?

• Generating time series between two dates in PostgreSQL

After INSERT INTO in DO block query an SELECT

Here is the solution using cursor:

Assign the output of your select query output in cursor and fetch it out of DO block but with in same session.
Considering your insert and select queries are working fine individually then write your do block like below.

DO $$ 
DECLARE
myid tbl_groupchat.id%TYPE;
ref_cursor REFCURSOR := 'mycursor';
BEGIN
INSERT INTO public.tbl_groupchat(
        chatname, createdate)
VALUES ('test4', CURRENT_DATE)
RETURNING id INTO myid;
INSERT INTO public.tbl_participants (
         id_groupchat, id_profile)
         VALUES (myid, 2);         
OPEN ref_cursor for SELECT *
FROM public.tbl_participants
WHERE id_profile = 2;
END $$;

FETCH ALL  from  mycursor;

Returning a query from a do block

You cannot return any result from a DO command. The documentation says (emphasis added):

The code block is treated as though it were the body of a function with no parameters, returning void.

You can use a common table expression:

WITH args(book_name, book_slug) AS (
    VALUES ('Ise Monogatari', 'ise')
)

  SELECT bk.id,
    bk.created_at,
    bk.updated_at,
    bk.title,
    bk.japanese_title,
    bk.content,
    bk.description,
    'public/cjp/' || book_slug || '/images/cover.png' cover_image_path,
    'public/cjp/' || book_slug || '/images/title.png' title_image_path,
    'public/cjp/' || book_slug || '/images/thumb_left.png' thumbnail_path,
    'public/cjp/' || book_slug || '/images/background.png' background_image_path,
    bk.about,
    bk.published
  FROM books bk
  CROSS JOIN args
  WHERE bk.title = book_name;

How does one SELECT block another?

SELECT statements may block another SELECT statement. You're probably thinking that since both acquire only S locks, they should never block. But blocking occurs on various types of resources, not only locks. Typical example is memory constraints. I'll try to digg up a recent answer to a question here that had attached a deadlock graph that showed to SELECT statements, one waiting for the other for parallel exchange operator memory resources (buffers).

Updated
Here is the link with deadlock info I talked about: I have data about deadlocks, but I can't understand why they occur
If you study the deadlock graph, you'll notice the following resource in the wait list:

<exchangeEvent id="Pipe894b0680" WaitType="e_waitPipeGetRow" nodeId="0">
  <owner-list>
    <owner id="process824df048"/>
  </owner-list>
  <waiter-list>
    <waiter id="process86ce0988"/>
  </waiter-list>
</exchangeEvent>

This is not a lock, is a 'e_waitPipeGetRow' resource, is owned by a SELECT and another SELECT is waiting for it. Some discussion about 'intra-query parallel resources' can be found here: Today's Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks". While most discussions are going to focus on deadlock issues, that doesn't mean that ordinary blocking cannot occur on these resources. sys.dm_exec_requests will have the proper info in wait_type and wait_resource.

SQL Server SELECT statements causing blocking

SELECT can block updates. A properly designed data model and query will only cause minimal blocking and not be an issue. The 'usual' WITH NOLOCK hint is almost always the wrong answer. The proper answer is to tune your query so it does not scan huge tables.

If the query is untunable then you should first consider SNAPSHOT ISOLATION level, second you should consider using DATABASE SNAPSHOTS and last option should be DIRTY READS (and is better to change the isolation level rather than using the NOLOCK HINT). Note that dirty reads, as the name clearly states, will return inconsistent data (eg. your total sheet may be unbalanced).

---

Related Topics