LINUX.ORG.RU

PostgreSQL scripts examples

 , ,


0

2

Добрый день, уважаемые форумчане! Спустя 40-а лет работы в ИТ, в частности, с Misrosoft и СУБД MS SQL Server, добрался до PostgreSQL. Очень тому рад, поскольку вижу для себя совершенно иной продукт! Прошу подсказать азы. Буду всем очень благодарен.

Все, что мне удалось на сегодняшний день, это перенести базу данных (таблицы) из оригинала mssql и выполнить простейшие конструкции, как то:

deallocate reviews;
prepare reviews(int, varchar) AS
select r."TID", r."OrderID", $2 || r."Login" as login, to_char("RD", 'yyyy-mm-dd hh24:mi:ss') as rd from public."Reviews_tb" as r where "OrderID"=$1;
execute reviews(5480, '...');

Оказался в серьезном затруднении, поскольку все мои попытки использовать локальные объявления переменных внутри произвольного скрипта в теле запроса PgAdmin заканчиваются ничем…

Как написать и выполнить простой скрипт, содержащий переменные, внутренние вычисления и их использование в SQL-запросах, например, мне нужно воспроизвести функцию (пусть пока как есть, без pg-оптимизации):

CREATE FUNCTION [dbo].[GET_SplittedStrings_fn](
    @pSource    varchar(MAX), 
    @pSplitter  varchar(10)
)
returns @data TABLE(n int IDENTITY(0,1), item varchar(1000))
AS
BEGIN
    DECLARE 
        @p int,
        @l int,
        @value varchar(1000)

    set @l = LEN(@pSplitter)

    WHILE LEN(@pSource) > 0
    BEGIN
        select @p = CHARINDEX(@pSplitter, @pSource)
        if @p = 0
            select @p = LEN(@pSource) + @l
        set @value = LEFT(@pSource, @p-1)
        insert into @data select @value
        set @pSource = SUBSTRING(@pSource, @p+@l, LEN(@pSource))
    END
    return
END

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

Моя проблема не только в том, как получить аналог в Postgres, но и в том, как «проиграть» код. В mssql нет особых ограничений в создании скриптовых конструкций, к примеру, код в обрамлении BEGIN … END можно легко выполнить в простом New Query. В PgAdmin я никак не могу найти вариант для подобных тестов, кроме моего «reviews». Получается как-то совсем неуклюже.


Оффтоп, но у Вас правда за 40 лет ниразу не возникло желание посмотреть на альтернативы/конкурентов используемых Вами СУБД?

IIIypuk ★★★ ()
Ответ на: комментарий от IIIypuk

Бывало)) DBase, Clipper, Informix, MySQL, SQLite. Но к psql руки не доходили. Что касается «аналогов», признаю, законный вопрос. Дело в том, что mssql настолько хорош, что и нужды особой не было. Вот решил на старость лет.

mkaro ()

Посоветую поставить Valentina Studio. В PHPшном поделии pgAdmin не знаю насколько всё хорошо, но в VStudio удобный визуальный редактор таблиц, прекрасный SQL executer, а ещё оно в GUI умеет готовые запросы INSERT, UPDATE (и другие) генерировать.

По сабжу топика помочь не могу, не за компьютером сейчас.

https://www.valentina-db.com/get-free-valentina-studio

IIIypuk ★★★ ()

Аналог получился вот такой:

CREATE OR REPLACE FUNCTION public."GET_SplittedStrings_fn"(
	text,
	character varying DEFAULT ':'::character varying)
    RETURNS TABLE(n integer, item text) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    RETURN QUERY
    SELECT (row_number() OVER()-1)::integer, * from unnest(string_to_array($1, $2));
END;
$BODY$;

но чувствую, что-то не так.

mkaro ()

Смешно вот мой кусок скрипта для mysql

## Удаляем таблицы и снова их востанавливаем
Drop="DROP TABLE rpa_rep;"
Drop2="DROP TABLE rpa_bul;"
mysql -u meteo  meteo  -e "$Drop"
mysql -u meteo  meteo  -e "$Drop2"
sleep 2
/home/meteo/bin/sql_control
sleep 2
#############################
zapros="select  rep_id, COUNT(*) from rpa_rep where MSG_KEY REGEXP \"V.RA..RUUS|W.RA..RUUS\" and rep_id
 REGEXP \"^3[1-2]\" and time  REGEXP \"^....-$mesec-\"  GROUP BY rep_id ;"

RES=`mysql -u $user  $db -e "$zapros"`
echo -e  $zag >  $itog
echo  "$RES" > $TMP
### Убираем заголовок, разбиваем на колонки, пишем в файл
cat $TMP |sed '1d' | pr -l1 -3 >> $itog
#####Добавляем конец текста

Bootmen ☆☆☆ ()
Ответ на: комментарий от mkaro

Можно как-то так:

SELECT
	 a.n-1	AS n
	,a.item AS item
FROM
	UNNEST (
		string_to_array (
			 'asdf:asdfdsf::sdas'
			,':'
		)
	) WITH ORDINALITY a(item, n)
;

Toxo2 ★★☆ ()

Один приём замены переменной - исполлзование конструкций CTE (common table expressions). Их синтаксис и возможностм зависят от СУБД, так что смотрите документацию по PostgreSQL.

Partisan ★★★★ ()

В популярных СУБД есть процедурный SQL. Переменные могут быть только в процедурном SQL. В MS SQL Server в одном T-SQL смешаны процедурные и непроцедурные средства. Но это нетипично. В PistgreSQL процедурный SQL называется PL/pgSQL, о нём в документации есть отдельная глава.

Partisan ★★★★ ()

Можно ли в каком-либо из диалектов PostgreSQL определить функцию, которая будет возвращать произвольный набор данных? О процедурах просто нет речи, насколько я понимаю, в pg это невозможно, ну а функция может быть с произвольным select-ом, либо же в ее определении обязательно должен быть задан соответствующий, предварительно декларированный, тип данных.

mkaro ()
Ответ на: комментарий от mkaro

Не понял, что значит произвольный. Но функция может возвращать RECORD (запись), которая может содержать набор полей разных типов. Или ARRAY (массив), содержащий элементы одного типаб

Partisan ★★★★ ()

Оказался в серьезном затруднении, поскольку все мои попытки использовать локальные объявления переменных внутри произвольного скрипта в теле запроса

Внезапно, постгрес не умеет в переменные в запросе. Да, это очень печальная новость.

Как вариант - делать CTE вместо переменных (естественно, подходит в очень ограниченных случаях и сильно усложняет синтаксис).

Shadow ★★★★★ ()
Ответ на: комментарий от mkaro

mssql настолько хорош

У него своих приколов хватает, типа, как получить числовой ряд 1...n запросом... Очень, очень нетривиально.

Shadow ★★★★★ ()
Ответ на: комментарий от Shadow

Внезапно, постгрес не умеет в переменные в запросе. Да, это очень печальная новость.

На эту чушь я уже ответил. Так что не новость.

Внезапно нашёлся пейсатель, который не читатель.

Partisan ★★★★ ()
Ответ на: комментарий от Partisan

Ладно, ребята, если это вы ко мне, это да, я знаю, стебануть да потролить это хлебом не корми.

Всем благодарен за позитив. Разберемся, не первый год замужем.

mkaro ()
Ответ на: комментарий от Partisan

Произвольный - это не требующий каких-либо предварительных определений. Пишу create function, а еще лучше create procedure, а в тело ставлю select и ни очем больше не думаю. Короче, нельзя, я все понял. Нужно определить так называемый базовый тип, почему он «базовый» хрен его знает. Документация ужасная, все вперемешку, и форматы, и язык, и тут же админка и установка, диалект Си почему-то первым делом и т.д.

mkaro ()
Ответ на: комментарий от Shadow

Приколов хватает, да есть неудобства, но для примитива в самый раз. А работает хорошо, никакой излишней головной боли на пустом месте.

mkaro ()
Ответ на: комментарий от mkaro

О процедурах просто нет речи, насколько я понимаю, в pg это невозможно

Процедуры есть начиная с PostgreSQL 11

определить функцию, которая будет возвращать произвольный набор данных?

Самое простое - возвращайте json

Toxo2 ★★☆ ()
Ответ на: комментарий от Toxo2

Не просто процедуры, а с select-ом в качестве возвращаемого значения. Для mssql это совсем не проблема, например:

CREATE PROCEDURE [dbo].[GET_StocksChildren_sp] 
    @p_MD  int
AS
BEGIN
    SET NOCOUNT ON

    SELECT 
        s.TID, 
        s.Name, 
        s.ShortName,
        s.NodeLevel,
        s.NodeCode,
        s.Children,
        (select count(*) from [dbo].Orders_tb o where o.StockListID in (
            select TID from [dbo].DIC_StockList_tb where NodeCode like s.NodeCode+'%'
            )
            and o.MD=@p_MD
        ) as Orders
    FROM
        [dbo].DIC_StockList_tb AS s
    ORDER BY NodeCode

END

Как видите, никаких предварительных определений. Работает отлично.

В pg можно только так:

CREATE TYPE stocks_children AS (
    "TID" integer,
    "Name" character varying,
    "ShortName" character varying,
    "NodeLevel" integer,
    "NodeCode" character varying,
    "Children" integer,
    "orders" bigint
);
--drop function public."GET_StocksChildren_fn";
CREATE OR REPLACE FUNCTION public."GET_StocksChildren_fn"(p_md integer)
    RETURNS SETOF stocks_children
    LANGUAGE SQL
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

    SELECT 
        s."TID", 
        s."Name", 
        s."ShortName",
        s."NodeLevel",
        s."NodeCode",
        s."Children",
        (select count(*) from public."Orders_tb" o where o."StockListID" > 0 and o."StockListID" in (
            select "TID" from public."DIC_StockList_tb" where "NodeCode" like s."NodeCode"||'%') and o."MD"=p_md
        ) as orders
    FROM
        public."DIC_StockList_tb" AS s
    ORDER BY "NodeCode";

$BODY$;

Сейчас бьюсь над тем, как обновиться, если что-то надо)). Надо удалить и тип, и функцию. И вот опять же, где-то фраза «OR_REPLACE» есть в синтаксисе, где-то нужен «IF NOT EXISTS». Но почему-то для таблицы он предусмотрен, а вот для типа не предусмотрен, нельзя записать: CREATE IF NOT EXISTS TYPE. Где же однородность диалекта).

Все это я называю головной болью на пустом месте. Особенно кавычки для идентификаторов, отличных от lower case.

mkaro ()
Ответ на: комментарий от mkaro

Все это я называю головной болью на пустом месте. Особенно кавычки для идентификаторов, отличных от lower case.

По-моему это не так должно назваться, но да бог с вами - пишите, как хотите, лишь бы это приносило радость и удовольствие.

Настоящая головная боль начнётся, когда ПГ перестанет попадать в ваши индексы и не находить оптимального плана запроса. Но до этого, видимо, ещё не скоро.

Toxo2 ★★☆ ()
Ответ на: комментарий от Toxo2

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

А что должно не так назваться? А, ясно, это опять про меня).

mkaro ()
Последнее исправление: mkaro (всего исправлений: 1)
Ответ на: комментарий от mkaro

Буду благодарен за комментарии.

/***
  Пример шаблона скрипта для создания функции, возвращающей произвольный набор данных (определяемый набор полей).
  Используем опеределение составного типа данных (Schemas/<имя>/Types)
*/

/* Вариант 1
-- В качестве имени схемы: public
-- Удалить все объекты, содержащие ссылку на определяемый тип данных
DROP FUNCTION IF EXISTS public."GET_StocksChildren_fn"(integer);
-- Удалить существующий тип данных
DROP TYPE IF EXISTS public.stocks_children;
*/

-- Вариант 2
-- Каскадно удалить тип данных и все зависимые объекты
DROP TYPE IF EXISTS public.stocks_children CASCADE;

-- Создать новый (одноименный переопределяемый) тип данных
CREATE TYPE public.stocks_children AS (
    "TID" integer,
    "Name" character varying,
    "ShortName" character varying,
    "NodeLevel" integer,
    "NodeCode" character varying,
    "Children" integer,
    "orders" bigint
);

-- Создать порождаемые объекты
CREATE OR REPLACE FUNCTION public."GET_StocksChildren_fn"(p_md integer)
    RETURNS SETOF stocks_children
    LANGUAGE SQL
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

	SELECT 
        s."TID", 
        s."Name", 
        s."ShortName",
        s."NodeLevel",
        s."NodeCode",
        s."Children",
        (select count(*) from public."Orders_tb" o where o."StockListID" in (
            select "TID" from public."DIC_StockList_tb" where "NodeCode" like s."NodeCode"||'%') and o."MD"=p_md
        ) as orders
    FROM
        public."DIC_StockList_tb" AS s
    ORDER BY "NodeCode"

$BODY$;
mkaro ()
Последнее исправление: mkaro (всего исправлений: 1)
Для того чтобы оставить комментарий войдите или зарегистрируйтесь.