Insert timestamps with a loop

This sql command allow you to test data writting in your database. It can be useful to benchmark your disk performances or check the impact of a lock.


INSERT INTO table SELECT timeofday()::timestamp FROM generate_series(1,1000000) WHERE pg_sleep(1) IS NOT NULL;


Grant rights on many tables

-- pgx_grant(PRIVILEGE, TABLES, USER)
--  Grants PRIVILEGE to USER on objects like TABLES%
--  Grants to tables, views and sequences.
--
CREATE FUNCTION pgx_grant(text,text,text) returns int4 as '
DECLARE
  priv ALIAS FOR $1;
  patt ALIAS FOR $2;
  user ALIAS FOR $3;
  obj  record;
  num  integer;
BEGIN
  num:=0;
  FOR obj IN SELECT relname FROM pg_class
  WHERE relname LIKE patt || \'%\' AND relkind in (\'r\',\'v\',\'S\') LOOP
    EXECUTE \'GRANT \' || priv || \' ON \' || obj.relname || \' TO \' || user;
    num := num + 1;
  END LOOP;
  RETURN num;
END;
' language 'plpgsql';

My first plpgsql function

At first you need to declare language :

$ createlang plpgsql database

Then connect on postgres :

database=# CREATE FUNCTION a_function () RETURNS int4 AS '
database'# DECLARE
database'# an_integer int4;
database'# BEGIN
database'#    an_integer := 10 * 10;
database'#    return an_integer;
database'# END;
database'# ' LANGUAGE 'plpgsql';

Your first function is now created. You can use it like this :

SELECT a_function() AS output;

Here an exemple of a function :

CREATE OR REPLACE FUNCTION b_function() RETURNS SETOF TEXT AS '
DECLARE
    element_id int4;
    elmt_id0 int4;
    elmt_id1 int4;
    element record;
    output text;
    cur_element CURSOR for SELECT * FROM element;
BEGIN
    OPEN cur_element;
    LOOP
        FETCH cur_element INTO element_id, elmt_id0, elmt_id1;
        IF NOT FOUND THEN
            EXIT;
        END IF;
        DELETE FROM alpha WHERE element_id=element_id;
        DELETE FROM country WHERE element_id=element_id;
        FOR element in SELECT * FROM element WHERE elmt_id=elmt_id0 OR elmt_id=elmt_id1 LOOP
            output := element.element_home_dir || element.element_uri;
            return next output;
        END LOOP;
    END LOOP;
    CLOSE cur_element;
    RETURN;
END;
' LANGUAGE 'plpgsql';
SELECT * from b_function() GROUP BY b_function;

Export / Import PostgreSQL database

Export a database

su - postgres
pg_dump <database_name> > database_name.pgdump

Import a database

cat database_name.pgdump | psql -d database_name


For all database

pg_dumpall -o > db_all.out
psql -e template1 < db_all.out