18  

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.

DO命令不返回行。您可以发送通知或发出其他消息(使用语言plpgsql)，也可以编写到(临时)表，然后从中选择以绕过这个问题。

But really, you should 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.

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

If you don't want the function to be 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

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('2012-04-01'::date
                      ,'2016-01-01'::date
                      ,'1 month'::interval) m;

That's all.

这是所有。

5  

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:

更多细节在欧文建议的解决方法与临时表,应该真正的对这个问题的回答,因为问题是更多的针对“在开发过程中,如何快速编写一个代码块选择和查看结果”比实际解决这个查询(基本问题从一开始就“howto迅速发展/调试表值函数”)。虽然我必须说，我想把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 codebloat来实现这个功能:

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

(感谢欧文!);

2  

This is a not too off-topic (IMHO), and may be helpful ...

这不是一个太离题的话题(IMHO)，可能会有帮助……

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通知和RAISE [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消息中返回很小的数据片段时才应该使用这种模式(尽管我已经看到多达96000个字符以这种方式返回——不确定限制是什么)。如果您需要返回一组更大的数据，您将需要将结果集保存到一个表中，但至少您仍然可以使用此范例来精确地隔离哪些记录属于所调用的SQL。例如，将数据放入具有UUID的表中，并在通知中返回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结果集存储在memcache中,应用小数据集,这样你不必处理存储的磁盘I / O是结果集的应用程序将使用生成一些HTML脚本结束时,立即扔掉)