FAQ: Having Trouble Getting PL/pgSQL Going?

[Google Mike]

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!