How to perform a select query in a DO block?

Question

I want to port the below SQL code from MS SQL-Server to PostgreSQL.

DECLARE @iStartYear integer DECLARE @iStartMonth integer  DECLARE @iEndYear integer DECLARE @iEndMonth integer  SET @iStartYear = 2012 SET @iStartMonth = 4  SET @iEndYear = 2016 SET @iEndMonth = 1   ;WITH CTE  AS (     SELECT           --@iStartYear AS TheStartYear           @iStartMonth AS TheRunningMonth          ,@iStartYear AS TheYear           ,@iStartMonth AS TheMonth       UNION ALL       SELECT           --CTE.TheStartYear AS TheStartYear           --@iStartYear AS TheStartYear           CTE.TheRunningMonth + 1 AS TheRunningMonth           --,CTE.TheStartYear + (CTE.TheRunningMonth / 12) AS TheYear          ,@iStartYear + (CTE.TheRunningMonth / 12) AS TheYear          ,(CTE.TheMonth + 1 -1) % 12 + 1 AS TheMonth     FROM CTE      WHERE (1=1)       AND     (         CASE              --WHEN (CTE.TheStartYear + (CTE.TheRunningMonth / 12) ) < @iEndYear              WHEN (@iStartYear + (CTE.TheRunningMonth / 12) ) < @iEndYear                  THEN 1              --WHEN (CTE.TheStartYear + (CTE.TheRunningMonth / 12) ) = @iEndYear              WHEN (@iStartYear + (CTE.TheRunningMonth / 12) ) = @iEndYear                  THEN                      CASE                          WHEN ( (CTE.TheMonth + 1 -1) % 12 + 1 ) <= @iEndMonth                              THEN 1                          ELSE 0                      END              ELSE 0          END = 1      ) ) SELECT * FROM CTE  

This is what I have so far.

DO $$     DECLARE r record;     DECLARE i integer;      DECLARE __iStartYear integer;     DECLARE __iStartMonth integer;      DECLARE __iEndYear integer;     DECLARE __iEndMonth integer;      DECLARE __mytext character varying(200); BEGIN     i:= 5;      --RAISE NOTICE  'test'     --RAISE NOTICE  'test1' || 'test2';      __mytext := 'Test message';     --RAISE NOTICE __mytext;     RAISE NOTICE '%', __mytext;     RAISE NOTICE '% %', 'arg1', 'arg2';      --SQL Standard:  "CAST( value AS text )" [or varchar]     --PostgreSQL short-hand:  "value::text"     __mytext := 'Test ' || i::text;     RAISE NOTICE '%', __mytext;      __mytext := 'mynumber: ' || CAST(i as varchar(33)) || '%';     RAISE NOTICE '%', __mytext;      __iStartYear := 2012;     __iStartMonth := 4;      __iEndYear := 2016;     __iEndMonth := 1;      --PERFORM  'abc';     SELECT 'abc';      -- SELECT  __iStartMonth AS TheRunningMonth;        -- RAISE NOTICE  'The raise_test() function began.' + CAST( i AS text ) ;     -- FOR r IN SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'VIEW' AND table_schema = 'public'     -- LOOP     --  EXECUTE 'GRANT ALL ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO webuser';     --END LOOP; END$$; 

As you can see, I had a few problems when wanting to 'print' with the raise notice functionality. But I managed to resolve that with Google.

From previous experience, I can tell that the Postgres syntax with CTE's is so similar I only have to add a recursive before the CTE, so the only real problem is that I have to define some variables, for which I need a do block.

From this results the simple question that I have:
How can I "perform" a select query in a do block? I want to see the results in the 'data output' tab in pgAdmin3.
And I don't want to create a function.

Answer 1

DO command vs. PL/pgSQL function

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

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

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

Answer 2

Here more details on the workaround with the temp table that Erwin advised, which should be the real answer to the question, since the question is more geared towards "during development, how can I quickly write a code block with a select and see the results" than it is to solve this actual query (the underlying question from the beginning was "howto quickly developping/debugging table valued functions").

Although I must say I'd like to upvote the generate_series part 100 times ;)

It's possible to select the results into a temp table,
and select from the temp table outside the do block,
like this:

DO $$     DECLARE r record;     DECLARE i integer;      DECLARE __iStartYear integer;     DECLARE __iStartMonth integer;       DECLARE __iEndYear integer;     DECLARE __iEndMonth integer;      DECLARE __mytext character varying(200); BEGIN     i:= 5;      -- Using Raise:     -- http://www.java2s.com/Code/PostgreSQL/Postgre-SQL/UsingRAISENOTICE.htm      --RAISE NOTICE  'test'     --RAISE NOTICE  'test1' || 'test2';       __mytext := 'Test message';     --RAISE NOTICE __mytext;     RAISE NOTICE '%', __mytext;     RAISE NOTICE '%', 'arg1' || 'arg2';     RAISE NOTICE '% %', 'arg1', 'arg2';      --SQL Standard:  "CAST( value AS text )" [or varchar]     --PostgreSQL short-hand:  "value::text"     __mytext := 'Test ' || i::text;     RAISE NOTICE '%', __mytext;      __mytext := 'mynumber: ' || CAST(i as varchar(33)) || '%';     RAISE NOTICE '%', __mytext;      __iStartYear := 2012;     __iStartMonth := 4;       __iEndYear := 2016;      __iEndMonth := 1;       --PERFORM  'abc';        --CREATE TEMP TABLE mytable AS SELECT * FROM orig_table;       --DROP TABLE table_name CASCADE;      --DROP TABLE IF EXISTS table_name CASCADE;       --DROP TABLE IF EXISTS tbl;      --CREATE TEMP TABLE tbl AS SELECT 1 as a,2 as b,3 as c;  DROP TABLE IF EXISTS mytable; CREATE TEMP TABLE mytable AS   WITH RECURSIVE CTE  AS (          SELECT               --__iStartYear AS TheStartYear               __iStartMonth AS TheRunningMonth              ,__iStartYear AS TheYear               ,__iStartMonth AS TheMonth       UNION ALL           SELECT               --CTE.TheStartYear AS TheStartYear               --__iStartYear AS TheStartYear               CTE.TheRunningMonth + 1 AS TheRunningMonth              --,CTE.TheStartYear + (CTE.TheRunningMonth / 12) AS TheYear              ,__iStartYear + (CTE.TheRunningMonth / 12) AS TheYear              ,(CTE.TheMonth + 1 -1) % 12 + 1 AS TheMonth         FROM CTE          WHERE (1=1)           AND         (             CASE                  --WHEN (CTE.TheStartYear + (CTE.TheRunningMonth / 12) ) < __iEndYear                  WHEN (__iStartYear + (CTE.TheRunningMonth / 12) ) < __iEndYear                      THEN 1                  --WHEN (CTE.TheStartYear + (CTE.TheRunningMonth / 12) ) = __iEndYear                  WHEN (__iStartYear + (CTE.TheRunningMonth / 12) ) = __iEndYear                      THEN                          CASE                              WHEN ( (CTE.TheMonth + 1 -1) % 12 + 1 ) <= __iEndMonth                                  THEN 1                              ELSE 0                          END                  ELSE 0              END = 1          )  )   SELECT * FROM CTE;        -- SELECT  __iStartMonth AS TheRunningMonth;         --RAISE NOTICE  'The raise_test() function began.' + CAST( i AS text ) ;     --FOR r IN SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'VIEW' AND table_schema = 'public'     --LOOP       --  EXECUTE 'GRANT ALL ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO webuser';     --END LOOP; END$$;   SELECT * FROM mytable; 

Which really is the base to quickly turn a query into a table-valued function version, which looks like this btw.:

-- SELECT * FROM tfu_V_RPT_MonthList(2012,1,2013,4);  CREATE OR REPLACE FUNCTION tfu_V_RPT_MonthList (       __iStartYear integer     ,__iStartMonth integer     ,__iEndYear integer     ,__iEndMonth integer )   RETURNS TABLE(      TheRunningMonth integer     ,TheYear integer     ,TheMonth integer ) AS $BODY$ DECLARE -- Declare vars here BEGIN RETURN QUERY   WITH RECURSIVE CTE  AS (          SELECT               --__iStartYear AS TheStartYear               __iStartMonth AS TheRunningMonth              ,__iStartYear AS TheYear               ,__iStartMonth AS TheMonth       UNION ALL           SELECT               --CTE.TheStartYear AS TheStartYear               --__iStartYear AS TheStartYear               CTE.TheRunningMonth + 1 AS TheRunningMonth              --,CTE.TheStartYear + (CTE.TheRunningMonth / 12) AS TheYear              ,__iStartYear + (CTE.TheRunningMonth / 12) AS TheYear              ,(CTE.TheMonth + 1 -1) % 12 + 1 AS TheMonth         FROM CTE          WHERE (1=1)           AND         (             CASE                  --WHEN (CTE.TheStartYear + (CTE.TheRunningMonth / 12) ) < __iEndYear                  WHEN (__iStartYear + (CTE.TheRunningMonth / 12) ) < __iEndYear                      THEN 1                  --WHEN (CTE.TheStartYear + (CTE.TheRunningMonth / 12) ) = __iEndYear                  WHEN (__iStartYear + (CTE.TheRunningMonth / 12) ) = __iEndYear                      THEN                          CASE                              WHEN ( (CTE.TheMonth + 1 -1) % 12 + 1 ) <= __iEndMonth                                  THEN 1                              ELSE 0                          END                  ELSE 0              END = 1          )  )      SELECT * FROM CTE ;  END; $BODY$   LANGUAGE plpgsql VOLATILE   --ALTER FUNCTION dbo.tfu_v_dms_desktop(character varying) OWNER TO postgres; 

BTW, have a look at the SQL-Server codebloat to achive this:

SELECT       extract('year' FROM m) AS RPT_Year     -- http://www.postgresql.org/docs/current/interactive/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE     --,to_char(m, 'TMmon')     --,to_char(m, 'TMmonth')     ,to_char(m, 'Month') AS RPT_MonthName      ,m AS RPT_MonthStartDate     ,m + INTERVAL '1 month' - INTERVAL '1 day' AS RPT_MonthEndDate   FROM  (    SELECT          generate_series((2012::text || '-' || 4::text || '-01')::date, (2016::text || '-' || 1::text || '-01')::date, interval '1 month') AS m  ) AS g ; 

Turns into this:

DECLARE @in_iStartYear integer DECLARE @in_iStartMonth integer   DECLARE @in_iEndYear integer DECLARE @in_iEndMonth integer  SET @in_iStartYear = 2012 SET @in_iStartMonth = 12   SET @in_iEndYear = 2016 SET @in_iEndMonth = 12    DECLARE @strOriginalLanguage AS nvarchar(200)  DECLARE @dtStartDate AS datetime  DECLARE @dtEndDate AS datetime    SET @strOriginalLanguage = (SELECT @@LANGUAGE)   SET @dtStartDate = DATEADD(YEAR, @in_iStartYear - 1900, 0)  SET @dtStartDate = DATEADD(MONTH, @in_iStartMonth -1, @dtStartDate)   SET @dtEndDate = DATEADD(YEAR, @in_iEndYear - 1900, 0)  SET @dtEndDate = DATEADD(MONTH, @in_iEndMonth -1, @dtEndDate)   SET LANGUAGE 'us_english'   ;WITH CTE_YearsMonthStartAndEnd  AS (         SELECT              YEAR(@dtStartDate) AS RPT_Year              ,DATENAME(MONTH, @dtStartDate) AS RPT_MonthName              ,@dtStartDate AS RPT_MonthStartDate               ,DATEADD(DAY, -1, DATEADD(MONTH, 1, @dtStartDate)) AS RPT_MonthEndDate       UNION ALL          SELECT               YEAR(DATEADD(MONTH, 1, CTE_YearsMonthStartAndEnd.RPT_MonthStartDate)) AS RPT_Year              ,DATENAME(MONTH, DATEADD(MONTH, 1, CTE_YearsMonthStartAndEnd.RPT_MonthStartDate)) AS RPT_MonthName              ,DATEADD(MONTH, 1, CTE_YearsMonthStartAndEnd.RPT_MonthStartDate) AS RPT_MonthStartDate              ,DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, 1, CTE_YearsMonthStartAndEnd.RPT_MonthStartDate)) ) AS RPT_MonthEndDate           FROM CTE_YearsMonthStartAndEnd          WHERE DATEADD(MONTH, 1, CTE_YearsMonthStartAndEnd.RPT_MonthStartDate) <= @dtEndDate  )  SELECT       RPT_Year      ,RPT_MonthName      ,RPT_MonthStartDate      ,RPT_MonthEndDate  FROM CTE_YearsMonthStartAndEnd  

(thanks Erwin !) ;)