Рубрика: PostgreSQL

Microsoft SQL Server, PostgreSQL

Функция, возвращающая таблицу returns table — в PostgreSQL и MS SQL Server

SecretSilent

Хочу сегодня привести примеры создания функций для СУБД Microsoft SQL Server и PostgreSQL. Функция возвращает таблицу с 3-мя полями.

Как это пишется в MS SQL Server:

CREATE FUNCTION dbo.myFunction (--variables list--)
RETURNS result_table TABLE(field1 integer, field2 integer, field3 integer) 
AS
BEGIN
	insert into result_table 
	select val1, val2, val3 from another_table;
	return;
END;

А вот так в PostgreSQL:

CREATE OR REPLACE FUNCTION public.get_count_in_batch(--variables list--)
    RETURNS TABLE(field1 integer, field2 integer, field3 integer) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
begin
	return query select val1, val2, val3 from another_table;
end;
$BODY$;

Конечно, здесь примеры сокращены до минимума, только лишь с целью показать механизм возврата результата. Продолжить чтение «Функция, возвращающая таблицу returns table — в PostgreSQL и MS SQL Server»

PostgreSQL, Базы данных

PostgreSQL: Узнать данные о колонках таблицы

SecretSilent

Из таблицы information_schema.columns можно узнать много чего полезного о колонках той или иной таблицы или всех таблиц сразу. А также и представлений.

Узнаем все данные о колонках таблиц и представлений схемы ‘public’ базы данных ‘my_database’:

select * from information_schema.columns
where table_catalog = 'my_database' and table_schema = 'public'

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

Чтобы получить перечень колонок и присвоенные им номера по порядку таблицы products, надо написать

select column_name, ordinal_position
from information_schema.columns
where table_catalog = 'products' and 
table_schema = 'public' and 
table_name = 'products'
PostgreSQL, Базы данных

Insert … on conflict do update — Обновление записи при вставке, если возникает нарушение ограничения

SecretSilent

Как без предварительной проверки существования записи по первичному ключу, сразу вставлять ее, а при возникновения конфликта — просто обновить? И все за раз. Необходимо воспользоваться предложением on conflict … do …

Подробней об этом расскажет страница документации.

Я покажу пример, как при возникновении нарушения уникальности первичного ключа обновить данные. Продолжить чтение «Insert … on conflict do update — Обновление записи при вставке, если возникает нарушение ограничения»

PostgreSQL, SQL, Базы данных

Вывод даты с указанием часового и минутного смещения timezone в PostgreSQL

SecretSilent

Для выделения часового сдвига даты формата timestamp with time zone есть функция extract(timezone_hour from date_), где date_ — дата, откуда необходимо извлечь смещение. Чтобы узнать минутное смещение есть функция extract(timezone_minute from date_). Но они обе покажут значение длиной 1, если оно меньше 10. Например, 3 вместо 03.

Напишем функцию, которая преобразует переданное значение даты в формат записи 2018-04-04T14:10:29+03:00: Продолжить чтение «Вывод даты с указанием часового и минутного смещения timezone в PostgreSQL»