OSSP CVS Repository

ossp-pkg/as/as-db/bres/bres-cfg.sql

---

DROP TABLE as_config CASCADE;
CREATE TABLE as_config (
    cf_var   TEXT,
    cf_val   TEXT
);
DROP TABLE as_config_session CASCADE;
CREATE TABLE as_config_session (
    cf_var   TEXT,
    cf_val   TEXT
);

CREATE OR REPLACE FUNCTION as_session_start ()
RETURNS VOID LANGUAGE 'plpgsql' STABLE AS '
BEGIN
    CREATE LOCAL TEMPORARY TABLE as_config
        AS SELECT * FROM as_config;
    RETURN;
END;
';
CREATE OR REPLACE FUNCTION as_session_end ()
RETURNS VOID LANGUAGE 'sql' STABLE AS '
    DROP TABLE as_config;
';
CREATE OR REPLACE FUNCTION as_session_var (TEXT)
RETURNS TEXT LANGUAGE 'sql' STABLE AS '
    SELECT cf_val FROM as_config WHERE cf_var = $1;
';
CREATE OR REPLACE FUNCTION as_session_var (TEXT, TEXT)
RETURNS TEXT LANGUAGE 'plpgsql' STABLE AS '
DECLARE
    old TEXT;
BEGIN
    old := (SELECT cf_val FROM as_config WHERE cf_var = $1);
    UPDATE as_config SET cf_val = $2 WHERE cf_var = $1;
    return old;
END;
';

CREATE OR REPLACE FUNCTION as_config_val (TEXT)
RETURNS TEXT LANGUAGE 'plpgsql' STABLE AS '
DECLARE
    var ALIAS FOR $1; 
    val TEXT;
BEGIN
    val := NULL;
    IF (SELECT tablename FROM pg_tables
        WHERE tablename = ''as_config_session'') IS NOT NULL THEN
        val := (SELECT cf_val FROM as_config_session WHERE cf_var = var);
    END IF;
    IF val IS NULL THEN
        val := (SELECT cf_val FROM as_config WHERE cf_var = var);
    END IF;
    RETURN val;
END;
';

INSERT INTO as_config VALUES ('foo', '1');
INSERT INTO as_config VALUES ('bar',  '2');
-- INSERT INTO as_config_session VALUES ('quux', '3');
-- INSERT INTO as_config_session VALUES ('bar', '4');

-- SELECT * FROM as_config;
-- SELECT * FROM as_config_session;

SELECT as_session_start();
SELECT as_session_var('non-existing');
SELECT as_session_var('foo');
SELECT as_session_var('bar');
SELECT as_session_var('quux');
SELECT as_session_end();

SELECT cf_var,as_session_var(cf_var) FROM as_config UNION (SELECT cf_var,cf_val FROM as_config_session);

---