SQL statement

Using of SQL statements in SQL/PSM

Practically any SQL statement which could be parameterized by using of variables, can be inserted into the code. Only it's necessary to respect the following restrictions:

• statement CREATE FUNCTION can not be inserted,
• variable can not appear on the position where it would have the significance of the column identifier or the table identifier.

In the case that we are not able to comply with the conditions mentioned above, we have to use so called dynamic SQL. But then we have to be careful and pay attention to the safety, so that the dynamically executed statements could not be exploited for application attack by the SQL injection method.

The following fragment initialize the content of table series by the ascending sequence from 1 to n:

  SET _i = 1;
  WHILE _i <= 100 DO
    INSERT INTO series VALUES(_i);
    SET _i = _i + 1;
  END WHILE

Statement SELECT can not be used other way than with INTO clause. Exception is the usage of SQL statement as a table expression (see SRF Functions).

Usage of cursors

Open cursors can be declared and used in PL/pgPSM. That are those cursors that are declared outside of the PL/pgPSM function and into its body are subsequently transfered through the type REFCURSOR or the determining SQL statement is established until in the statement OPEN. Open cursor life cycle is not restricted to the block and so the cursor is, until its closure, accessible by its name even outside of the function.

Constrained cursors (determining SQL statement is given in the statement DECLARE) are restricted to the block, in which they are declared and after the block finishes, all opened constrained cursors will be closed automatically (that means that calling of the statement CLOSE is not necessary).

The cursor can be conceived as an analogy to the file descriptor. Before the usage, it has to be activated (opened) and after the usage deactivated (closed). For this purpose serve SQL statements OPEN and CLOSE. Data from the table which was made accessible by the cursor usage, we would obtain by the statement FETCH calling.

PL/pgPSM supports also so called scrollable cursors. Those cursors we can move to any position within the result set, no matter if it's entered relatively or absolutely. On the contrary so called forward cursor can be moved only forward by one row. If we don't use the attribute SCROLL in the cursor declaration, then the declared cursor will be of forward cursor type.

The following code includes an example of the scrollable cursor (the table is read from the bottom).

CREATE OR REPLACE FUNCTION foo()
RETURNS void AS
$$
  BEGIN
    DECLARE a, b integer;
    DECLARE sqlstate char(5);
    DECLARE cx SCROLL CURSOR FOR SELECT * FROM Foo;
    OPEN cx;
    -- fetch last record
    FETCH ABSOLUTE -1 FROM cx INTO a, b;
    WHILE sqlstate = '00000' DO
      PRINT a, b;
      FETCH PRIOR FROM cx INTO a, b;
    END WHILE;
    -- implicit cursor closing
  END;
$$ LANGUAGE plpgpsm;

Note: If it isn't necessary, use only the forward cursors. In this example we could avoid the using of scrollable cursor by utilizing of the clause ORDER BY DESC. In the case that it isn't necessary, do not use the cursors at all. Mass UPDATE of the table per one SQL statement is multiply faster than the UPDATE of one row repeated for each individual table row. Certainly exist cases, when the cursor usage is inevitable or more efficient, nevertheless in generalities it could be replaced by the mass SQL statement or by the usage of the SQL construction CASE.

The source of the cursor can be also preprocessed statement. Required parameters are handed over the clause USING in the statement OPEN.

Set returned functions

The output table can be the result of any SQL statement, whose result is a table (e.g. INSERT INTO RETURNING, but not only simply INSERT INTO), which will be used as PL/pgPSM function body. In that case deals always the function including only that statement. In the case that we use a compound statement, SQL statement can be parameter of the statement RETURN (utilization of so called table expression). Another possibility we have, is to generate table subsequently row after row by the statement RETURN NEXT. RETURN NEXT and RETURN can not be combined for generating of the output table.

CREATE OR REPLACE FUNCTION filter(p int)
RETURNS SETOF Foo AS
$$
  SELECT * 
     FROM Foo
    WHERE a = p;
$$ LANGUAGE plpgsql;

Because the result of SQL statement is directly handed over as the result of function, clause INTO is not used. No variables are filled by the result yet. It's the only case in PL/pgPSM, when can be used SELECT without the clause INTO.

We don't call those functions in the scalar context, but in the table one. That means that we view the function as parametrized table. Function is called with all IN and INOUT arguments. If it's necessary, the output columns can be renamed. Relatively often (if the table is generated only on basis of the global variables) is the calling of the function encapsulated into a view. That makes the calling of the function absolutely transparent.

-- scalar context
SELECT sin(0.33);

-- table context
SELECT * FROM filter(2);

-- table context and rename of parameters
SELECT * FROM filter(2) f(c1, c2);

-- fixing of arguments and creation of a view
CREATE VIEW Filter_2 AS 
       SELECT * FROM filter(2) f(c1, c2);

Holdable cursors

Cursors in PostgreSQL can be marked as holdable. This attribute causes that cursor is not aborted after the completion of transaction, but exists until we won't explicitly abort it. Unfortunately is not possible to use the holdable cursors directly in PL/pgPSM (they aren't supported by SPI interface, which PL/pgPSM uses for the access to the functions of PostgreSQL). Nevertheless PL/pgPSM supports REFCURSOR type. Items of this type hold the name of either existing or non-existing cursor. By usage of the variable of type refcursor it's possible to transfer the link into the procedure. By the returned value of the function of that type, on the contrary, cursor can be exported from the procedure. That is a step forward in the comparison with the standard. Attribute WITH HOLD in PL/pgPSM doesn't create a real holdable cursor (because it's not possible to create it at this moment), but guarantees that the cursor won't be closed automatically while abandoning the block and therefore would be possible then to export it.

CREATE OR REPLACE FUNCTION filter(p int)
RETURNS SETOF Foo AS
$$
  SELECT * <pre>
CREATE OR REPLACE FUNCTION fx(a integer) 
RETURNS refcursor AS
$$
  BEGIN
    DECLARE cx CURSOR WITH HOLD FOR
            SELECT * FROM Foo WHERE Foo.a = a;
    OPEN cx;
    RETURN cx;
  END;
$$ LANGUAGE plpgpsm;

root=# select fx(3);
 f  
----
 cx
(1 row)

root=# fetch cx;
 a | b 
---+---
 3 | 7
(1 row)

Attribute WITH HOLD can be also used for dynamic cursors. If it's not established alternatively, cursor won't be holdable.