PostgreSQL

Скачать в pdf «PostgreSQL»
18.3    Pl/PGSQL Functions


Pl/PGSQL is another language intended for server-side functions. It is a true programming language. While SQL functions allow only argument substitution, Pl/PGSQL includes features such as variables, conditional evaluation, and looping.


Pl/PGSQL is not installed in each database by default. To use it in database test, you must install it by running createlang plpgsql test from the operating system prompt.


Figure
18.7 shows a Pl/PGSQL version of the SQL function getstatename from Figure 18.5. It illustrates several Pl/PGSQL features:


test=> SELECT customer.name, statename.name test-> FROM customer, statename test-> WHERE customer.state = statename.code test-> ORDER BY eustomer.name;


name    |    name


———————————+————————


Fleer Gearworks, Inc.    |    Alabama


Mark Middleton    |    Indiana


Mike Nichols    |    Florida


(3 rows)


test=> SELECT customer.name, getstatename(customer.state) test-> FROM customer test-> ORDER BY customer.name;


name    |    getstatename


———————————+————————


Fleer Gearworks, Inc.    |    Alabama


Mark Middleton    |    Indiana


Mike Nichols    |    Florida


(3 rows)


test=> CREATE FUNCTION getstatename2(text) test-> RETURNS text test-> AS ‘DECLARE ret TEXT; test’>    BEGIN


test’>    SELECT INTO ret CAST(name AS TEXT)


test’>    FROM statename


test’>    WHERE code = $1;


test’>    RETURN ret;


test’>    END;’


test’> LANGUAGE ‘plpgsql’;


CREATE


declare Defines variables used in the function.


select into A special form of select that allows query results to be placed into variables. It should not be confused with select * into.


return Exits and returns a value from the function.

Скачать в pdf «PostgreSQL»