Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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
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)
--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
WHEN ( (CTE.TheMonth + 1 -1) % 12 + 1 ) <= @iEndMonth
THEN 1
ELSE 0
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 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?
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
Generating time series between two dates in PostgreSQL
Cursors are visible in the transaction scope so you should use this
within one transaction.
Name of the cursor variable should be the same as a text constant;
More about cursors.
Technique source here (in Russian).
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
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)
--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
WHEN ( (CTE.TheMonth + 1 -1) % 12 + 1 ) <= __iEndMonth
THEN 1
ELSE 0
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
$BODY$
DECLARE
-- Declare vars here
BEGIN
RETURN QUERY
WITH RECURSIVE CTE
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)
--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
WHEN ( (CTE.TheMonth + 1 -1) % 12 + 1 ) <= __iEndMonth
THEN 1
ELSE 0
ELSE 0
END = 1
SELECT * FROM CTE ;
$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
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
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 !) ;)
This is a not too off-topic (IMHO), and may be helpful ...
I ran into this issue recently where I needed to execute a number of statements in a transaction and return some (very little) data which would indicate to a PHP script how the transaction was processed (records affected and any custom error code).
Sticking to the RAISE NOTICE and RAISE [EXCEPTION] paradigm, I found it best to return a JSON string in the NOTICE/EXCEPTION being returned. This way, all the PHP app would need to do is use pg_last_notice() or pg_last_error() to get and decode the JSON string.
RAISE EXCEPTION '{"std_response":{"affected":%,"error":%}}', var_affected, var_error_id;
RAISE NOTICE '{"std_response":{"affected":%,"error":%}}', var_affected, var_error_id;
Since the returning JSON object named "std_response" is actually a standard response for all these types of scripts, it makes it really easy to write unit tests since the wrapper function that loads and executes the SQL will always return a "std_response" object that can have it's values tested.
This paradigm should only be be used if you return TINY pieces of data in the RAISE message (although I have seen up to 96,000 characters returned this way - not sure what the limit is).
If you need to return a larger set of data, you will need to save the result-set into a table but at least you can still use this paradigm to isolate exactly which records belong the called SQL. i.e. place the data into a table with a UUID and return the UUID in the NOTICE like so:
RAISE NOTICE '{"table_name":{"affected":%,"uuid":%}}', var_affected, var_uuid;
The nice thing about this is that since it's still structured and describes which table to select the data from, it can also be used with unit tests in the app.
(Alternatively, you can also use Postgresql to store the result-set in memcache and have the application pickup the dataset from there, that way you don't have to deal with the disk I/O just for storing the result-set the app will use to generate some HTML then immediately throw away when the script finishes)
–
–
As the accepted solution states, do blocks aren't really designed to yield rows. However, you're here because you need a way to do something and yield rows. So here's an example function to yield rows with some primers on args and variables.
Table tweets
create or replace function sync_tweets(
src_pub_id text, -- function arguments
dst_pub_id text
) returns setof tweets as -- i.e. rows
declare
src_id int; -- temp function variables (not args)
dst_id int;
src_tweet text;
begin
-- query result into a temp variable
src_id := (select id from tweets where pub_id = src_pub_id);
-- query result into a temp variable (another way)
select tweet into src_tweet from tweets where id = src_id;
dst_id := (select id from tweets where pub_id = dst_pub_id);
update tweets set tweet=src_tweet where id = dst_id;
return query -- what you're here for
select * from tweets where pub_id in (src_pub_id, dst_pub_id);
$$ language plpgsql; -- need the language to avoid ERROR 42P13
-- Run it!
select * from sync_tweets('abc', 'def');
-- Postgres stores functions, drop if not needed anymore.
drop function if exists sync_tweets(text, text);
Outputs
__________________________________________________
| id (serial) | pub_id (text) | tweet (text) |
|---------------|-----------------|----------------|
| 1 | abc | hello world |
| 2 | def | blurb |
--------------------------------------------------
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
