Jemný úvod do jazyka PL/pgSQL PostgreSQL

CREATE [OR REPLACE] FUNCTION název (typ_argumenty [,...]) 
  RETURNS návr_typ AS '
    tělo fce
  'LANGUAGE 'plpgsql'
  [EXTERNAL SECURITY INVOKER | EXTERNAL SECURITY DEFINER]

createlang plpgsql vaše_db

CREATE OR REPLACE FUNCTION Soucet(int,int) RETURNS int AS '
BEGIN
  /*
    Moje první triviální PL/pgSQL funkce.
  */
  RETURN $1 + $2;
END;
' LANGUAGE 'plpgsql'; 

SELECT INTO cíl výraz FROM ...;

GET DIAGNOSTICS promenna = ROW_COUNT;

CREATE OR REPLACE FUNCTION radku1() RETURNS int AS '
  DECLARE r int;
  BEGIN
    PERFORM * FROM jmena;
    GET DIAGNOSTICS r = ROW_COUNT;
    RETURN r;
  END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION radku2() RETURNS int AS '
  DECLARE r int;
  BEGIN
    SELECT INTO r count(*) FROM jmena;
    RETURN r;
  END;
' LANGUAGE 'plpgsql';

DECLARE jméno [CONSTANT] typ [NOT NULL][DEFAULT|(:=)hodnota]

CREATE OR REPLACE FUNCTION MonthName1 (INTEGER) RETURNS VARCHAR(10) AS '
  DECLARE vysledek RECORD;
  BEGIN
    IF $1 <1 OR $1 > 12 THEN
      RAISE EXCEPTION ''Parametr je mimo přípustné meze\n'';
    END IF;
    SELECT INTO vysledek 
      CASE $1
        WHEN  1 THEN ''Leden''
        WHEN  2 THEN ''Únor''
        WHEN  3 THEN ''Březen''
        WHEN  4 THEN ''Duben''
        WHEN  5 THEN ''Květen''
        WHEN  6 THEN ''Červen''
        WHEN  7 THEN ''Červenec''
        WHEN  8 THEN ''Srpen''
        WHEN  9 THEN ''Září''
        WHEN 10 THEN ''Říjen''
        WHEN 11 THEN ''Listopad''
        WHEN 12 THEN ''Prosinec''
      END::VARCHAR(10) AS retval;
    RETURN vysledek.retval;
  END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION tabsin(float,float,float) RETURNS BOOL AS '
  DECLARE i NUMERIC(5,4); krok NUMERIC(5,4); do NUMERIC(5,4);
  BEGIN
    IF NOT EXISTS(SELECT relname FROM pg_class
      WHERE relname = ''tabsin'' AND relkind=''r'') THEN
      RAISE NOTICE ''Vytvářím tabulku tabsin'';
      CREATE TABLE tabsin (x NUMERIC(5,4) PRIMARY KEY, fx float);
    ELSE 
      RAISE NOTICE ''Ruším všechny zaznamy v tabulce tabsin'';
      TRUNCATE TABLE tabsin;
    END IF;
    i := CAST($1 AS NUMERIC(5,4));
    do := CAST($2 AS NUMERIC(5,4));
    krok := CAST($3 AS NUMERIC(5,4));
    WHILE i <do LOOP
      INSERT INTO tabsin VALUES(CAST(i AS NUMERIC(5,4)), SIN(CAST(i AS float)));
      i := i + krok;
    END LOOP;
    RETURN TRUE;
  END;
' LANGUAGE 'plpgsql';

SELECT * FROM tabsin WHERE x=0.1234::NUMERIC(5,4);

<<hlavni>>
  LOOP
    EXIT hlavni WHEN c > 10;
    RAISE NOTICE ''%'', c;
    c := c + 1;
  END LOOP;

CREATE OR REPLACE FUNCTION velnedele(INTEGER) RETURNS DATE AS '
DECLARE B INTEGER; D INTEGER; E INTEGER; Q INTEGER;
  DD INTEGER; MM INTEGER;
BEGIN
    IF $1 < 1900 OR $1 > 2099 THEN
      RAISE EXCEPTION ''Out of range'';
  END IF;
  B := 255 - 11 * ($1 % 19); D := ((B - 21) % 30) + 21;
  IF D > 38 THEN D := D - 1; END IF;
  E := ($1 + $1/4 + D + 1) % 7; Q := D + 7 - E;
  IF Q < 32 THEN DD:=Q; MM := 3; ELSE DD := Q - 31; MM := 4; END IF;
  RETURN to_date(to_char(DD, ''00'') || 
    to_char(MM, ''00'') || to_char($1,''0000''), '' DD MM YYYY'');
END;
' LANGUAGE plpgsql; 

CREATE OR REPLACE FUNCTION clean(VARCHAR) RETURNS VARCHAR AS '
DECLARE pom varchar DEFAULT $1;
BEGIN pom := to_ascii(lower(trim(both FROM pom)));
  pom = replace(pom,''c.'','' '');
  pom = replace(pom,''j.'','' '');
  pom = replace(pom,''cj.'','' '');
  pom = replace(pom,''cj '','' '');

  pom = replace(pom,''vyhl.'',''vyhlaska '');
  pom = replace(pom,''vyhl '',''vyhlaska '');
  pom = replace(pom,''.'','''');

  WHILE position(''  '' IN pom) <> 0 LOOP
    pom := replace(pom, ''  '','' '');
  END LOOP;
  RETURN trim(both FROM pom);
END;
' LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION drop_students_databases() RETURNS INTEGER AS '
DECLARE
  helpstr VARCHAR(300);
BEGIN
  helpstr  := ''Funkce pro zrušení všech databází uživatelů jejichž\n'' ||
    ''jméno (jméno uživatele) vyhovuje masce příkazu LIKE.\n\n'' ||
    ''např. SELECT drop_students_databases('' || quote_literal(''group%'') || '')\n\n'' ||
    ''pozn. musíte být přihlášen jako uživatel postgres.\n'' ||
    ''autor: Pavel Stěhule, stehule@kix.fsv.cvut.cz 19.1.2002\n'';

  RAISE NOTICE ''%'', helpstr;
  RETURN 0;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION drop_students_databases(varchar(100)) RETURNS INTEGER AS '
DECLARE 
  db RECORD;
  deleted INTEGER := 0;
  helpstr VARCHAR(300);
BEGIN 
  IF length($1) = 0 OR $1 ISNULL OR current_user() <> ''postgres'' THEN
    helpstr  := ''Funkce pro zrušení všech databází uživatelů jejichž\n'' ||
      ''jméno (jméno uživatele) vyhovuje masce příkazu LIKE.\n\n'' ||
      ''např. SELECT drop_students_databases('' || quote_literal(''group%'') || '')\n\n'' ||
      ''pozn. musíte být přihlášen jako uživatel postgres.\n'' ||
      ''autor: Pavel Stěhule, stehule@kix.fsv.cvut.cz 19.1.2002\n'';
    IF current_user <> ''postgres'' THEN
      RAISE EXCEPTION ''Funkci může volat pouze uživatel postgres\n\n%'', helpstr;
    ELSE
      RAISE EXCEPTION ''Argument funkce musí být neprázdný řetězec\n\n%'', helpstr;    
    END IF;
  END IF;
  
  FOR db IN 
    select datname, usename from pg_database, pg_user 
      where datdba=usesysid and usename like $1
  LOOP
    deleted := deleted + 1;
    RAISE NOTICE ''Drop database %,%'', db.datname, db.usename;
    EXECUTE ''drop database "'' || db.datname || ''"'';
  END LOOP;
  RETURN deleted;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION check_ISBN(CHAR(12)) RETURNS boolean AS '
  DECLARE 
    ISBN ALIAS FOR $1;
    pos INTEGER; asc INTEGER; suma INTEGER DEFAULT 0;
    weight INTEGER[] DEFAULT ''{10,9,8,7,6,5,4,3,2,1}'';  -- pro ISSN {8,7,6,5,4,3,2,1}
    digits INTEGER DEFAULT 1;
  BEGIN 
    FOR pos IN 1..length(ISBN) LOOP
      asc := ascii(substr(ISBN,pos,1));
      IF asc IN (88, 120) THEN -- ISDN muze obsahovat kontrolni cislo X
        suma := suma + 10;
        digits := digits + 1;
      ELSIF asc >= 48 AND asc <= 57 THEN
        suma := suma + (asc - 48)*weight[digits];
        digits := digits + 1;
      END IF;
    END LOOP;
    IF digits <> 11 THEN -- pro ISSN <> 9
      RETURN ''f'';
    ELSE
      RETURN (suma % 11) = 0;
    END IF;
  END;
' LANGUAGE 'plpgsql' WITH (isstrict);

CREATE TABLE ZCHU_RezervacniKnihy (
  id INTEGER PRIMARY KEY DEFAULT nextval('doc_seq'),
  idoo INTEGER NOT NULL,  -- id objektu ochrany
  taboo CHAR(1) NOT NULL CHECK(taboo IN ('m','v','s')),
  isign varchar(50) NOT NULL UNIQUE CHECK (isign <> ''),
  typ CHAR(2) NOT NULL REFERENCES cv_TypDocRK(kod),
  autor_prijmeni VARCHAR(30) NOT NULL CHECK (autor_prijmeni <> ''), 
  autor_jmeno VARCHAR(30) NULL CHECK (autor_jmeno <> ''),
  nazev VARCHAR(100) NOT NULL CHECK (nazev <> ''),
  poznamka VARCHAR(300) NULL,
  rok_vydani INTEGER NULL CHECK (rok_vydani > 1918 AND rok_vydani <EXTRACT(year FROM current_date)),
  zalozeno DATE DEFAULT current_date NOT NULL,
  vyrazeno DATE NULL CHECK (vyrazeno > zalozeno),
  isbn CHAR(12) NULL CHECK (check_ISBN(isbn)),
  url VARCHAR(100) NULL CHECK (url ~* '^(file|http|ftp)://.+')
); 

omega=# select '1hour'::interval / '10min'::interval;
ERROR:  operator does not exist: interval / interval

CREATE OR REPLACE FUNCTION div_op(interval, interval) 
RETURNS double precision AS $$
BEGIN
  RETURN EXTRACT(EPOCH FROM $1) / EXTRACT(EPOCH FROM $2);
END;
$$ LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT;

CREATE OPERATOR / (procedure = div_op, leftarg = interval, rightarg = interval);

omega=# select '1hour'::interval / '10min'::interval;
 ?column?
----------
        6

CREATE OR REPLACE FUNCTION op_xor (boolean, boolean) RETURNS boolean AS '
  BEGIN
    RETURN ((NOT $1) AND $2) OR ($1 AND (NOT $2));
  END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION op_imp (boolean, boolean) RETURNS boolean AS '
  BEGIN
    RETURN ($2 OR NOT $1);
  END;
' LANGUAGE 'plpgsql';

DROP OPERATOR # (boolean, boolean);

CREATE OPERATOR # (
  procedure = op_xor,
  leftarg = boolean,
  rightarg = boolean,
  commutator = #
);

DROP OPERATOR >>> (boolean, boolean);

CREATE OPERATOR >>> (
  procedure = op_imp,
  leftarg = boolean,
  rightarg = boolean,
  commutator = >>>
);

DROP TABLE logtab;

CREATE TABLE logtab (l boolean, p boolean);

INSERT INTO logtab VALUES(FALSE,FALSE);
INSERT INTO logtab VALUES(TRUE,FALSE);
INSERT INTO logtab VALUES(FALSE,TRUE);
INSERT INTO logtab VALUES(TRUE,TRUE);

SELECT l, p, l # p AS XOR FROM logtab;
SELECT l, p, l >>> p AS IMPL FROM logtab;

DROP TABLE Hlaseni;
DROP SEQUENCE Hlaseni_id_seq;

CREATE TABLE Hlaseni (
  id SERIAL PRIMARY KEY,
  zalozeno DATE DEFAULT current_date NOT NULL,
  zalozil_neprihlasen VARCHAR(60) CHECK (zalozil_neprihlasen <> ''),
  zalozil INTEGER REFERENCES Uzivatele(id) CHECK (
    zalozil_neprihlasen IS NOT NULL # zalozil IS NOT NULL),
  trida_chyby CHAR(2) NOT NULL REFERENCES TridaChHlaseni(kod),
  chybove_hlaseni TEXT CHECK (
    (trida_chyby IN ('ch','zc') >>> chybove_hlaseni IS NOT NULL) AND
    (chybove_hlaseni <> '')),
  podrobny_popis TEXT NULL CHECK (podrobny_popis <> '')
);

INSERT INTO Hlaseni (zalozil, trida_chyby, podrobny_popis)
  VALUES (1,'po', 'Nainstaloval jsem novou verzi.'); -- ok

INSERT INTO Hlaseni (zalozil, zalozil_neprihlasen, trida_chyby, podrobny_popis)
  VALUES (1, 'Pavel Stěhule','po', 'Nainstaloval jsem novou verzi.'); -- selže

INSERT INTO Hlaseni (zalozil_neprihlasen, trida_chyby, podrobny_popis)
  VALUES ('Pavel Stěhule','po', 'Nainstaloval jsem novou verzi.'); -- ok

INSERT INTO Hlaseni (zalozil_neprihlasen, trida_chyby, podrobny_popis)
  VALUES ('Pavel Stěhule','ch', 'Nainstaloval jsem novou verzi.'); -- selže 

CREATE OR REPLACE FUNCTION comma_aggreg (text, text) RETURNS text AS '
  BEGIN
    IF $2 IS NULL THEN
      RETURN $1;
    END IF
    IF length($1) > 0 THEN
      RETURN $1 || ', ' || $2;
    ELSE
      RETURN $2;
    END IF;
  END;
' LANGUAGE 'plpgsql';

DROP AGGREGATE comma(text);

CREATE AGGREGATE comma (
  basetype = text,
  sfunc = comma_aggreg,
  stype = text,
  initcond = ''
);

SELECT comma(jmeno) FROM jmena;

CREATE OR REPLACE FUNCTION comma_aggreg(text,text) RETURNS text AS '
BEGIN
  IF $2 IS NULL THEN
    RETURN $1;
  END IF;
  IF length($1) > 0 THEN
    IF position($2 in $1) = 0 THEN
      RETURN $1 || '', '' || $2;
    ELSE
      RETURN $1;
    END IF;
  ELSE
    RETURN $2;
  END IF;
END; 
' LANGUAGE plpgsql;

aopk=> select * from pusobnost;
    pobocka    |     okres     | kraj
---------------+---------------+------
 Benešov       | Benešov       | SČ
 Benešov       | Kutná Hora    | SČ
 Č. Budějovice | Č. Budějovice | JČ
(3 řádek)

aopk=> select pobocka, sum(okres), sum(kraj) from pusobnost group by pobocka;
    pobocka    |         sum         | sum
---------------+---------------------+-----
 Č. Budějovice | Č. Budějovice       | JČ
 Benešov       | Benešov, Kutná Hora | SČ
(2 řádek)

CREATE OR REPLACE FUNCTION html_li_aggr (text, text) RETURNS text AS '
  BEGIN
    IF $2 IS NULL THEN
      RETURN $1;
    ELSE
      RETURN $1 || ''<li>'' || $2 || ''</li>\n'';
    END IF;
  END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION html_ul_final (text) RETURNS text AS '
  BEGIN
    IF $1 <> '''' THEN
      RETURN ''<ul>\n'' || $1 || ''</ul>'';
    ELSE
      RETURN '''';
    END IF;
  END;
' LANGUAGE 'plpgsql';

DROP AGGREGATE html_ul (text);

CREATE AGGREGATE html_ul (
  basetype = text,
  sfunc = html_li_aggr,
  stype = text,
  initcond = '',
  finalfunc = html_ul_final
);

<ul>
<li>Stěhule</li>
<li>Rusňák</li>
<li>Chromečka</li>
<li>Bilíček</li>
<li>Bilak</li>
<li>Gottwald</li>
</ul> 

CREATE OR REPLACE FUNCTION First_aggr (text, text) RETURNS text AS '
  BEGIN
    IF $1 <> '''' THEN
      RETURN $1;
    ELSE
      RETURN $2;
    END IF;
  END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION Last_aggr (text, text) RETURNS text AS '
  BEGIN
    RETURN $2;
  END;
' LANGUAGE 'plpgsql';

DROP AGGREGATE First (text);

CREATE AGGREGATE First (
  basetype = text,
  sfunc = First_aggr,
  stype = text,
  initcond = ''
);

DROP AGGREGATE Last (text);

CREATE AGGREGATE Last (
  basetype = text,
  sfunc = Last_aggr,
  stype = text
);

SELECT First(jmeno) FROM jmena; -- -> Stěhule
SELECT Last(jmeno) FROM jmena;  -- -> Gottwald

CREATE TABLE zdroj1 (
  kod CHAR(2) PRIMARY KEY,
  popis VARCHAR(100) CHECK (popis <> '')
);

CREATE TABLE zdroj2 (
  kod CHAR(2) PRIMARY KEY,
  popis VARCHAR(100) CHECK (popis <> '')
);

CREATE TABLE cil (
  kod CHAR(2) PRIMARY KEY,
  popis VARCHAR(100) CHECK (popis <> ''),
  zdroj CHAR(1) NOT NULL CHECK (zdroj = '1' OR zdroj = '2')
);

CREATE OR REPLACE FUNCTION trig_build_cil_F() RETURNS OPAQUE AS '
BEGIN
  IF TG_OP = ''DELETE'' THEN
    DELETE FROM cil WHERE kod = OLD.kod;
    RETURN OLD;
  ELSE
    IF TG_OP = ''UPDATE'' THEN
      UPDATE cil SET kod = NEW.kod, popis = NEW.popis WHERE kod = OLD.kod;
      RETURN NEW;
    ELSE 
      INSERT INTO cil VALUES(NEW.kod, NEW.popis,
        CASE TG_RELNAME WHEN 'zdroj1' THEN 1 WHEN 'zdroj2' THEN 2 END);
      RETURN NEW;
    END IF;
  END IF;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER t_zdroj1 
  BEFORE INSERT OR UPDATE OR DELETE ON zdroj1
  FOR EACH ROW EXECUTE PROCEDURE trig_build_cil_F();

CREATE TRIGGER t_zdroj2
  BEFORE INSERT OR UPDATE OR DELETE ON zdroj2
  FOR EACH ROW EXECUTE PROCEDURE trig_build_cil_F();

DROP SEQUENCE ciselnik_id_seq;
DROP TABLE ciselnik;

CREATE TABLE ciselnik (
  id SERIAL PRIMARY KEY,
  popis VARCHAR(100) CHECK (popis <> '')
);

CREATE OR REPLACE FUNCTION trig_static_id() RETURNS OPAQUE AS '
BEGIN
  IF TG_OP = ''UPDATE'' AND NEW.id <> OLD.id THEN
    RAISE EXCEPTION ''You can not update PRIMARY KEY column on table %'', TG_RELNAME;
  ELSE
    RETURN NEW;  
  END IF;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER static_id_ciselnik
  BEFORE UPDATE ON ciselnik
  FOR EACH ROW EXECUTE PROCEDURE trig_static_id();

DROP SEQUENCE zchu_seq; 
DROP SEQUENCE doc_seq;

CREATE SEQUENCE zchu_seq; -- Vytvoří posloupnost pro sdílený prim. key
CREATE SEQUENCE doc_seq;  -- Vytvoří posloupnost pro dokumenty.

DROP TABLE MCHU;

CREATE TABLE MCHU(
  id INTEGER PRIMARY KEY DEFAULT nextval('zchu_seq'),
  cis INTEGER NULL UNIQUE,
  status CHAR(1)
    DEFAULT 'n' CHECK (status IN ('n','v','z') AND (
      (status = 'n' AND zalozeno IS NOT NULL AND vyhlaseno IS NULL AND zruseno IS NULL) OR
      (status = 'v' AND vyhlaseno IS NOT NULL AND zruseno IS NULL) OR
      (status = 'z' AND zruseno IS NOT NULL AND vyhlaseno IS NOT NULL))),
  zalozeno DATE DEFAULT current_date NULL,
  vyhlaseno DATE NULL CHECK (vyhlaseno >= zalozeno),
  zruseno DATE NULL CHECK (zruseno >= zalozeno AND zruseno >= vyhlaseno),
  rozloha NUMERIC(11,2) NULL CHECK (
    (status = 'v' >>> (CAST(rozloha AS DOUBLE PRECISION) > 0.0 AND rozloha IS NOT NULL))),
  nazev VARCHAR(100) NOT NULL UNIQUE CHECK (nazev <> ''),
  vyska_min INTEGER NULL CHECK ((vyska_min > 150 AND vyska_min <1602) 
    AND (status = 'v' >>> vyska_min IS NOT NULL)),
  vyska_max INTEGER NULL CHECK ((vyska_max > 150 AND vyska_max <1602) 
    AND (status = 'v' >>> vyska_max IS NOT NULL)),
  stav_ochr CHAR(1) NULL REFERENCES StavOchr(kod) 
    CHECK (status = 'v' >>> stav_ochr IS NOT NULL),
  charakter_uzemi VARCHAR(250) NULL,
  lesni_rezervace BOOLEAN NULL CHECK (status = 'v' >>> lesni_rezervace IS NOT NULL)
);

DROP TABLE VCHU;

CREATE TABLE VCHU(
  id INTEGER PRIMARY KEY DEFAULT nextval('zchu_seq'),
  cis INTEGER NULL UNIQUE,
  status CHAR(1)
    DEFAULT 'n' CHECK (status IN ('n','v','z') AND (
      (status = 'n' AND zalozeno IS NOT NULL AND vyhlaseno IS NULL AND zruseno IS NULL) OR
      (status = 'v' AND vyhlaseno IS NOT NULL AND zruseno IS NULL) OR
      (status = 'z' AND zruseno IS NOT NULL AND vyhlaseno IS NOT NULL))),
  zalozeno DATE DEFAULT current_date NULL,
  vyhlaseno DATE NULL CHECK (vyhlaseno >= zalozeno),
  zruseno DATE NULL CHECK (zruseno >= zalozeno AND zruseno >= vyhlaseno),
  rozloha NUMERIC(11,2) NULL CHECK (
    (status = 'v' >>> (CAST(rozloha AS DOUBLE PRECISION) > 0.0 AND rozloha IS NOT NULL))),
  nazev VARCHAR(100) NOT NULL UNIQUE CHECK (nazev <> ''),
  vyska_min INTEGER NULL CHECK ((vyska_min > 150 AND vyska_min <1602) 
    AND (status = 'v' >>> vyska_min IS NOT NULL)),
  vyska_max INTEGER NULL CHECK ((vyska_max > 150 AND vyska_max <1602) 
    AND (status = 'v' >>> vyska_max IS NOT NULL)),
  stav_ochr CHAR(1) NULL REFERENCES StavOchr(kod) 
    CHECK (status = 'v' >>> stav_ochr IS NOT NULL),
  charakter_uzemi VARCHAR(250) NULL,
  lesni_rezervace BOOLEAN NULL CHECK (status = 'v' >>> lesni_rezervace IS NOT NULL)
);

DROP TABLE PS;

CREATE TABLE PS (
  id INTEGER PRIMARY KEY DEFAULT nextval('zchu_seq'),
  cis INTEGER NULL UNIQUE,
  puv_data BOOLEAN NOT NULL DEFAULT false CHECK (puv_data OR zalozeno IS NOT NULL),
  status CHAR(1)
    DEFAULT 'n' CHECK (status IN ('n','v','z') AND (
      (status = 'n' AND zalozeno IS NOT NULL AND vyhlaseno IS NULL AND zruseno IS NULL) OR
      (status = 'v' AND vyhlaseno IS NOT NULL AND zruseno IS NULL) OR
      (status = 'z' AND zruseno IS NOT NULL AND vyhlaseno IS NOT NULL))),
  zalozeno DATE DEFAULT current_date NULL,
  vyhlaseno DATE NULL CHECK (vyhlaseno >= zalozeno),
  zruseno DATE NULL CHECK (zruseno >= zalozeno AND zruseno >= vyhlaseno)
);

DROP TABLE ZCHU_RezervacniKnihy;

CREATE TABLE ZCHU_RezervacniKnihy (
  id INTEGER PRIMARY KEY DEFAULT nextval('doc_seq'), -- teoreticky by bylo možné wf i na dokumentech v RK
  idoo INTEGER NOT NULL,  -- id objektu ochrany
  taboo CHAR(1) NOT NULL CHECK(taboo IN ('m','v','s')), -- bez parků
  isign varchar(50) NOT NULL UNIQUE CHECK (isign <> ''),
  typ CHAR(2) NOT NULL REFERENCES TypDocRK(kod),
  autor_prijmeni VARCHAR(30) NOT NULL CHECK (autor_prijmeni <> ''), 
  autor_jmeno VARCHAR(30) NULL CHECK (autor_jmeno <> ''),
  nazev VARCHAR(100) NOT NULL CHECK (nazev <> ''),
  poznamka VARCHAR(300) NULL,
  rok_vydani INTEGER NULL CHECK (rok_vydani > 1918 AND rok_vydani <EXTRACT(year FROM current_date)),
  zalozeno DATE DEFAULT current_date NOT NULL,
  vyrazeno DATE NULL CHECK (vyrazeno > zalozeno),
  isbn CHAR(12) NULL CHECK (check_ISBN(isbn)),
  zalozil INTEGER NOT NULL REFERENCES AOPKUzivatele(id),
  vyradil INTEGER NULL REFERENCES AOPKUzivatele(id)
    CHECK(vyradil IS NOT NULL AND vyrazeno IS NOT NULL),
  url VARCHAR(100) NULL CHECK (url ~* '^(file|http|ftp)://.+')
); 

CREATE OR REPLACE FUNCTION trig_refintegr_zchu_P() RETURNS OPAQUE AS '
DECLARE ktab CHAR(1);
BEGIN
  IF TG_OP = ''UPDATE'' THEN
    IF NEW.id <> OLD.id THEN
      RAISE EXCEPTION ''referential integrity violation - You can not update PRIMARY KEY column on table %'', 
        TG_RELNAME;
    END IF;
    RETURN NEW;
  END IF;

  ktab := CASE TG_RELNAME 
    WHEN ''mchu'' THEN ''m'' 
    WHEN ''vchu'' THEN ''v''
    WHEN ''ps''   THEN ''s'' 
  END;  

  IF EXISTS(SELECT id FROM ZCHU_RezervacniKnihy WHERE idoo = OLD.id
    AND tanoo = ktab) THEN
    RAISE EXCEPTION ''referential integrity violation - key in % still referenced from ZCHU_rezervacniKnihy'', 
      TG_RELNAME;
  END IF;
  RETURN OLD;
END;
' LANGUAGE 'plpgsql'

CREATE TRIGGER refintegr_zchu_p_mchu
  BEFORE DELETE OR UPDATE ON MCHU
  FOR EACH ROW EXECUTE PROCEDURE trig_refintegr_zchu_P();

CREATE TRIGGER refintegr_zchu_p_vchu
  BEFORE DELETE OR UPDATE ON VCHU
  FOR EACH ROW EXECUTE PROCEDURE trig_refintegr_zchu_P();

CREATE TRIGGER refintegr_zchu_p_ps
  BEFORE DELETE OR UPDATE ON PS
  FOR EACH ROW EXECUTE PROCEDURE trig_refintegr_zchu_P(); 

CREATE OR REPLACE FUNCTION trig_refintegr_zchu_F() RETURNS OPAQUE AS '
DECLARE r RECORD; tab VARCHAR(20);
BEGIN
  IF TG_OP = ''UPDATE'' THEN
    IF NEW.id <> OLD.id THEN
      RAISE EXCEPTION ''referential integrity violation - You can not update PRIMARY KEY column on table %'', TG_RELNAME;
    END IF;
  END IF;
  IF NEW.taboo IS NULL THEN
    RETURN NEW;  -- nelze pokračovat
  END IF;
  IF NOT NEW.taboo IN (''m'',''v'',''s'') THEN
    RETURN NEW;
  END IF;
  SELECT INTO r CASE NEW.taboo
    WHEN ''m'' THEN (SELECT id FROM MCHU WHERE id=NEW.idoo)
    WHEN ''v'' THEN (SELECT id FROM VCHU WHERE id=NEW.idoo)
    WHEN ''s'' THEN (SELECT id FROM PS WHERE id=NEW.idoo)
  END AS id;
  IF r.id IS NULL THEN
    tab := CASE NEW.taboo
      WHEN ''m'' THEN ''MCHU''
      WHEN ''v'' THEN ''VCHU''
      WHEN ''s'' THEN ''PS''
    END;   
    RAISE EXCEPTION ''referential integrity violation - key referenced from % not found in %'', TG_RELNAME, tab;
  END IF;
  RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER refintegr_zchu_f_ZCHU_RK
  BEFORE INSERT OR UPDATE ON ZCHU_rezervacniKnihy
  FOR EACH ROW EXECUTE PROCEDURE trig_refintegr_zchu_f();

CREATE TABLE foo_a(i integer);
CREATE TABLE foo_b(i integer);

CREATE OR REPLACE FUNCTION trig() RETURNS trigger AS '
BEGIN
  RAISE NOTICE ''Trigger: %,Insert do tabulky: %,Parametr: %'', 
    TG_NAME, TG_RELNAME, TG_ARGV[0];
  RETURN NULL;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER foo_a_trg AFTER INSERT ON foo_a
  FOR EACH ROW EXECUTE PROCEDURE trig('a');
CREATE TRIGGER foo_b_trg AFTER INSERT ON foo_b
  FOR EACH ROW EXECUTE PROCEDURE trig('b');

INSERT INTO foo_a VALUES(1);
INSERT INTO foo_b VALUES(1);

psql:testtr.sql:16: NOTICE:  Trigger: foo_a_trg,Insert do tabulky: foo_a,Parametr: a
INSERT 336812 1
psql:testtr.sql:17: NOTICE:  Trigger: foo_b_trg,Insert do tabulky: foo_b,Parametr: b
INSERT 336813 1

CREATE TABLE prodejci (
  id SERIAL PRIMARY KEY,
  rc VARCHAR(10) NOT NULL UNIQUE CHECK (rc ~ '^[0-9]{9,10}$'),
  jmeno VARCHAR(20) NOT NULL CHECK (jmeno <> ''),
  prijmeni VARCHAR(20) NOT NULL CHECK (prijmeni <> '')
);

CREATE TABLE prodej (
  id SERIAL PRIMARY KEY,
  prodejce int REFERENCES prodejci(id),
  closing_date DATE NOT NULL DEFAULT current_date::DATE,
  c1   integer NULL CHECK(c1 > 0),
  c2   integer NULL CHECK(c2 > 0),
  c3   integer NULL CHECK(c3 > 0),
  t2   integer NULL CHECK(t2 > 0),
  ot   integer NULL CHECK(ot > 0),
  oec1 integer NULL CHECK(oec1 > 0),
  oec2 integer NULL CHECK(oec2 > 0),
  oec3 integer NULL CHECK(oec3 > 0),
  t1   integer NULL CHECK(t1 > 0),
  b1   integer NULL CHECK(b1 > 0),
  b2   integer NULL CHECK(b2 > 0),
  c050 integer NULL CHECK(c050 > 0),
  c150 integer NULL CHECK(c150 > 0),
  c300 integer NULL CHECK(c300 > 0),
  t1n  integer NULL CHECK(t1n > 0),
  t2n  integer NULL CHECK(t2n > 0)
);

0 - 4, 5 - 9, 10 - 19, 20 - 49, nad 50

CREATE OR REPLACE FUNCTION isnz(integer) RETURNS integer AS '
  BEGIN
    IF $1 IS NOT NULL THEN
      RETURN $1; 
    ELSE 
      RETURN 0;
    END IF;
  END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION c_prodej_seg (prodej, int, int) RETURNS integer AS '
  DECLARE s INTEGER;
  BEGIN 
    s := isnz($1.c1) + isnz($1.c2) + isnz($1.c3) + isnz($1.t2)
      + isnz($1.oec1) + isnz($1.oec2) + isnz($1.oec3) + isnz($1.t1)
      + isnz($1.b1) + isnz($1.b2) + isnz($1.c150) + isnz($1.c300)
      + isnz($1.t1n) + isnz($1.t2n) + isnz($1.ot);
    IF $2 IS NOT NULL THEN
      IF s <$2 THEN RETURN 0; END IF;
    END IF;
    IF $3 IS NOT NULL THEN
      IF s > $3 THEN RETURN 0; END IF;
    END IF;
    RETURN s;
  END;
' LANGUAGE 'plpgsql'; 

INSERT INTO prodejci (rc, jmeno, prijmeni) 
  VALUES ('7307150000','Pavel', 'Stěhule');
INSERT INTO prodejci (rc, jmeno, prijmeni) 
  VALUES ('7807150000','Zdeněk', 'Stěhule');

INSERT INTO prodej (prodejce,c1,c2) VALUES (1,18,11);
INSERT INTO prodej (prodejce,c1,c2) VALUES (1,18,6);
INSERT INTO prodej (prodejce,c3,c2) VALUES (1,50,24);
INSERT INTO prodej (prodejce,t1,c3) VALUES (1,1,1);
INSERT INTO prodej (prodejce,c300)  VALUES (2,10);
INSERT INTO prodej (prodejce,c1,c2) VALUES (2,11,2);
INSERT INTO prodej (prodejce,c1,c2) VALUES (2,1,6);
INSERT INTO prodej (prodejce,c3,c2) VALUES (2,5,12);
INSERT INTO prodej (prodejce,t1,ot) VALUES (2,1,1);
INSERT INTO prodej (prodejce,c300)  VALUES (2,252);

SELECT 
  SUM(c_prodej_seg(prodej,NULL, 4)) AS lt5,
  SUM(c_prodej_seg(prodej,5, 9)) AS be5a9,
  SUM(c_prodej_seg(prodej,10, 19)) AS be10a19,
  SUM(c_prodej_seg(prodej,20, 49)) AS be20a49,
  SUM(c_prodej_seg(prodej,50, NULL)) AS ge50,
  jmeno || ' ' || prijmeni as prodejce  
from 
  prodej join prodejci on prodejce = prodejci.id 
group 
  by jmeno || ' ' || prijmeni ;

 lt5 | be5a9 | be10a19 | be20a49 | ge50 |    prodejce
-----+-------+---------+---------+------+----------------
   2 |     0 |       0 |      53 |   74 | Pavel Stěhule
   2 |     7 |      40 |       0 |  252 | Zdeněk Stěhule

SELECT 
  SUM(
    CASE WHEN (isnz(c1) + isnz(c2) + isnz(c3)) BETWEEN 0 AND 4 
      THEN (isnz(c1) + isnz(c2) + isnz(c3)) ELSE 0 END) AS le4, 
  SUM(
    CASE WHEN (isnz(c1) + isnz(c2) + isnz(c3)) BETWEEN 5 AND 9 
      THEN (isnz(c1) + isnz(c2) + isnz(c3)) ELSE 0 END) AS be5a9, 
  SUM(
    CASE WHEN (isnz(c1) + isnz(c2) + isnz(c3)) BETWEEN 10 AND 19 
      THEN (isnz(c1) + isnz(c2) + isnz(c3)) ELSE 0 END) AS be10a19, 
  SUM(
    CASE WHEN (isnz(c1) + isnz(c2) + isnz(c3)) BETWEEN 20 AND 49 
      THEN (isnz(c1) + isnz(c2) + isnz(c3)) ELSE 0 END) AS be20a49, 
  SUM(
    CASE WHEN (isnz(c1) + isnz(c2) + isnz(c3)) >= 50 
      THEN (isnz(c1) + isnz(c2) + isnz(c3)) ELSE 0 END) AS ge50,
  jmeno || ' ' || prijmeni as prodejce  
from 
  prodej join prodejci on prodejce = prodejci.id 
group 
  by jmeno || ' ' || prijmeni ;

CREATE TYPE tf AS (f1 varchar(10), f2 varchar(10));

CREATE OR REPLACE FUNCTION makesettf(integer) RETURNS SETOF tf AS '
  DECLARE f tf%ROWTYPE;
  BEGIN
    FOR i IN 1..$1 LOOP
      f.f1 := CAST(i AS varchar(10));
      f.f2 := ''bbbbb ''||CAST(i AS varchar(10));
      RAISE NOTICE ''%'', f.f1;
      RETURN NEXT f;
    END LOOP;
    RETURN;
  END;
' LANGUAGE 'plpgsql';

SELECT a.*, b.* FROM 
  makesettf(10) a JOIN makesettf(5) b ON a.f1 = b.f1;

SELECT * FROM makesettf(3) UNION ALL SELECT * FROM makesettf(8);

CREATE OR REPLACE FUNCTION DaysInYear(integer) RETURNS SETOF date AS '
DECLARE st date;
BEGIN st := to_date(''0101''||$1,''MMDDYYYY'');
  WHILE EXTRACT(year FROM st) < $1 + 1 LOOP
    RETURN NEXT st;
    st := st + 1;
  END LOOP;
  RETURN;
END' 
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION Seq(integer) RETURNS SETOF integer AS '
DECLARE i integer;
BEGIN i := 1;
  WHILE i <= $1 LOOP
    RETURN NEXT i;
    i := i + 1;
  END LOOP;
  RETURN;
END' LANGUAGE plpgsql;

-- testy
SELECT DaysInYear, Seq FROM DaysInYear(2003) CROSS JOIN Seq(40) 
  WHERE AddWDays(DaysInYear, Seq) <> AddWDays2(DaysInYear, Seq);
SELECT DaysInYear, Seq FROM DaysInYear(2003) CROSS JOIN Seq(40) 
  WHERE DiffWDays(DaysInYear, AddWDays(DaysInYear, Seq)) <> Seq;

CREATE TYPE StatniSvatek AS (termin date, nazev varchar(50));

CREATE OR REPLACE FUNCTION StatniSvatky(date, date) RETURNS SETOF StatniSvatek  AS '
DECLARE 
  svatek VARCHAR [][] DEFAULT ARRAY
   [[''Nový rok'', ''0101''], 
    [''Neděle velikonoční'', ''xxxx''],
    [''Pondělí velikonoční'',''xxx1''], 
    [''Svátek práce'',''0501''],
    [''Den osvobození'',''0508''], 
    [''Den slovanských věrozvěstů Cyrila a Metoděje'',''0705''],
    [''Den upálení mistra Jana Husa'',''0706''], 
    [''Den české státnosti'',''0928''],
    [''Den vzniku samostatného československého státu'', ''1028''],
    [''Den boje za svobodu a demokracii'',''1117''],
    [''Štědrý den'',''1224''],
    [''1. svátek vánoční'',''1225''],
    [''2. svátek vánoční'',''1226'']];
  rok integer; stepd date; d varchar;
  sv StatniSvatek%ROWTYPE;
BEGIN stepd := $1;
  WHILE stepd <= $2 LOOP
    rok := CAST(EXTRACT(year FROM stepd) AS INTEGER);
    FOR i IN array_lower(svatek,1)..array_upper(svatek,1) LOOP
      d := svatek[i][2];
      IF d = ''xxxx'' THEN
        sv.termin := velnedele(rok);
      ELSIF d = ''xxx1'' THEN
        sv.termin := sv.termin +1;
      ELSE
        sv.termin := to_date(d||rok,''MMDDYYYY'');
      END IF;
      IF sv.termin BETWEEN stepd AND $2 THEN
        sv.nazev := svatek[i][1]; RETURN NEXT sv;
      END IF;
    END LOOP;
    stepd := date_trunc(''year'', stepd) + interval ''1 year'';
  END LOOP;
  RETURN;
END' LANGUAGE plpgsql STRICT;

CREATE OR REPLACE FUNCTION PocetStatnichSvatku(date, date, INTEGER[]) RETURNS INTEGER AS '
DECLARE 
  svatek VARCHAR [] DEFAULT ARRAY
   [''0101'', ''xxxx'', ''xxx1'', ''0501'', ''0508'', ''0705'', ''0706'', 
    ''0928'', ''1028'', ''1117'', ''1224'', ''1225'', ''1226''];
  rok INTEGER; stepd date; d varchar; sv date; pss INTEGER DEFAULT 0;  OM INTEGER;
BEGIN stepd := $1;
  WHILE stepd <= $2 LOOP
    rok := CAST(EXTRACT(year FROM stepd) AS INTEGER);
    FOR i IN array_lower(svatek,1)..array_upper(svatek,1) LOOP
      d := svatek[i];
      IF d = ''xxxx'' THEN
        sv := velnedele(rok);
      ELSIF d = ''xxx1'' THEN
        sv := sv +1;
      ELSE
        sv := to_date(d||rok,''MMDDYYYY'');
      END IF;
      IF NOT EXTRACT(dow FROM sv) = ANY ($3) THEN
        IF sv BETWEEN stepd AND $2 THEN
          pss := pss + 1;
        END IF;
      END IF;
    END LOOP;
    stepd := date_trunc(''year'', stepd) + interval ''1 year'';
  END LOOP;
  RETURN pss;
END' LANGUAGE plpgsql STRICT;

CREATE OR REPLACE FUNCTION AddWDaysSlow2(date, integer) RETURNS date AS '
DECLARE st date; ed date; svatky date[]; d integer; k integer;
BEGIN st := $1; d := $2; ed := st + 365;
  SELECT INTO svatky ARRAY(SELECT termin FROM StatniSvatky(st, ed));
  LOOP k := CAST(EXTRACT(DOW FROM st) AS INTEGER);
    WHILE k = 6 OR k = 0 OR st = ANY(svatky) LOOP
      st := st + 1; k := CAST(EXTRACT(DOW FROM st) AS INTEGER);
      IF st > ed THEN ed = st + 365; 
        SELECT INTO svatky ARRAY(SELECT termin FROM StatniSvatky(st, ed));
      END IF;
    END LOOP;
    EXIT WHEN d = 0 OR d = 1;
    st := st + 1; d := d - 1;
  END LOOP;
  RETURN st;
END; 
' LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION AddWDays(date, integer) RETURNS date AS '
DECLARE st date; ed DATE; korekce INTEGER; sv INTEGER; d INTEGER;
BEGIN st := $1; ed := AddWDay(st,$2);
  korekce = PocetStatnichSvatku(st, ed, ARRAY[6,0]); 
  WHILE korekce > 0 LOOP
    st := ed + 1; ed := AddWDay(st, korekce); 
    korekce =  PocetStatnichSvatku(st, ed, ARRAY[6,0]);
  END LOOP;
  RETURN FirstWDay(ed);
END' LANGUAGE plpgsql STRICT;

CREATE OR REPLACE FUNCTION AddWDay(date, integer) RETURNS date AS '
DECLARE st DATE; wd INTEGER; k INTEGER;
BEGIN k := CAST(EXTRACT(DOW FROM $1) AS INTEGER);
  IF k = 0 THEN st := $1 + 1; k := 1;
  ELSIF k = 6 THEN st := $1 + 2; k := 1;
  ELSE st := $1; END IF;
  st := st - k; wd := $2 + k - 2;
  st := st + ((wd) / 5) * 7  + ((wd) % 5) + 1;
  RETURN st;
END' LANGUAGE plpgsql STRICT;

CREATE OR REPLACE FUNCTION ISODOW(date) RETURNS INTEGER AS '
  SELECT CAST(EXTRACT(DOW FROM $1 - 1) + 1 AS INTEGER);
' LANGUAGE sql STRICT;

CREATE OR REPLACE FUNCTION DiffWDays(date, date) RETURNS integer AS '
DECLARE wc INTEGER; st DATE; ED DATE; p INTEGER;
BEGIN 
  p := ISODOW($1); IF p > 5 THEN st := $1 + (8 - p); ELSE st := $1; END IF;
  p := ISODOW($2); IF p > 5 THEN ed := $2 - (p - 5); ELSE ed := $2; END IF;
  wc := ($2 - $1) / 7;
  IF EXTRACT(DOW FROM st - 1) + 1 > EXTRACT(DOW FROM ed - 1) + 1 THEN
    wc := wc + 1;
  END IF;
  RETURN ($2 - $1 - (wc * 2) - PocetStatnichSvatku(st,ed, ARRAY[6,7]) + 1);
END' LANGUAGE plpgsql STRICT;

CREATE OR REPLACE FUNCTION foo (integer) RETURNS SETOF foo AS '
DECLARE pid ALIAS FOR $1; rec RECORD;
BEGIN
  FOR rec IN SELECT * FROM foo WHERE foo.pid=pid LOOP
    RETURN NEXT rec;
    RAISE NOTICE ''uid=% pid=%'',rec.uid,rec.pid;
    SELECT INTO rec * FROM foo (rec.uid);
  END LOOP;
  RETURN;
END; 
' LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION foor (integer) RETURNS SETOF foo AS '
DECLARE pid ALIAS FOR $1; rec RECORD;
DECLARE fo bool;
BEGIN
  FOR rec IN SELECT * FROM foo WHERE foo.pid=pid LOOP
    RETURN NEXT rec;
    FOR rec IN SELECT * FROM foor (rec.uid) LOOP
      RETURN NEXT rec;
    END LOOP;
  END LOOP;
  RETURN;
END;
' LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION polyfoo1(anyelement) RETURNS anyelement AS '
BEGIN
  RETURN  $1 + 1;
END;
' LANGUAGE plpgsql;

testdb011=> select polyfoo1(date '1.1.2004'), polyfoo1(10);
  polyfoo1  | polyfoo1
------------+----------
 2004-01-02 |       11
(1 řádka)


CREATE OR REPLACE FUNCTION polyfoo2(anyelement, anyelement) RETURNS anyelement AS '
BEGIN
  RETURN  $1 - $2;
END;
' LANGUAGE plpgsql;

testdb011=> select polyfoo2(date '1.1.2004',date '1.2.2003');
 polyfoo2
----------
        0
(1 řádka)

testdb011=> select polyfoo2(date '1.1.2004',date '1.2.2003');
  polyfoo2
------------
 2003-12-31
(1 řádka)

CREATE OR REPLACE FUNCTION aggregate_array(ANYARRAY,ANYELEMENT) RETURNS ANYARRAY AS '
   SELECT CASE
     WHEN $1 IS NULL THEN ARRAY[$2]
     WHEN $2 IS NULL THEN $1
     ELSE array_append($1,$2)
 END;
' LANGUAGE 'SQL';

testdb011=> SELECT count(*), aggarray(prijmeni) FROM lide GROUP BY prijmeni ~ '.*á';

 count |                  aggarray
-------+---------------------------------------------
     1 | {Gregorová}
     6 | {Kolík,Čurda,Hojka,Chytlík,Stěhule,Novotný}
(2 řádek)

CREATE OR REPLACE FUNCTION foo1(IN par1 integer) RETURNS bool AS $$
DECLARE
  x ALIAS FOR $1;
  y integer = par1;
BEGIN
  RAISE NOTICE '% % %', par1, x, y;
  -- par1 := 11; ERROR: "$1" is declared CONSTANT
  -- x := 10;  ERROR: "$1" is declared CONSTANT
  y := 10;
  RETURN 't';
END; $$ LANGUAGE plpgsql;

DROP TYPE footype CASCADE;
CREATE TYPE footype AS (x integer, y integer, z varchar);

CREATE OR REPLACE FUNCTION foo2(footype) RETURNS bool AS $$
DECLARE 
  x integer = $1.x;
  y footype;
BEGIN
  RAISE NOTICE '% %', x, $1.y;
  y := $1; y := (10, 10, 'Pavel');
  RETURN 't';
END; $$ LANGUAGE plpgsql;

SELECT foo2((10, 10, 'Pavel'));

CREATE OR REPLACE FUNCTION foo3() RETURNS footype AS $$
DECLARE
  x footype;
BEGIN
  x := row(10, 12, 'Jirka');
  x := (10, 12, 'Jirka');
  RETURN x;
END; $$ LANGUAGE plpgsql;

SELECT foo3(); -- NELZE
SELECT (foo3()).z;

DROP TABLE testxx CASCADE;
CREATE TABLE testxx(x integer, y integer);

CREATE OR REPLACE FUNCTION trig01(anyelement) RETURNS integer AS $$
BEGIN RAISE NOTICE 'trig01: %', $1.x;
END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION trig02(testxx) RETURNS integer AS $$
BEGIN RAISE NOTICE 'trig02: %', $1.y;
END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION footrig() RETURNS TRIGGER AS $$
DECLARE
  s1 integer; s2 integer;
BEGIN
  s1 := trig01(NEW); s2 := trig02(NEW);
  RAISE NOTICE '% %', s1, s2;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER xxx BEFORE INSERT ON testxx 
  FOR EACH ROW EXECUTE PROCEDURE footrig();

CREATE OR REPLACE FUNCTION foo(integer) RETURNS integer AS $$
BEGIN
  INSERT INTO footab VALUES($1);
  RETURN 0;
EXCEPTION WHEN OTHERS THEN
  RETURN -1;
END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION foo(OUT x integer, OUT y integer, INOUT z integer) AS $$
BEGIN
  x := z + 1; y := z + 2;
END; $$ LANGUAGE plpgsql;
SELECT foo(10);

CREATE OR REPLACE FUNCTION foo(OUT x integer, OUT y integer, IN z integer) 
RETURNS SETOF RECORD AS $$
BEGIN
  FOR _i IN 1 .. z LOOP
    x := _i; y := _i + 1;
    RETURN NEXT;
  END LOOP;
END; $$ LANGUAGE plpgsql;
SELECT * FROM foo5(2);
 x | y
---+---
 1 | 2
 2 | 3
(2 rows)

CREATE OR REPLACE FUNCTION foox() RETURNS void AS $$
DECLARE _r RECORD;
BEGIN
  FOR _r IN EXPLAIN SELECT * FROM footab LOOP
    RAISE NOTICE '%', _r."QUERY PLAN";
  END LOOP;
END; $$ LANGUAGE plpgsql;
SELECT foox();
NOTICE:  Seq Scan on footab  (cost=0.00..31.40 rows=2140 width=4)
 foox
------

(1 row)

CREATE OR REPLACE FUNCTION generate_string(integer) RETURNS SETOF varchar AS $$
BEGIN
  FOR _i IN 1 .. $1 LOOP 
    RETURN NEXT '<item>'||_i||'</item>';
  END LOOP;
  RETURN;
END; $$ LANGUAGE plpgsql;

SELECT array_to_string(
  ARRAY(SELECT * FROM generate_string(1000)), '');