Как выполнить запрос select в блоке DO?

Ответ 1

Команда DO против функции PL/pgSQL

Команда DO не возвращает строки. Вы можете отправить NOTICES или RAISE другие сообщения (с языком plpgsql), или вы можете написать (временную) таблицу, а затем SELECT из нее, чтобы обойти это.

Но на самом деле вместо этого создайте (plpgsql) функцию, где вы можете определить тип возвращаемого значения с помощью предложения RETURNS или параметров OUT/INOUT и . из функции различными способами.

Если вы не хотите, чтобы функция сохранялась и отображалась для других подключений, рассмотрите "временную" функцию, которая является недокументированной, но хорошо зарекомендовавшей себя функцией:

• Как создать временную функцию в 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 & lt;> скрипка здесь

Почему?

• Генерация временных рядов между двумя датами в PostgreSQL

Ответ 2

Здесь более подробная информация об обходном пути с таблицей temp, о которой советовал Эрвин, который должен быть реальным ответом на вопрос, поскольку этот вопрос больше ориентирован на "во время разработки", как я могу быстро написать блок кода с помощью select и см. результаты ", чем для решения этого фактического запроса (основным вопросом с самого начала было" как быстро развить/отладить функции, связанные с таблицей ").


Хотя я должен сказать, что я хотел бы увеличить часть generate_series 100 раз;)

Можно выбрать результаты в таблице temp,
и выберите из таблицы temp вне блока 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 

(спасибо Эрвин!);)

Ответ 3

Это не слишком не по теме (IMHO), и может быть полезно...

Недавно я столкнулся с этой проблемой, когда мне нужно было выполнить несколько операторов в транзакции и вернуть некоторые (очень маленькие) данные, которые указывали бы на PHP script, как транзакция была обработана (записи затронуты и любая пользовательская ошибка код).

Придерживаясь парадигмы RAISE NOTICE и RAISE [EXCEPTION], я нашел, что лучше вернуть строку JSON в возвращаемом NOTICE/EXCEPTION. Таким образом, всем 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, всегда будет возвращать "std_response", объект, который может иметь проверенные значения.

Эта парадигма должна использоваться только в том случае, если вы возвращаете фрагменты данных TINY в сообщении RAISE (хотя я видел до 96 000 символов, возвращенных таким образом - не знаю, каков предел). Если вам нужно вернуть больший набор данных, вам нужно будет сохранить набор результатов в таблицу, но по крайней мере вы все равно можете использовать эту парадигму, чтобы изолировать именно те записи, которые принадлежат названному SQL. то есть поместите данные в таблицу с UUID и верните UUID в УВЕДОМЛЕНИЕ так:

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

Приятная вещь в том, что, поскольку он все еще структурирован и описывает, какую таблицу выбрать данные, он также может использоваться с модульными тестами в приложении.

(В качестве альтернативы вы также можете использовать Postgresql для хранения результирующего набора в memcache и получить приложение от набора данных оттуда, так что вам не нужно иметь дело с дисковым вводом-выводом только для хранения результатов, установите приложение, которое будет использоваться для создания некоторого HTML-кода, а затем сразу же выкинет, когда закончится script)