How to perform a select query in a DO block?

by

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:

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:

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?

More Related Contents:

  1. PL/pgSQL checking if a row exists
  2. Postgres function returning table not returning data in columns
  3. Join a count query on generate_series() and retrieve Null values as ‘0’
  4. Refactor a PL/pgSQL function to return the output of various SELECT queries
  5. Is SELECT or INSERT in a function prone to race conditions?
  6. Difference between language sql and language plpgsql in PostgreSQL functions
  7. How to return result of a SELECT inside a function in PostgreSQL?
  8. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  9. Pass multiple values in single parameter
  10. GROUP BY and aggregate sequential numeric values
  11. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  12. PostgreSQL parameterized Order By / Limit in table function
  13. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  14. PostgreSQL IF statement
  15. Compute percents from SUM() in the same SELECT sql query
  16. Postgres analogue to CROSS APPLY in SQL Server
  17. Record returned from function has columns concatenated
  18. Postgres Dynamic Query Function
  19. Window Functions or Common Table Expressions: count previous rows within range
  20. How to find a table having a specific column in postgresql
  21. DROP FUNCTION without knowing the number/type of parameters?
  22. Return multiple columns and rows from a function PostgreSQL instead of record
  23. Calculate working hours between 2 dates in PostgreSQL
  24. COPY with dynamic file name
  25. Check if NULL exists in Postgres array
  26. pseudo_encrypt() function in plpgsql that takes bigint
  27. How to use SQL LIKE condition with multiple values in PostgreSQL?
  28. How to create a new database with the hstore extension already installed?
  29. Function with SQL query has no destination for result data
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment