I want to be able to SELECT the code of a Function. When I try this Query:
select prosrc from pg_proc where proname = 'my_proc'
I get an empty column.
The thing is, it seems column prosrc does hold the function text. When I try this Query:
select proname from pg_proc where prosrc ~* 'part of Function text'
I get the correct number and names of Functions. It just doesn't display prosrc. Any idea?
PostgreSQL 8.2. pgAdmin III 1.12.2.
Thanks.
I ran into this as well using pgAdmin III on PostgreSQL 9.0.3. Looks to me like this is related to the string being too long for pgAdmin III to display properly. If you execute it on the command prompt then you should be alright. A lot of database management tools run into string truncation problems.
Interestingly, if you select the prosrc cell in pgAdmin III and copy it with the keyboard, you can paste the output. This is probably some kind of strange display bug.
It's because the value of prosrc often starts with a linefeed. Usually when creating a function you start a new line after the $$ (or $whatever$) quoting the function text:
CREATE FUNCTION myfunction() RETURNS integer AS $$
SELECT 1
$$ LANGUAGE sql;
If you defined that as:
CREATE FUNCTION myfunction() RETURNS integer AS $$SELECT 1$$ LANGUAGE sql;
There would be no linefeed in the function text.
You can chop off leading linefeeds with the ltrim function:
SELECT ltrim(prosrc, E'\x0a') FROM pg_proc WHERE proname = 'myfunction';
Alternatively, if you vertically resize the row in PGAdmin, you can see the full value.
In the later versions you can do it by right clicking and Scripts -> Create Script
File > Options > Query Tool > Query editor > Max. characters per column
Or just view it in the Object browser.
