Postgresql - как получить набор результатов из хранимой процедуры postgresql?

Defining Substitution Variables

You can define variables, called substitution variables, for repeated use in a single script by using the SQL*Plus DEFINE command. Note that you can also define substitution variables to use in titles and to save your keystrokes (by defining a long string as the value for a variable with a short name).

Example 5-8 Defining a Substitution Variable

To define a substitution variable L_NAME and give it the value «SMITH», enter the following command:

DEFINE L_NAME = SMITH

To confirm the variable definition, enter DEFINE followed by the variable name:

DEFINE L_NAME

To list all substitution variable definitions, enter DEFINE by itself. Note that any substitution variable you define explicitly through DEFINE takes only CHAR values (that is, the value you assign to the variable is always treated as a CHAR datatype). You can define a substitution variable of datatype NUMBER implicitly through the ACCEPT command. You will learn more about the ACCEPT command.

Definer’s rights and invoker’s rights

When a PL/SQL procedure or function is defined, the optional clause of the or statement specifies whether the function or procedure executes with definer’s rights (, the default) or invoker’s rights (). Similarly, for procedures or functions in a package, the clause of the statement specifies whether each member function or procedure of the package executes with definer’s rights or invoker’s rights. The clause is shown in the syntax documentation for these statements, under in Oracle TimesTen In-Memory Database SQL Reference.

The setting affects the name resolution and privilege checking of SQL statements that a procedure or function issues at runtime. With definer’s rights, SQL name resolution and privilege checking operate as though the owner of the procedure or function (the definer, in whose schema it resides) is running it. With invoker’s rights, SQL name resolution and privilege checking simply operate as though the current user (the invoker) is running it.

Invoker’s rights would be useful in a scenario where you might want to grant broad privileges for a body of code, but would want that code to affect only each user’s own objects in his or her own schema.

Definer’s rights would be useful in a situation where you want all users to have access to the same centralized tables or other SQL objects, but only for the specific and limited actions that are executed by the procedure. The users would not have access to the SQL objects otherwise.

See for examples using definer’s and invoker’s rights.

How to unnest an array with element indexes

Sometimes we need to unnest a array with indexes. There are more ways how to do it:

Author: Pavel Stěhule (PostgreSQL 9.1)

CREATE OR REPLACE FUNCTION unnest_rownum(anyarray)
RETURNS TABLE (id int, element anyelement) AS $$
BEGIN
  id := 1;
  FOREACH element IN array $1
  LOOP
    RETURN NEXT;
    id := id + 1;
  END LOOP;
  RETURN;
END
$$ LANGUAGE plpgsql; 

postgres=# select * from unnest_rownum(ARRAY);
 id | element
----+---------
  1 | A
  2 | B
  3 | C
(3 rows)

Author: Tom Lane (PostgreSQL 9.3)

SELECT i, arraycol
   FROM tab,
        LATERAL generate_subscripts(arraycol, 1) as i;

9.4 supports clause WITH ORDINALITY:

postgres=# SELECT * FROM unnest(ARRAY) WITH ORDINALITY;
 unnest | ordinality 
--------+------------
 A      |          1
 D      |          2
 C      |          3
(3 rows)

простое решение с фиксированным типом возврата

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

имена мы оставим в пользу общих имен в возвращаемом типе.типы оставим тоже и бросим все С тип данных может быть приведен к .

как это работает?

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

в SQL-строке не ссылается на параметр функции, а на значение, переданное с помощью предложения. (Оба в их соответствующей области в этом простом примере.)
обратите внимание на значение примера для : каждый столбец приведен к типу .
этот вид кода очень уязвим для SQL-инъекций. Я использую для защиты от него. Объединение нескольких имен столбцов в переменной мешает использовать (и, как правило, плохая идея!). Убедитесь, что никакие плохие вещи не могут быть там каким-то другим способом, например, индивидуально запустив имена столбцов через вместо. А колонок
но все столбцы одного и того же тип (псевдоним )

как мы должны определить тип функции я прибегаю к тип в этом случае, который может содержать переменное число значений. Кроме того, я возвращаю массив с именами столбцов, чтобы вы также могли разобрать имена из результата:

звоните:

заменить в разговор с любым другим именем таблицы.

как это работает?

— это псевдо-тип данных, полиморфный тип, заполнитель для любого типа данных без массива. Все вхождения в функции вычислить тот же тип, что и во время выполнения. Предоставляя значение определенного типа в качестве аргумента функции, мы неявно определяем возвращаемый тип.
PostgreSQL автоматически определяет тип строки (составной тип данных) для каждой созданной таблицы, поэтому для каждой таблицы есть четко определенный тип. Это включает временные таблицы, которые удобный для ad-hoc пользы.
любой тип может быть . Поэтому мы сдаем значение, приведенное к типу таблицы.
теперь функция возвращает четко определенный тип строки, и мы можем использовать разложить в ряд и получить отдельные столбцы.
возвращает имя таблицы как объект с идентификатором типа . Когда автоматически преобразовано в идентификаторы автоматически с двойными кавычками и схемой-квалифицированный при необходимости. Поэтому SQL-инъекция невозможна. Это может даже иметь дело со схемами-квалифицированными именами таблиц здесь не.

Dynamic modification some record’s field

PL/pgSQL can modify record’s field only when field specification is static. Some fast C libraries are generally available, but when we have no necessary rights, then we cannot use it. Then we can use function:

CREATE OR REPLACE FUNCTION x.setfield2(anyelement, text, text)
 RETURNS anyelement
 LANGUAGE plpgsql
AS $function$
DECLARE 
  _name text;
  _values text[];
  _value text;
  _attnum int;
BEGIN
  FOR _name, _attnum
     IN SELECT a.attname, a.attnum
           FROM pg_catalog.pg_attribute a 
          WHERE a.attrelid = (SELECT typrelid
                                 FROM pg_type
                                WHERE oid = pg_typeof($1)::oid) 
  LOOP
    IF _name = $2 THEN
      _value := $3;
    ELSE
      EXECUTE 'SELECT (($1).' || quote_ident(_name) || ')::text' INTO _value USING $1;
    END IF;
    _values :=  COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', '');
  END LOOP;
  EXECUTE 'SELECT (' || pg_typeof($1)::text || '''(' || array_to_string(_values,',') || ')'').*' INTO $1; 
  RETURN $1;
END;
$function$;

postgres=# select setfield2(mypoint '(10,)', 'b', '33');
 setfield2 
───────────
 (10,33)
(1 row)

Time: 9,480 ms

Author: Pavel Stěhule

Erwin Brandstetter designed better (more simply, faster) solution:

-- Erwin 1
CREATE OR REPLACE FUNCTION x.setfield3(anyelement, text, text)
RETURNS anyelement
AS $body$
DECLARE
 _list text;

BEGIN
_list := (
   SELECT string_agg(x.fld, ',')
   FROM   (
      SELECT CASE WHEN a.attname = $2
              THEN quote_literal($3)
              ELSE quote_ident(a.attname)
             END AS fld
      FROM   pg_catalog.pg_attribute a 
      WHERE  a.attrelid = (SELECT typrelid
                           FROM   pg_type
                           WHERE  oid = pg_typeof($1)::oid) 
      ORDER BY a.attnum
   ) x
);

EXECUTE '
SELECT ' || _list || '
FROM   (SELECT $1.*) x'
USING  $1
INTO   $1;

RETURN $1;
END;
$body$ LANGUAGE plpgsql;

or modernised final version

CREATE FUNCTION f_setfield(INOUT _comp_val anyelement, _field text, _val text)
  RETURNS anyelement AS
$func$
BEGIN

EXECUTE 'SELECT ' || array_to_string(ARRAY(
      SELECT CASE WHEN attname = _field
                THEN '$2'
                ELSE '($1).' || quote_ident(attname)
             END AS fld
      FROM   pg_catalog.pg_attribute
      WHERE  attrelid = pg_typeof(_comp_val)::text::regclass
      AND    attnum > 0
      AND    attisdropped = FALSE
      ORDER  BY attnum
      ), ',')
USING  _comp_val, _val
INTO   _comp_val;

END
$func$ LANGUAGE plpgsql;

Изменить несколько столбцов в таблице

Синтаксис

Синтаксис для изменения нескольких столбцов в таблице в PostgreSQL (используя ALTER TABLE):

ALTER TABLE table_name
ALTER COLUMN column_name TYPE column_definition,
ALTER COLUMN column_name TYPE column_definition,
…
;

table_name: Имя таблицы для изменения.
column_name: Имя столбца, который нужно изменить в таблице.
column_definition: Измененный тип данных столбца.

Пример

Рассмотрим пример, который показывает, как изменить несколько столбцов в таблице PostgreSQL с помощью оператора ALTER TABLE.
Например:

PgSQL

ALTER TABLE order_details
ALTER COLUMN notes TYPE varchar(500),
ALTER COLUMN quantity TYPE numeric;

1
2
3

ALTERTABLEorder_details

ALTERCOLUMNnotesTYPEvarchar(500),

ALTERCOLUMNquantityTYPEnumeric;

В этом примере ALTER TABLE будут изменены два столбца таблицы order_details — notes и quantity.
Поле notes будет изменен на тип данных varchar(500), а столбец quantity будет изменен на тип данных numeric.

Trigger functions

The trigger functions have three main properties:

They are immutable
The statement returns a trigger type
They don’t take any input argument

The trigger functions can be defines in two setps:

1- Define the trigger function:

Where is a variable that contains a row of the table.

2- Define when the trigger will be fired:

Where:

BEFORE (or AFTER): define when the trigger function will be activated (before or after the operation)
FOR EACH ROW: the operation of the trigger function is executed once for each row.
FOR EACH STATEMENT: the operation of the trigger function is executed once for each statement.

If we want to remove our triggers:

Using REFCURSOR Bind Variables

SQL*Plus REFCURSOR bind variables allow SQL*Plus to fetch and format the results of a SELECT statement contained in a PL/SQL block.

REFCURSOR bind variables can also be used to reference PL/SQL cursor variables in stored procedures. This enables you to store SELECT statements in the database and reference them from SQL*Plus.

A REFCURSOR bind variable can also be returned from a stored function.

Example 5-14 Creating, Referencing, and Displaying REFCURSOR Bind Variables

To create, reference and display a REFCURSOR bind variable, first declare a local bind variable of the REFCURSOR datatype

VARIABLE employee_info REFCURSOR

Next, enter a PL/SQL block that uses the bind variable in an OPEN… FOR SELECT statement. This statement opens a cursor variable and executes a query. See the Oracle Database PL/SQL User’s Guide and Reference for information on the OPEN command and cursor variables.

In this example we are binding the SQL*Plus employee_info bind variable to the cursor variable.

BEGIN
OPEN :employee_info FOR SELECT EMPLOYEE_ID, SALARY
FROM                                            EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN' ;
END;
 /

The results from the SELECT statement can now be displayed in SQL*Plus with the PRINT command.

PRINT employee_info

The PRINT statement also closes the cursor. To reprint the results, the PL/SQL block must be executed again before using PRINT.

Example 5-15 Using REFCURSOR Variables in Stored Procedures

A REFCURSOR bind variable is passed as a parameter to a procedure. The parameter has a REF CURSOR type. First, define the type.

CREATE OR REPLACE PACKAGE EmpPack AS
  TYPE EmpInfoTyp IS REF CURSOR;
  PROCEDURE EmpInfoRpt (emp_cv IN OUT EmpInfoTyp);
END EmpPack;
/

Next, create the stored procedure containing an OPEN… FOR SELECT statement.

CREATE OR REPLACE PACKAGE BODY EmpPack AS
  PROCEDURE EmpInfoRpt (emp_cv IN OUT EmpInfoTyp) AS
  BEGIN
    OPEN emp_cv FOR SELECT EMPLOYEE_ID, SALARY
    FROM EMP_DETAILS_VIEW
    WHERE JOB_ID='SA_MAN' ;
  END;
END;
 /

Execute the procedure with a SQL*Plus bind variable as the parameter.

VARIABLE cv REFCURSOR
EXECUTE EmpPack.EmpInfoRpt(:cv)

Now print the bind variable.

PRINT cv

The procedure can be executed multiple times using the same or a different REFCURSOR bind variable.

VARIABLE pcv REFCURSOR
EXECUTE EmpInfo_rpt(:pcv)

PRINT pcv

Example 5-16 Using REFCURSOR Variables in Stored Functions

Create a stored function containing an OPEN… FOR SELECT statement:

CREATE OR REPLACE FUNCTION EmpInfo_fn RETURN -
cv_types.EmpInfo IS
resultset cv_types.EmpInfoTyp;
BEGIN
OPEN resultset FOR SELECT EMPLOYEE_ID, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';
RETURN(resultset);
END;
/

Execute the function.

VARIABLE rc REFCURSOR
EXECUTE :rc := EmpInfo_fn

Now print the bind variable.

PRINT rc

The function can be executed multiple times using the same or a different REFCURSOR bind variable.

EXECUTE :rc := EmpInfo_fn

Изменение таблиц

Последнее обновление: 19.03.2018

Нередко возникает изменить уже имеющуюся таблицу, в частности, добавить или удалить столбцы, изменить тип столбцов и т.д..
То есть потребуется изменить определение таблицы. Для этого применяется выражение ALTER TABLE,
которое имеет следующий формальный синтаксис:

ALTER TABLE название_таблицы
{ ADD название_столбца тип_данных_столбца  | 
  DROP COLUMN название_столбца |
  ALTER COLUMN название_столбца параметры_столбца |
  ADD  определение_ограничения |
  DROP  имя_ограничения}

Рассмотрим некоторые возможности по изменению таблицы.

Добавление нового столбца

Добавим в таблицу Customers новый столбец Phone:

ALTER TABLE Customers
ADD Phone CHARACTER VARYING(20) NULL;

Здесь столбец Phone имеет тип , и для него определен атрибут , то есть
столбец допускает отсутствие значения. Но что если нам надо добавить столбец, который не должен принимать значения NULL? Если в таблице есть
данные, то следующая команда не будет выполнена:

ALTER TABLE Customers
ADD Address CHARACTER VARYING(30) NOT NULL;

Поэтому в данном случае решение состоит в установке значения по умолчанию через атрибут :

ALTER TABLE Customers
ADD Address CHARACTER VARYING(30) NOT NULL DEFAULT 'Неизвестно';

Удалим столбец Address из таблицы Customers:

ALTER TABLE Customers
DROP COLUMN Address;

Изменение типа столбца

Для изменения типа применяется ключевое слово TYPE. Изменим в таблице Customers тип данных у столбца FirstName на (он же ):

ALTER TABLE Customers
ALTER COLUMN FirstName TYPE VARCHAR(50);

Изменение ограничений столбца

Для добавления ограничения применяется оператор SET, после которого указывается ограничение.
Например, установим для столбца FirstName ограничение :

ALTER TABLE Customers 
ALTER COLUMN FirstName 
SET NOT NULL;

Для удаления ограничения применяется оператор DROP, после которого указывается ограничение.
Например, удалим выше установленное ограничение:

ALTER TABLE Customers 
ALTER COLUMN FirstName 
DROP NOT NULL;

Изменение ограничений таблицы

Добавление ограничения CHECK:

ALTER TABLE Customers
ADD CHECK (Age > 0);

Добавление первичного ключа :

ALTER TABLE Customers 
ADD PRIMARY KEY (Id);

В данном случае предполагается, что в таблице уже есть столбец Id, который не имеет ограничения PRIMARY KEY. А с помощью вышеуказанного скрипта устанавливается ограничение PRIMARY KEY.

ALTER TABLE Customers
ADD UNIQUE (Email);

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

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

ALTER TABLE Customers
ADD CONSTRAINT phone_unique UNIQUE (Phone);

В данном случае ограничение будет называться «phone_unique».

Чтобы удалить ограничение, надо знать его имя, которое указывается после выражения . Например, удалим выше добавленное ограничение:

ALTER TABLE Customers
DROP CONSTRAINT phone_unique;

Переименование столбца и таблицы

Переименуем столбец Address в City:

ALTER TABLE Customers
RENAME COLUMN Address TO City;

Переименуем таблицу Customers в Users:

ALTER TABLE Customers
RENAME TO Users;

НазадВперед

Dynamic PL/pgSQL functions

In order to write dynamic PL/pgSQL queries the is used. The coommand it is used to generate dynamic commands inside the PL/pgSQL function. EXECUTE command it is used when the function involves different tables or different data types each time that the function is executed.

Syntax of EXECUTE command:

is an expression that contains que query to be executed.
The statement is optional. Is a record variable, row variable or a comma-separated list of simple variables and record/row fields, into which the results of the command will be stored.
The statement is optional. Supply values to be inserted into the command.

Features:

There is no plan caching for commands executed via EXECUTE. The command is always planned each time the statements are running.
Any required variable values must be inserted in the command string as it is constructed.
The INTO clause specifies where the results of a SQL command returning rows should be assigned. If a row or variable list is provided, it must exactly match the structure of the query’s results (when a record variable is used, it will configure itself to match the result structure automatically). If multiple rows are returned, only the first will be assigned to the INTO variable. If no rows are returned, NULL is assigned to the INTO variable(s). If no INTO clause is specified, the query results are discarded.
If the STRICT option is given, an error is reported unless the query produces exactly one row.

For more information and examples look at of the PostgreSQL documentation.

Using custom casting when you migrate from Oracle to PostgreSQL

Author: Pavel Stěhule

Oracle doesn’t support boolean data type, and developers use a varchar(1) or number(1) instead. It is a issue in PostgreSQL because this width is too less for storing a strings «true» or «false». But we can redefine a default casting (there is used a ugly hack — direct update of system tables under superuser rights). This solution is fast hack — changing to PostgreSQL’s boolean is much better.

CREATE OR REPLACE FUNCTION public.boolcast_varchar(boolean)
 RETURNS character varying
 LANGUAGE sql
AS $$
  SELECT CASE WHEN $1 THEN 't' ELSE 'f' END 
$$

UPDATE pg_cast SET castfunc='boolcast_varchar'::regproc, 
                   castcontext='i' 
  WHERE castsource=16 and casttarget=1043;

CREATE TABLE test(a varchar(1));
INSERT INTO test VALUES(true);

Как получить набор результатов из хранимой процедуры postgresql?

Defining Substitution Variables

Further reading

Definer’s rights and invoker’s rights

How to unnest an array with element indexes

простое решение с фиксированным типом возврата

как это работает?

как это работает?

Dynamic modification some record’s field

Изменить несколько столбцов в таблице

Синтаксис

Пример

Trigger functions

Using REFCURSOR Bind Variables

Изменение таблиц

Добавление нового столбца

Изменение типа столбца

Изменение ограничений столбца

Изменение ограничений таблицы

Переименование столбца и таблицы

Dynamic PL/pgSQL functions

Using custom casting when you migrate from Oracle to PostgreSQL