I had RH9 Linux. It came with pgSQL, but I couldn't seem to figure out
how to get PL/pgSQL going. I read the HTML documentation that came
with it and was confused until I tried a few different variations and
guessed about some things. I've finally got it working and here's what
the SQL looks like to enable and test it:
-- ON YOUR SYSTEM, LOOK FOR WHERE plpgsql.so FILE EXISTS AND
SUBSTITUTE THAT
-- PATH WITH THE ONE I HAVE BELOW.
-- ONLY NEED TO RUN THESE THINGS ONCE ON A POSTGRESQL DB
CREATE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS
'/usr/lib/pgsql/plpgsql.so' LANGUAGE C;
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER
plpgsql_call_handler;
-- END OF PL/pgSQL INSTALLATION
-- TEST:
DROP FUNCTION test();
CREATE FUNCTION test() RETURNS SETOF INTEGER AS '
BEGIN
FOR i IN REVERSE 10..1 LOOP
RETURN NEXT i;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
SELECT * FROM test();
For more help on writing these stored functions, look at this on your
Linux hard drive:
cd /usr/share/doc/postgresql*
cd html
mozilla plpgsql.html
To dump them out so you can see which ones you've stored, do this at
command line, replacing the <options>:
pg_dump -h "<host>" -p 5432 -U "<username>" -s -C "<database>" | grep
-i "CREATE" -A 500000 | grep -v "\-\-" | grep -v "\\connect" | grep -v
"SET " | tr -s "\n"
Happy stored procedure (er, uh, function) programming on PostgreSQL!
