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.
DO
command vs. PL/pgSQL functionThe 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:
generate_series()
for problem at handFor 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?