Postgres Pro
Downloads
Home   >   mailing lists

Thread: logging SQL statements

logging SQL statements

From
"c k"
Date:
Is it possible to log all sql statements submitted to database server in a table in that database it self? This will be different from database logs which are written by db server. 

Regards,
CPK

PLSQL function calling another function

From
Date:
Hi All,
 
I am trying to figure out how to call one PLSQL function from another, specifically how to access the return values from the callee.
 
I made two test functions, A and B. A calls B.
 
B returns two values:  
                OUT tid integer
                OUT msg character varying
 
In caller function A, I do the following:
                SELECT B(1) INTO rec;       -- rec declared as a RECORD
                RAISE DEBUG 'XXX %   [%]', rec.tid, rec.msg;
The runtime error I got was:
 
                 ERROR: record "rec" has no field "tid"
                 SQL state: 42703
 
The full function code is below.
 
Thanks,
CYW
 
 
 
 
 
 
--------------------------------
FUNCTION B(IN id integer, OUT tid integer, OUT msg character varying)   RETURNS record AS BODY$
DECLARE
BEGIN
 msg := 'MSG';
 tid := 100;
 RETURN;
END;
 
FUNCTION A(IN x integer, IN "text" character varying, OUT whatever character varying)  RETURNS character varying AS $BODY$
DECLARE
 rec RECORD;
 tid int4;
 msg varchar;
BEGIN
 SELECT B(1) INTO rec;
 RAISE DEBUG 'XXX %   [%]', rec.tid, rec.msg;
 RETURN;
END;

Re: PLSQL function calling another function

From
Tom Lane
Date:
<cyw@dls.net> writes:
> I am trying to figure out how to call one PLSQL function from another, specifically how to access the return values
fromthe callee. 

> I made two test functions, A and B. A calls B.

> B returns two values:
>                 OUT tid integer
>                 OUT msg character varying

> In caller function A, I do the following:
>                 SELECT B(1) INTO rec;       -- rec declared as a RECORD

This produces a record containing a single composite column (named "b"),
as you would see if you did the same SELECT by hand:

regression=# select b(1);
     b
-----------
 (100,MSG)
(1 row)

You will get the results you expect if you do something like

    select * into rec from b(1);

            regards, tom lane

Re: logging SQL statements

From
Greg Smith
Date:
On Thu, 9 Oct 2008, c k wrote:

> Is it possible to log all sql statements submitted to database server in
> a table in that database it self?

It's possible to cobble together something to appoximate that without too
much trouble if you're running V8.3.  You can use the CSV log format to
make the logs show up in a tabular fashion:
http://www.postgresql.org/docs/current/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG

And then you can change log_statement (on that same page) to "all".  The
main tricky part is that you need to account for log rotation and such
with whatever approach you take; you're on your own to figure out when the
server is finished with a log such that you can import it, or to write
something that imports more often.  It's possible to write something to
use a "tail -f" type of approach that imports after every line is read,
but that's harder.

BTW:  I'm dropping the admin list from my reply here because I'm not
subscribed to that one at the moment.  It's bad form to cross-post to
multiple lists here.  Pick the one you think you're more likely to get a
response on first, and only if you don't get anything useful back after a
wait should you submit your question to a second list.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD