如何在DO块中执行选择查询? [英] How to perform a select query in a DO block?
问题描述
我想将下面的SQL代码从MS SQL Server移植到PostgreSQL.
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$$;
如您所见,当我想使用加薪通知功能进行打印"时,我遇到了一些问题.但是我设法通过Google解决了这个问题.
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.
根据以前的经验,我可以说CTE的Postgres语法是如此相似,我只需要在CTE之前添加一个递归,所以唯一的真正问题是我必须定义一些变量,对此我需要做阻止.
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.
从这个结果来看,我有一个简单的问题:
如何在do块中执行"选择查询?
我想在pgAdmin3的数据输出"选项卡中查看结果.
而且我不想创建一个函数.
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
命令与PL/pgSQL函数
DO
命令不会返回行.您可以发送NOTICES
或 RAISE
其他消息(使用语言plpgsql),或者您可以写入(临时)表,稍后再从中写入SELECT
即可解决此问题.
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.
但实际上,创建一个(plpgsql)函数,而在您可以使用 RETURNS
子句或OUT
/
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:
对于眼前的问题,您似乎不需要任何.请使用以下简单查询:
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<>小提琴此处
为什么?
这篇关于如何在DO块中执行选择查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!