CODEWITHSUNDEEP
postgresqlpgadmin
bentaly
bentaly

Reputation: 81

PostgreSQL - How to see Function Text/Source in pgAdmin?

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.

Upvotes: 8

Views: 11893

Answers (4)

aspdeepak
aspdeepak

Reputation: 2781

In the later versions you can do it by right clicking and Scripts -> Create Script

Create Script

Upvotes: 2

TCs
TCs

Reputation: 1

File > Options > Query Tool > Query editor > Max. characters per column

Or just view it in the Object browser.

Upvotes: 0

hbn
hbn

Reputation: 1946

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.

Upvotes: 2

anon
anon

Reputation:

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.

Upvotes: 2

Related Questions