From: | googlemike(at)hotpop(dot)com (Google Mike) |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | FAQ: Having Trouble Getting PL/pgSQL Going? |
Date: | 2004-09-07 00:11:40 |
Message-ID: | 25d8d6a8.0409061611.6246728@posting.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Randy Yates | 2004-09-07 03:57:25 | Datatypes Documentation |
Previous Message | NTPT | 2004-09-06 22:23:56 | Re: Index on TEXT versus CHAR(32)... fast exact TEXT matching |