如何在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:

• 如何在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<>小提琴此处

为什么?

• 在PostgreSQL中的两个日期之间生成时间序列

这篇关于如何在DO块中执行选择查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！