Переменные в запросе postgresql: Postgres Pro Standard : Документация: 9.5: 40.5. Основные операторы : Компания Postgres Professional

postgresql — Postgre SQL переменные 8.4 в запросе

Вопрос задан 6 лет 4 месяца назад

Изменён 3 месяца назад

Просмотрен 1k раз

Подскажите пожалуйста можно ли в Postgre SQL 8.4 использовать переменные в самих запросах (не в функциях)? порывшись в инете, я так понял что их нет((

поэтому решил схитрить и использовать временную таблицу, чтобы из нее брать нужные параметры

К примеру:

CREATE TEMP TABLE tempp ON COMMIT DROP
AS
SELECT  'test.ss' AS imptable, --Таблица
    'company_id'  AS column1   --столбец ;
SELECT * FROM  (SELECT imptable FROM tempp) a
where (SELECT column1 FROM tempp) >1;

Думал что это сработает, и он будет смотреть в test.ss, но нет:

SELECT * FROM  (SELECT imptable FROM tempp)

делает выбор не из

test. ss а из tempp, ибо по факту он видит мой подзапрос как таблицу, а не как слово, которое я хочу подставить в скрипт

я хочу чтобы получился результат как если бы я написал

SELECT * FROM  test.ss

Подскажите как быть, если ли нормальные переменные, без использования функций? Если нет, то как можно еще в запросах использовать что то вроде переменных, изменять что то в одном месте, чтобы не править весь запрос

• sql
• postgresql

7

    UPDATE "STA_User" b
SET "ID" = a."ID", "Username"=a."Username"
FROM  (
   SELECT *
   FROM dblink('port=5432 dbname=Bekap user=postgres  password=123456'
               , 'SELECT "ID", "Username" FROM public."STA_User"')
               AS t("ID" INTEGER, "Username" TEXT)
      )a
WHERE b."ID" = a."ID";
-- Обновить таблицу с такими же ID как у исходной

‘a’ и ‘b’ преремнные

Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации

Почта

Необходима, но никому не показывается

Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки

Подстановка амперсанта

Вы разработали хороший запрос и возможно захотите использовать его в будущем. Иногда полезно иметь заготовку запроса, в которой указаны переменные, которые будут заменены на значения при выполнении запросы. Oracle предоставляет такой функционал в виде так называемой заменты переменной (ampersand substitution). Каждый элемент команды SELECT может быть подставлен во время выполнения, и путём оставления в запросе только ключевых элементов и ввода динамических переменных вы можете избежать много скучной и повторяемой работы. Мы рассмотрим подстановку переменной и ключевые слова DEFINE и VERIFY.

 

Замена переменных

 

Замену переменных можно рассматривать как установку заглушек. SQL запрос состоит из двух или более частей. Каждую часть можно разбить на подчасти, которые состоят из текста. Любой текст, подчасть или часть можно указать как заглушку.

 

Замена одиночным амперсандом

 

Самая простая и популярная форма SQL элемента это замена одинарного амперсанта. Символ амперсанта (&) выбран для назначения переменной в запросе и переменная состоит из амперсанта и названия переменной без пробела между ними. Когда запрос выполняется, серверный процесс Oracle видит переменную для замены и пытается определить её значение двумя способами. Во первых просматривается определена ли переменная в сессии пользователя. (Команду DEFINE мы рассмотрим чуть позже). Если переменная не определена, то пользовательский процесс запрашивает значение на которое будет заменена сооветствующая переменная. После того как значение введено, запрос выполняется сервером Oracle. Замена переменной амперсанта происходит в момент выполнения запроса и иногда называется связывание во время выполнения  (runtime binding) или подстановка во время выполнения (runtime substitution).

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

 

select employee_id, last_name, phone_number from employees

where last_name = &LASTNAME or employee_id = &EMPNO;

Когда вы запустите запрос, Oracle спросит входное значения для переменной с именем LASTNAME. Вы можете ввести фамилию сотрудника, если вы её знаете, например ‘King’. Если вы не знаете фамилию но знаете номер, вы можете ввести любой значение и нажать OK для ввода значения. Затем Oracle запросит значения для переменной EMPNO. После ввода значения, например 0, и нажатия OK, не остаётся переменных для замены и выполняется следующий запрос

 

select employee_id, last_name, phone_number from employees

where last_name = ‘King’ or employee_id = 0;

 

Переменной можно назначить любой символьное значение с валидным именем. Значение-литерала на которое будет произведена замена должно быть соответственного типа данных, иначе вы получите ошибку “ORA-00904: invalid identifier”. Если переменная подразумевает символьное значение или значение данных, то литерал должен быть заключен в одинарные кавычки. Полезным способом избежать ошибки типа данных является обрамление переменной в кавычки при необходимости. Тогда пользователю нет необходимости знать о типе данных.

 

select employee_id, last_name, phone_number from employees

where last_name = ‘&LASTNAME’ or employee_id = &EMPNO;

 

Двойной амперсант

 

Когда переменная используется несколько раз в запросе, Oracle будет запрашивать значение каждый раз когда встречается переменная в запросе. Для сложных запросов, это может быть очень неэффективно и приводить к ошибкам. Следующий запрос выбирает FIRST_NAME и LAST_NAME из таблицы EMPLOYEES который содержит символы в обоих столбцах

 

select first_name, last_name from employees

where last_name like ‘%&SEARCH%’ and first_name like ‘%&SEARCH%’;

 

Два условия одинаковые но они проверяются для разных столбцов. Когда запрос будет выполняться, вначале Oracle потребует ввод значения для переменной SEARCH используемом в первом условии со столбцом LAST_NAME. Затем потребуется ввод данных для замены значения переменной SEARCH используемом при сравнении с FIRST_NAME. Это привносит две проблемы. Во первых это неэффективно вводить одно и тоже значение два раза, и во вторых, что более важно, можно допустить опечатку при повторном вводе, так как Oracle не проверяет идентичность ввода значения для переменной. В этом примере, допущено логическое предположение что значение переменных должно быть одинаковым, но тот факт что у переменной одинаковое имя не значит для Oracle что значение должно быть одинаковым. На первом примере на рисунке 9-7 отображён результат выполнения запроса и ввода двух разных значений для подстановки переменной. В этом примере результат неверный, так как исходно требование было таким, что фамилия и имя сотрудника должны содержать одинаковые литералы.

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

На втором примере на рисунке 9-7 показано как использовать переменную SEARCH с двумя амперсантами в условии для столбца LAST_NAME, а затем переменная с тем же именем и одним амперсантом не затребует ввода значения. Когда запрос будет выполняться, Oracle запросит значение для SEARCH только один раз установит значение переменной SEARCH для сессии введённое значение и будет использовать его дальше. Для того чтобы сбросить это значение вам необходимо будет выполнить команду UNDEFINE.

 

Рисунок 9-7 Использование двойного амперсанта

 

Подстановка названий столбцов

 

До этого мы обсуждали подстановку литералов в секции WHERE, но можно заменять любой элемент SQL запроса. В следующем примере столбцы FIRST_NAME и JOB_ID статическиу и будут возвращены в любом случае, но третий столбец это переменная для подстановки во время выполнения с именем COL. Результат также сортируется используя этот столбец-переменную указанную в директиве ORDER BY

 

select first_name, job_id, &&col

from employees

where job_id in (‘MK_MAN’,’SA_MAN’)

order by &col;

 

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

 

Подстановка выражений и текста

 

Практически все элементы SQL запроса могут быть заменены во время выполнения. Ограничение, установленное Oracle – только первое слово должно быть статичным. В случае команды SELECT, минимальной командой будет ключевое слово SELECT, а всё остальное может быть заменено во время выполнения как в следующем примере

 

select &rest_of_statement;

 

Когда команда будет выполняться, будет выведен запрос для ввода значения для переменной с именем REST_OF_STATEMENT, которая добавится к слову SELECT. Лучшими кандидатами для использования подстановки переменных являются запросы, которые выполняются много раз, и незначительно отличаются друг от друга.

 

Команды DEFINE и VERIFY

 

Двойной амперсант используется чтобы избежать ввода значения несколько раз для одной и тоже переменной которая используется несколько раз в запросе. Когда встречается двойной амперсант, переменная сохраняется как переменная сессии. Когда запрос выполняется, все остальные вхождения переменной автоматически заменяются, используя сохраненную переменную. Все последующие выполнения запроса в той же сессии автоматически определяют значения переменной из переменной сессии. Это не всегда то что требуется и также ограничивает функционал подстановки переменной. Как-бы там ни было, Oracle предоставляет механизм для очистки переменных сессии. Команда VERIFY это команда с клиентской стороны и она контролирует будут или нет выводиться пользователю заменённые элементы перед выполнением запроса, который использует переменные.

 

DEFINE и UNDEFINE

 

Переменные сессии создаются, когда они встречаются в запросах использующих двойной амперсант. Они существуют всё время жизни сессии или пока они явно неудалены. Сессия завершается, когда пользователь выходит из клиентской программы к примеру, SQL *Plus, или, когда пользовательский процесс принудительно завершается.

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

 

UNDEFINE variablename

 

Рассмотрим простой динамический запрос, который выбирает статические столбцы и столбцы-переменные из таблицы EMPLOYEES и сортирует результат на основании столбца-переменной

 

select last_name, &&COLNAME

from employees where department_id=30 order by &COLNAME;

 

Первый раз когда запрос будет выполняться, появится запрос на ввод значения для переменной COLNAME. Предположим вы ввели SALARY. Это значение подменяется и запрос выполняется. Все последующие выполнения этого запроса в той же сессии не будут запрашивать значения для переменной COLNAME, так как уже создалась переменная и её значение SALARY. Переменная может быть удалена командой UNDEFINE COLNAME. После того как переменная сессии удалена – следующее выполнение запроса заново запросит пользователя ввести значение для переменной COLNAME.

У команды DEFINE два назначения. Её можно использовать чтобы просмотреть список всех переменных, установленных в текущей сессии, а также для явного задания значения переменной, используемой для подстановки в любых запросах на время жизни сессии. Синтаксис команды для двух этих вариаций

 

DEFINE;

DEFINE variable=value;

Как показано на рисунке 9-8, переменная с именем EMPNAME явно определяется со значением ‘King’. Команда DEFINE без параметров возвращает список переменных сессии явно заданных и исользованных в запросах с двойным амперсантом.

Рисунок 9-8 – Пример использования команд DEFINE и UNDEFINE

 

Затем выполняются два простых запроса в которых используется явно определённая переменная EMPNAME. Затем, переменная удаляется.

Поддержка переменных-сессии может быть включена или отключена, когда необходимо с помощью команды SET DEFINE ON|OFF. Команда SET это не команда SQL, это команда управления окружением SQL. Если вы указываете SET DEFINE OFF, клиентская программа (к примеру, SQL *Plus) не сохраняет переменные сессии, а считает, что амперсант — это обычный литерал. Таким образом команда SET DEFINE OFF|ON управляет доступна ли замена переменной для вашей сессии. Следующий запрос использует символ амперсанта как обычный литерал. Когда запрос будет выполняться, будет выведен запрос для ввода значения дла переменной SID.

select ‘Coda&Sid’ from dual;

 

Но если вы выключите подстановку переменной, то это запрос выполнится без запроса ввода данных

 

SET DEFINE OFF;

select ‘Coda&Sid’ from dual;

SET DEFINE ON;

 

После выполнения запроса, команда SET DEFINE ON может быть использована для включения функционала подстановки переменной обратно. Если подстановка переменной выключена и амперсанд используется не как литерал, запрос вернёт ошибку.

 

Команда VERIFY

 

Существует два типа команд при работе с Oracle: SQL команды и команды управления окружением SQL. Команда SELECT это команда языка SQL, команда SET управляет окружением. Доступно много параметров команды SET, но для контроля подстановки переменной доступны всего две: DEFINE и VERIFY.

Команда VERIFY управляет выводом введённого значения на экран, чтобы вы могли убедиться (verify) что подстановка осуществлена правильно. Сообщение выводится на экран в формате старого запроса, за ним идёт новый запрос с замененным значением. Команда VERIFY включает или выключает вывод на экран используя параметр OFF или ON. Если проверка выключена, то во время выполнения запроса с использованием амперсанта у вас запрашивается ввод значения, переменная заменяется, запрос выполняется и отображается результат. Если проверка включена и выполняется тот же запрос, то после ввода значения, но перед выводом результата, Oracle отображает раздел, в котором находилась переменная как старое значение с добавлением номеров строк, а затем ниже отображается новое значение с уже замененным значением.

производительность запросов — postgresql — объявление переменных, которые будут использоваться для диапазона start_time — end_time

Я относительно новичок в postgresql, поэтому простите меня, если это простая проблема.

Я пытаюсь реализовать запрос с аргументами start_time и end_time, в которые пользователь может вводить свои собственные даты для получения настраиваемых результатов.

Взяв фрагменты из других примеров в Интернете, я придумал нечто, что более или менее работает, однако я получаю сообщение об ошибке «запрос не имеет назначения для данных результатов».

Я просто пытаюсь вернуть данные из оператора select.

Есть что-то тривиальное, что я здесь упустил?

 сделать $$
ЗАЯВИТЬ
    Отметка времени start_date: = '2020-10-1';
    Временная метка end_date: = '2020-10-5';
начинать
выберите отдельный для (account.id, menu.name, kitchen_item.name)
...
запрос материал
....
где заказы размещены МЕЖДУ start_date И end_date
порядок по учетной записи.id по возрастанию, menu.name, kitchen_item.name по возрастанию, order_item.created по убыванию;
конец$$
 

РЕДАКТИРОВАТЬ — Это окончательный результат, который я разработал, который, похоже, работает для этой цели с использованием временных таблиц.

 ДЕЛАТЬ $$
ЗАЯВИТЬ
--Specify start date - конечная дата в формате ГГГГ-ММ-ДД.
Отметка времени start_date: = '2020-10-1';
Временная метка end_date: = '2020-10-5';
НАЧИНАТЬ
 СОЗДАТЬ ВРЕМЕННУЮ ТАБЛИЦУ temp_output ON COMMIT DROP AS
 выберите отдельный для (account. id, menu.name, kitchen_item.name)
 account.id как «Идентификатор учетной записи»,
 account.firstname как "Имя продавца",
 account.lastname как «Фамилия продавца»,
 account.email как "Электронная почта продавца",
 account.phone как «Телефон продавца»,
 address.address как "Адрес продавца (улица)",
 address.address_2 как "Адрес продавца 2",
 account.zip_code как «Почтовый индекс продавца»,
 address.neighborhood как «район продавца»,
 menu.name как "Имя активного меню",
 kitchen_item.name как "Блюда",
 kitchen_item.price как "Цена",
 kitchen_item.daily_max_orders как "Количество",
 menu.pickup_start_time как «Время начала»,
 menu.pickup_end_time как «Время окончания»,
 menu.repeat_mon как "понедельник",
 menu.repeat_tues как «вторник»,
 menu.repeat_wed как "среда",
 menu.repeat_thurs как «четверг»,
 menu.repeat_fri как «пятница»,
 menu.repeat_sat как «суббота»,
 menu.repeat_sun как «воскресенье»,
 order_item.created as "Дата последней продажи"
 со счета
 оставил магазин присоединений на account. 
  id = store.account_id
 левое меню присоединения к store.id = menu.store_id
 левое присоединение к menu_item на menu.id = menu_item.menu_id
 оставил соединение kitchen_item включенным (menu_item.kitchen_item_id = kitchen_item.id и store.id = kitchen_item.store_id)
 оставил заказы на соединение (orders.store_id = store.id)
 оставил соединение order_item включенным (order_item.order_id = orders.id)
 присоединиться к store_address на store.id = store_address.store_id
 адрес присоединения к store_address.address_id = address.id
 где orders.placed >= start_date AND orders.placed <= end_date
 порядок по учетной записи.id по возрастанию, menu.name, kitchen_item.name по возрастанию, order_item.created по убыванию;
КОНЕЦ $$;
ВЫБЕРИТЕ * ОТ temp_output;
 9Stack Overflow $$ DECLARE myvar integer; BEGIN SELECT 5 INTO myvar; УДАЛИТЬ ТАБЛИЦУ, ЕСЛИ СУЩЕСТВУЕТ tmp_table; CREATE TABLE tmp_table AS SELECT * FROM yourtable WHERE id = myvar; КОНЕЦ ...
 Как использовать переменные в простом скрипте PostgreSQL?
 
 
 Как объявить переменную в скрипте postgres? - Переполнение стека
 Как вы используете переменные скрипта в psql? - Stack Overflow
 Есть ли ПРОСТОЙ способ использовать переменную в запросе PostgreSQL?
 Дополнительная информация из stackoverflow. com
 Документация: 15: 43.3. Объявления — PostgreSQL
 www.postgresql.org › current › plpgsql-declarations
 Переменные PL/pgSQL могут иметь любой тип данных SQL, например integer, varchar и char. Вот несколько примеров объявления переменных:
 Ähnliche Fragen
 Как объявить переменную в запросе PostgreSQL?
 Можем ли мы объявить переменную в запросе SELECT?
 Как вы используете переменные в запросе?
 Что такое $$ в PostgreSQL?
 PostgreSQL: объявление переменных — TechOnTheNet
 www.techonthenet.com › postgresql › declare_vars
 Ниже приведен пример объявления переменной vSite в PostgreSQL. ОБЪЯВИТЬ vSite varchar;. В этом примере переменная с именем vSite объявляется как ...
 Объявление переменной в запросе PostgreSQL - Delft Stack
 www.delftstack.com › Howto › Postgres
 12.04.2022 · Мы используем ключевое слово set для объявления переменных на уровне сеанса или локальном уровне в настройках динамической конфигурации . Переменная, объявленная в сеансе- ...
 Объявить переменную в... · Использовать процедуры PostgreSQL...
 Переменные PL/pgSQL
 www.postgresqltutorial.com › postgresql-plpgsql › p..
 В этом руководстве мы покажем вам, как объявлять переменные PL/pgSQL, используя различные методы.
 PostgreSQL эквивалент переменных запроса MySQL?
 dba.stackexchange.com › вопросы › postgresql-equ...
 По сути, вам нужна возможность писать сценарии SQL. PSQL имеет условные выражения и переменные, а также возможность динамической обратной связи ...
 Как использовать переменную postgres в предложении select
 postgresql — объявление переменных, которые будут использоваться для start_time
 Объявление переменной для инструкции SELECT в функции Postgres
 Как использовать переменные в процедуре (не функции)? [закрыто]
 Weitere Ergebnisse von dba.stackexchange.com
 Как объявить переменную в запросе PostgreSQL - Intellipaat
 intellipaat. com › community › SQL
 1 Ответ ... Функция отсутствует в Постгрес SQL. Но вы можете сделать это только в pl/PgSQL (или другом pl/*), но не в простом SQL. ... Примечание. В этом единственном исключении ...
 Практика PostgreSQL — Использование переменных — Linuxtopia
 www.linuxtopia.org › PostgreSQL_x19832
 Переменные используются в коде PL/pgSQL для хранения изменяемых данных явно указанного типа. Все переменные, которые вы будете использовать в блоке кода, должны быть ...
 Как инициализировать и объявлять переменные в PostgreSQL? - eduCBA
 www.educba.com › ... › PostgreSQL Tutorial
 Рассмотрим следующий пример, чтобы понять объявление переменной с initial_value. ... DECLARE name_of_student VARCHAR DEFAULT 'Джон';. Приведенный выше пример ...
 Изучение переменных SQL для SQL Server, Oracle и PostgreSQL
 www.mssqltips.com › sqlservertip › sql-variables-sql...
 19.09.2022 · В PostgreSQL нет возможности использовать переменные в специальный запрос (если только не используются или более правильно не злоупотребляют CTE), но только в PL/pgSQL .