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

cygmwpex  于 2023-02-12  发布在  PostgreSQL
关注(0)|答案(5)|浏览(146)

我想移植下面的SQL代码从MS SQL服务器到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

这是我目前掌握的情况。

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$$;

正如你所看到的，我有一些问题时，想'打印'的加薪通知功能。但我设法解决了谷歌。
根据以前的经验，我可以看出Postgres语法与CTE非常相似，我只需要在CTE之前添加一个递归，因此唯一真正的问题是我必须定义一些变量，为此我需要一个do块。
从这个结果我有一个简单的问题：
如何在do块中"执行"选择查询？我想在pgAdmin3的"数据输出"选项卡中查看结果。
我不想创造一个函数。

postgresql

5条答案

按热度按时间
pkmbmrz7

pkmbmrz71#

DO命令与PL/pgSQL函数

DO命令不返回行。您可以发送NOTICES或RAISE其他消息（使用默认的LANGUAGE plpgsql），或者您可以写入（临时）表，然后从该表写入SELECT以解决此问题。
但实际上，可以使用create a function来定义返回类型，其中可以使用RETURNS clause和/或OUT和INOUT参数来定义返回类型，并以各种方式从函数返回。

  • 从带OUT参数的函数返回

如果您不希望保存一个函数并使其对其他连接可见，请考虑使用"临时"函数，这是一个未记录但已建立良好的特性：

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

generate_series()解决手头的问题

对于当前的问题，您似乎不需要 * 任何 * 这样的查询，而是使用以下简单的查询：

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〈〉小提琴here *

为什么？

  • 在PostgreSQL中生成两个日期之间的时间序列
赞(0）分享  回复(0）举报  2023-02-12
txu3uszq

txu3uszq2#

这里有更多关于Erwin建议的临时表的解决方法的细节，这应该是问题的真正答案，因为这个问题更多的是针对"在开发过程中，我如何快速地编写一个带有select的代码块并查看结果"，而不是解决这个实际的查询（从一开始的基本问题是"如何快速地开发/调试表值函数"）。
尽管我必须说我想把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;

这才是将查询快速转换为表值函数版本的基础，顺便说一句，如下所示：

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

顺便说一句，看看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
;

变成这样：

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

（谢谢欧文!）;）

赞(0）分享  回复(0）举报  2023-02-12
7cjasjjr

7cjasjjr3#

要从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;

通知

1.游标在事务范围中可见，因此应在一个事务中使用它。
1.游标变量的名称应与文本常量相同;
更多关于cursors的信息。技术源代码here（俄语）。

赞(0）分享  回复(0）举报  2023-02-12
jm2pwxwz

jm2pwxwz4#

这是一个不太离题（恕我直言），可能是有帮助的...
我最近遇到了这个问题，我需要在一个事务中执行许多语句，并返回一些（非常少的）数据，这些数据将向PHP脚本指示事务是如何处理的（受影响的记录和任何自定义错误代码）。
坚持RAISE NOTICE和RAISE [EXCEPTION]范例，我发现最好在返回的NOTICE/EXCEPTION中返回一个JSON字符串，这样，PHP应用程序只需要使用pg_last_notice（）或pg_last_error（）来获取和解码JSON字符串。
例如：

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

或

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

由于返回的JSON对象名为“std_response”，实际上是所有这些类型脚本的标准响应，因此编写单元测试非常容易，因为加载和执行SQL的 Package 器函数将始终返回一个“std_response”对象，该对象可以测试其值。
仅当在RAISE消息中返回微小的数据片段时，才应使用此范例（虽然我已经看到多达96，000个字符以这种方式返回-不确定限制是什么）。如果您需要返回更大的数据集，你需要将结果集保存到一个表中，但是至少你仍然可以使用这个范例来准确地分离出哪些记录属于被调用的SQL。将数据放入带有UUID的表中，并在NOTICE中返回UUID，如下所示：

RAISE NOTICE '{"table_name":{"affected":%,"uuid":%}}', var_affected, var_uuid;

它的好处是，由于它仍然是结构化的，并且描述了从哪个表中选择数据，因此它还可以用于应用程序中的单元测试。
（或者，您也可以使用Postgresql将结果集存储在memcache中，并让应用程序从那里拾取数据集，这样您就不必处理磁盘I/O，只是为了存储应用程序将用于生成一些HTML的结果集，然后在脚本完成时立即丢弃）

赞(0）分享  回复(0）举报  2023-02-12
6ie5vjzr

6ie5vjzr5#

正如公认的解决方案所指出的，do块实际上并不是为了生成行而设计的，然而，您在这里是因为您需要一种方法来执行某项操作并生成行，所以这里有一个示例函数，它使用args和变量作为一些引物来生成行。
表tweets
| id（序列号）|发布标识（文本）|推特（文本）|
| - ------|- ------|- ------|
| 1个|美国广播公司|你好世界|
| 第二章|定义|导语|

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);
end
$$ 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         |
  --------------------------------------------------
 */
赞(0）分享  回复(0）举报  2023-02-12
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com