SQL 如何在 DO 块中执行选择查询？

Question

提问by Stefan Steiger

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

我想将以下 SQL 代码从 MS SQL-Server 移植到 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.

根据之前的经验，我可以看出 Postgres 语法与 CTE 非常相似，我只需要在 CTE 之前添加一个递归，所以唯一真正的问题是我必须定义一些变量，为此我需要一个 do 块。

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 块中“执行”选择查询？我想在 pgAdmin3 的“数据输出”选项卡中查看结果。
我不想创建一个函数。

Answer 1

回答by Erwin Brandstetter

`DO`command vs. PL/pgSQL function

`DO`命令与 PL/pgSQL 函数

The DOcommand does not return rows. You can send NOTICESor RAISEother messages (with language plpgsql) or you can write to a (temporary) table and later SELECTfrom it to get around this.

该DO命令不返回行。您可以发送NOTICES或RAISE其他消息（使用语言 plpgsql），或者您可以写入（临时）表，然后再SELECT从它写入以解决此问题。

But really, create a (plpgsql) functioninstead, where you can define a return type with the RETURNSclause or OUT/ INOUTparametersand return from the functionin various ways.

但实际上，创建一个 (plpgsql) 函数，您可以在其中使用RETURNS子句或OUT/INOUT参数定义返回类型，并以各种方式从函数返回。

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?

如何在 PostgreSQL 中创建临时函数？

`generate_series()`for problem at hand

`generate_series()`对于手头的问题

For the problem at hand you don't seem to need anyof 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

db<>在这里摆弄

Why?

为什么？

Generating time series between two dates in PostgreSQL

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

Answer 2

回答by Stefan Steiger

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:

这里有更多关于 Erwin 建议的临时表的解决方法的详细信息，这应该是问题的真正答案，因为问题更倾向于“在开发过程中，我如何快速编写带有选择的代码块并查看结果“而不是解决这个实际查询（从一开始的基本问题是“如何快速开发/调试表值函数”）。

虽然我必须说我想对 generate_series 部分点赞 100 次；)

可以将结果选择到临时表中，
然后从 do 块外的临时表中进行选择，
如下所示：

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:

顺便说一句，看看 SQL-Server 代码膨胀来实现这个：

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 !) ;)

（感谢欧文！）；）

Answer 3

回答by MikeM

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).

我最近遇到了这个问题，我需要在事务中执行许多语句并返回一些（非常少）数据，这些数据将向 PHP 脚本指示事务是如何处理的（受影响的记录和任何自定义错误代码）。

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 NOTICE 和 RAISE [EXCEPTION] 范式，我发现最好在返回的 NOTICE/EXCEPTION 中返回一个 JSON 字符串。这样，PHP 应用程序所需要做的就是使用 pg_last_notice() 或 pg_last_error() 来获取和解码 JSON 字符串。

e.g.

例如

RAISE EXCEPTION '{"std_response":{"affected":%,"error":%}}', var_affected, var_error_id;

or

或者

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.

由于名为“std_response”的返回 JSON 对象实际上是所有这些类型脚本的标准响应，因此编写单元测试变得非常容易，因为加载和执行 SQL 的包装函数将始终返回一个“std_response”对象，该对象可以测试它的值。

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 消息中返回 TINY 数据时才应使用此范例（尽管我已经看到最多 96,000 个字符以这种方式返回 - 不确定限制是多少）。如果您需要返回更大的数据集，则需要将结果集保存到表中，但至少您仍然可以使用此范例来准确隔离哪些记录属于被调用的 SQL。即将数据放入带有 UUID 的表中，并在 NOTICE 中返回 UUID，如下所示：

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)

（或者，您也可以使用 Postgresql 将结果集存储在内存缓存中，并让应用程序从那里获取数据集，这样您就不必处理磁盘 I/O 仅用于存储应用程序的结果集将用于生成一些 HTML，然后在脚本完成后立即丢弃）

Answer 4

回答by Sergey Nemchinov

To get records from a DOanonymous code block you can use the following technique:

要从DO匿名代码块中获取记录，您可以使用以下技术：

DO $$
DECLARE
  _query text;
  _cursor CONSTANT refcursor := '_cursor';
BEGIN
  _query := 'SELECT * FROM table_name';
  OPEN _cursor FOR EXECUTE _query;
END
$$;

FETCH ALL FROM _cursor;

Notice

注意

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).

更多关于游标。技术来源在这里（俄语）。

SQL 如何在 DO 块中执行选择查询？

提问by Stefan Steiger

回答by Erwin Brandstetter

`DO`command vs. PL/pgSQL function

`DO`命令与 PL/pgSQL 函数

`generate_series()`for problem at hand

`generate_series()`对于手头的问题

回答by Stefan Steiger

回答by MikeM

回答by Sergey Nemchinov

相关推荐

最近更新

标签

SQL 如何在 DO 块中执行选择查询？

提问by Stefan Steiger

回答by Erwin Brandstetter

DOcommand vs. PL/pgSQL function

DO命令与 PL/pgSQL 函数

generate_series()for problem at hand

generate_series()对于手头的问题

回答by Stefan Steiger

回答by MikeM

回答by Sergey Nemchinov

相关推荐

SQL 在 Postgres 中搜索整数数组

SQL 数据库自动备份

SQL 计数空字段

如何在没有聚合函数的情况下在 sql server 中创建数据透视查询

相关推荐

最近更新

标签

`DO`command vs. PL/pgSQL function

`DO`命令与 PL/pgSQL 函数

`generate_series()`for problem at hand

`generate_series()`对于手头的问题