Thread: Show stored function code

Hello,
I'm trying to execute search in my stored functions code, but I have some
difficulties. I'm using postgres 9.1.3, and if I execeute this query

SELECT prosrc FROM pg_proc WHERE proname = <function name>;

I retrieve an empty column for all my custom stored functions.

Are I committing some errors?
Is there an alternative way to achieve my goal?

Thanks in advance.

Meph

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Show-stored-function-code-tp5600485p5600485.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

On Wed, Mar 28, 2012 at 8:33 AM, mephysto <mephystoonhell@gmail.com> wrote:
> Hello,
> I'm trying to execute search in my stored functions code, but I have some
> difficulties. I'm using postgres 9.1.3, and if I execeute this query
>
> SELECT prosrc FROM pg_proc WHERE proname = <function name>;
>
> I retrieve an empty column for all my custom stored functions.
>
> Are I committing some errors?
> Is there an alternative way to achieve my goal?

That should work. What language are your functions written in?

merlin

On Wed, Mar 28, 2012 at 9:05 AM, Mephysto <mephystoonhell@gmail.com> wrote:
> Oh, I'm sorry.
>
> My function are written in sql and plpgsql.

the source code should be in pg_proc.  try a schema only dump with
pg_dump -s and see if your code is there.  either you are querying
your functions wrong (upper/lower case issue?) or something else is
wrong, like the functions are not there.

merlin

In pg_dump -s I can see my code, but not in pg_proc, not even I try with

select * from pg_proc


Column prosrc is empty for all my functions.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Show-stored-function-code-tp5600485p5600710.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

On Wed, Mar 28, 2012 at 9:31 AM, mephysto <mephystoonhell@gmail.com> wrote:
> In pg_dump -s I can see my code, but not in pg_proc, not even I try with
>
> select * from pg_proc
>
>
> Column prosrc is empty for all my functions.

how about \df+ yourfunc

?  I'm stumped here -- maybe this is some recently added security
feature I don't know about?

merlin

On Wed, Mar 28, 2012 at 3:50 PM, Mephysto <mephystoonhell@gmail.com> wrote:
> \df+ shows function code.

well, I'm suspecting operator error, because psql \df+ func queries
pg_proc. if you don't believe me, fire up psql with the -E switch and
it will echo all sql it sends to the server and you'll see it queries
pg_proc.  take the query it runs, and gradually cut it down and you
should find the reason.  perhaps line formatting is throwing you off
maybe.

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> On Wed, Mar 28, 2012 at 3:50 PM, Mephysto <mephystoonhell@gmail.com> wrote:
>> \df+ shows function code.

> well, I'm suspecting operator error, because psql \df+ func queries
> pg_proc. if you don't believe me, fire up psql with the -E switch and
> it will echo all sql it sends to the server and you'll see it queries
> pg_proc.  take the query it runs, and gradually cut it down and you
> should find the reason.  perhaps line formatting is throwing you off
> maybe.

I'm wondering if the OP has accidentally created another table called
"pg_proc".  Usually the system version would come first in the search
path, but maybe he's using a non-default search path?  If this is the
right guess, then the reason \df works is that it qualifies pg_proc
as pg_catalog.pg_proc.

            regards, tom lane

I'm trying to learn how to use stored procedures. In particular, I need to
return multiple values from the function, so I've been experimenting with
the OUT argument type. I'm writing code in C++ and using the libpq dll as
the interface to postgresql. I've created the following function, that
works:

CREATE OR REPLACE FUNCTION getdetailamts ( acct_no char(22), beg_moyr
char(6),
      end_moyr char(6), OUT beg_bal float8) AS $$
  DECLARE sum_totl float8;
  BEGIN
    SELECT SUM(dramt) FROM detrec WHERE detrec.acctno = $1 AND detrec.apmoyr
 >= $2 AND
               detrec.apmoyr <= $3 INTO sum_totl;
    beg_bal := sum_totl;
  END; $$ language plpgsql;

This returns the proper value, but when I attempt to add a second argument
of OUT type, I get an error when attempting to create the function:

CREATE OR REPLACE FUNCTION getdetailamts ( acct_no char(22), beg_moyr
char(6),
      end_moyr char(6), OUT beg_bal float8, OUT half_bal float8) AS $$
  DECLARE sum_totl float8;
  BEGIN
    SELECT SUM(dramt) FROM detrec WHERE detrec.acctno = $1 AND detrec.apmoyr
 >= $2 AND
               detrec.apmoyr <= $3 INTO sum_totl;
    beg_bal := sum_totl;
    half_bal := sum_totl / 2;
  END; $$ language plpgsql;

Can anyone tell me why adding the second OUT argument type causes the
function to return an error and not be created?

Thanks, Lynn

----- Original Message -----
From: "lmanorders" <lmanorders@gmail.com>
To: <pgsql-novice@postgresql.org>
Sent: Wednesday, August 22, 2012 4:28 PM
Subject: Problems with stored procedure


> I'm trying to learn how to use stored procedures. In particular, I need to
> return multiple values from the function, so I've been experimenting with
> the OUT argument type. I'm writing code in C++ and using the libpq dll as
> the interface to postgresql. I've created the following function, that
> works:
>
> CREATE OR REPLACE FUNCTION getdetailamts ( acct_no char(22), beg_moyr
> char(6),
>      end_moyr char(6), OUT beg_bal float8) AS $$
>  DECLARE sum_totl float8;
>  BEGIN
>    SELECT SUM(dramt) FROM detrec WHERE detrec.acctno = $1 AND
> detrec.apmoyr
> >= $2 AND
>               detrec.apmoyr <= $3 INTO sum_totl;
>    beg_bal := sum_totl;
>  END; $$ language plpgsql;
>
> This returns the proper value, but when I attempt to add a second argument
> of OUT type, I get an error when attempting to create the function:
>
> CREATE OR REPLACE FUNCTION getdetailamts ( acct_no char(22), beg_moyr
> char(6),
>      end_moyr char(6), OUT beg_bal float8, OUT half_bal float8) AS $$
>  DECLARE sum_totl float8;
>  BEGIN
>    SELECT SUM(dramt) FROM detrec WHERE detrec.acctno = $1 AND
> detrec.apmoyr
> >= $2 AND
>               detrec.apmoyr <= $3 INTO sum_totl;
>    beg_bal := sum_totl;
>    half_bal := sum_totl / 2;
>  END; $$ language plpgsql;
>
> Can anyone tell me why adding the second OUT argument type causes the
> function to return an error and not be created?
>
> Thanks, Lynn
>
I retyped this function and tried it again, and now the function is working.
I must have missed something somewhere. I've been staring at it for about 4
hours and couldn't get it to work, but now it does. Sorry for the false
alarm.

Thanks, Lynn

On 23/08/12 10:54, lmanorders wrote:
> ----- Original Message ----- From: "lmanorders" <lmanorders@gmail.com>
> To: <pgsql-novice@postgresql.org>
> Sent: Wednesday, August 22, 2012 4:28 PM
> Subject: Problems with stored procedure
>
>
>> I'm trying to learn how to use stored procedures. In particular, I
>> need to return multiple values from the function, so I've been
>> experimenting with the OUT argument type. I'm writing code in C++ and
>> using the libpq dll as the interface to postgresql. I've created the
>> following function, that works:
>>
>> CREATE OR REPLACE FUNCTION getdetailamts ( acct_no char(22), beg_moyr
>> char(6),
>>      end_moyr char(6), OUT beg_bal float8) AS $$
>>  DECLARE sum_totl float8;
>>  BEGIN
>>    SELECT SUM(dramt) FROM detrec WHERE detrec.acctno = $1 AND
>> detrec.apmoyr
>> >= $2 AND
>>               detrec.apmoyr <= $3 INTO sum_totl;
>>    beg_bal := sum_totl;
>>  END; $$ language plpgsql;
>>
>> This returns the proper value, but when I attempt to add a second
>> argument of OUT type, I get an error when attempting to create the
>> function:
>>
>> CREATE OR REPLACE FUNCTION getdetailamts ( acct_no char(22), beg_moyr
>> char(6),
>>      end_moyr char(6), OUT beg_bal float8, OUT half_bal float8) AS $$
>>  DECLARE sum_totl float8;
>>  BEGIN
>>    SELECT SUM(dramt) FROM detrec WHERE detrec.acctno = $1 AND
>> detrec.apmoyr
>> >= $2 AND
>>               detrec.apmoyr <= $3 INTO sum_totl;
>>    beg_bal := sum_totl;
>>    half_bal := sum_totl / 2;
>>  END; $$ language plpgsql;
>>
>> Can anyone tell me why adding the second OUT argument type causes the
>> function to return an error and not be created?
>>
>> Thanks, Lynn
>>
> I retyped this function and tried it again, and now the function is
> working. I must have missed something somewhere. I've been staring at
> it for about 4 hours and couldn't get it to work, but now it does.
> Sorry for the false alarm.
>
> Thanks, Lynn
>
>
>
>
No worries!

I remember when I was learning C many years ago, being stuck for several
hours wondering why a short program of less than 2/3 of a page was not
working as expected... turned out I had an extraneous semi-colon after a
while statement:

while (condition);
{
     do something
}

This was despite having extensive experience in FORTRAN & COBOL - so I
wasn't exactly a novice programmer!

I'm still experimenting with stored procedures and I have a couple of
questions that I can't seem to find answers to. Here is the function

CREATE OR REPLACE FUNCTION getdetailamts ( acct_no char(22), beg_moyr
char(6),
       end_moyr char(6), OUT beg_bal float8, OUT half_bal float8) AS $$
DECLARE recs record;
BEGIN
   beg_bal := 0.0;        -- if this is left out the later addition doesn't
occur
   half_bal := 0.0;
   FOR recs IN SELECT apmoyr, drtype, dramt FROM detrec  WHERE
      detrec.acctno = $1 AND detrec.apmoyr >= $2 AND detrec.apmoyr <= $3"
   LOOP
      beg_bal := beg_bal + recs.dramt;
   END LOOP;
END; $$ language plpgsql IMMUTABLE STRICT;

My first question is that if I don't initially set beg_bal := 0.0, the
addition in the loop doesn't seem to work. It returns NULL. I don't see
anything in the documentation that explains this behavior. I assume it has
something to do with beg_bal's initial value being NULL?

I change the loop part to:

LOOP
  IF recs.apmoyr < $3 THEN beg_bal := beg_bal + recs.dramt;
END LOOP;

Now I get an error when I attempt to create the function. Why?

Thanks, Lynn

On Thu, Aug 23, 2012 at 4:08 PM, lmanorders <lmanorders@gmail.com> wrote:
> I'm still experimenting with stored procedures and I have a couple of
> questions that I can't seem to find answers to. Here is the function
>
> CREATE OR REPLACE FUNCTION getdetailamts ( acct_no char(22), beg_moyr
> char(6),
>       end_moyr char(6), OUT beg_bal float8, OUT half_bal float8) AS $$
> DECLARE recs record;
> BEGIN
>   beg_bal := 0.0;        -- if this is left out the later addition doesn't
> occur
>   half_bal := 0.0;
>   FOR recs IN SELECT apmoyr, drtype, dramt FROM detrec  WHERE
>      detrec.acctno = $1 AND detrec.apmoyr >= $2 AND detrec.apmoyr <= $3"
>   LOOP
>      beg_bal := beg_bal + recs.dramt;
>   END LOOP;
> END; $$ language plpgsql IMMUTABLE STRICT;
>
> My first question is that if I don't initially set beg_bal := 0.0, the
> addition in the loop doesn't seem to work. It returns NULL. I don't see
> anything in the documentation that explains this behavior. I assume it has
> something to do with beg_bal's initial value being NULL?

that is correct (and any subsequent calculation then returns null)

> I change the loop part to:
>
> LOOP
>  IF recs.apmoyr < $3 THEN beg_bal := beg_bal + recs.dramt;
> END LOOP;
>
> Now I get an error when I attempt to create the function. Why?

structure of if statements is If..then..end if;

LOOP
  IF recs.apmoyr < $3 THEN
    beg_bal := beg_bal + recs.dramt;
  END IF;
END LOOP;

merlin