Questions on dynamic execution and sqlca

I'm very new to Postgres, but have plenty of experience developing stored
procs in Oracle.

I'm going to be creating Postgres stored procedures (functions actually,
since I discovered that in postgres, everything is a function) to do a
variety of batch-type processing. These functions may or may not be called
by the .Net application that is being developed. To support both my
Postgres function development and run-time monitoring, I wanted to develop
generic logging functions that would be called by other Postgres functions
to be developed in order to help trace through code and collect error
information.

The attached create_log_utilities.sql holds plsql for creating two logging
functions (one for logging status messages, and one for logging errors).
In the log_msg function, the various sets of EXEC and EXECUTE statements
are from my experimenting with dynamically generating SQL. If I could get
it working, the intent is to be able to add a LogTableName as an input
parameter, thereby allowing individual developers to utilize their own
version of the log table (w/ the same columns). I've been able to do this
sort of thing w/ Oracle before.

I've tried a variety of ways based on the on-line docs I've seen, but I
always get a syntax error on EXEC when I use only the line EXEC statement
(is there a setting I need to set in order to be able to include EXEC
directives?). The closest I've come is the currently uncommented prepared
statement - it compiles, but I get the following error messages:

INFO: INSERT INTO UTILITY.BPC_AUDIT (COMPONENT, ACTIVITY, AUDIT_LEVEL,
AUDIT_TIME, NOTE, SQL) VALUES ('Overpayment','Create
TLI','LOG','2014-08-06 10:44:23.933','Created TLI','INSERT INTO
TLIA...')
CONTEXT: SQL statement "SELECT utility.LOG_MSG (p_log_yn, p_component,
p_function, p_note, p_sql)"
PL/pgSQL function utility.logging_test() line 24 at PERFORM
ERROR: INSERT has more expressions than target columns
LINE 3: VALUES ($1, $2, $3, $4, $5, $6)
^
QUERY: PREPARE myinsert7 (text, text, text, timestamp, text, text) AS
INSERT INTO UTILITY.BPC_AUDIT (COMPONENT, ACTIVITY,
AUDIT_LEVEL, NOTE, SQL)
VALUES ($1, $2, $3, $4, $5, $6)
CONTEXT: PL/pgSQL function utility.log_msg
(character,text,text,text,text) line 48 at SQL statement
SQL statement "SELECT utility.LOG_MSG (p_log_yn, p_component,
p_function, p_note, p_sql)"
PL/pgSQL function utility.logging_test() line 24 at PERFORM
********** Error **********

ERROR: INSERT has more expressions than target columns
SQL state: 42601
Context: PL/pgSQL function utility.log_msg
(character,text,text,text,text) line 48 at SQL statement
SQL statement "SELECT utility.LOG_MSG (p_log_yn, p_component,
p_function, p_note, p_sql)"
PL/pgSQL function utility.logging_test() line 24 at PERFORM

In the other function (log_error ), the problem I'm having is that I'm
trying to pull out the sqlca error code and description (as I've done in
the past w/ Oracle), in order to write that information in my log table.
The intent is that this function will only be called from within an
EXCEPTION block (as I do in my logging_test function - I purposely run a
bad query to trigger it).

To exercise the code, I'm just executing select utility.logging_test(); in
a query window.

A few other items I could use clarification on:
- What's the difference between hitting the Execute Query and Execute
PGScript buttons? Both seem to compile the functions.

- What are the differences among PL/SQL, PL/PGSQL and pgScript.

- I installed Postgres 9.3.4 and I'm using PEM v4.0.2. When I click on
the icon to "Execute arbitrary SQL queries", I notice that the icons on the
window that opens are different from the pgAdmin PostgreSQL Tools window
that opens if I double-click on one of my .sql files. Is there a
difference in these tools?

Attached are the relevant scripts:
(See attached file: create_bpc_audit.sql) - Create the log table
(See attached file: create_log_utilities.sql)- Code to create the two
logging functions
(See attached file: test_log_utilities.sql)- Code to exercise the msg and
error logging functions

Thanks.
Bill

_____________________
William Epstein
Consulting I/T Specialist
AIS ADM Information Management
US Federal
Office/Fax: 301-240-3887, Tie Line: 372-3887
International Business Machines (IBM) Corporation
Global Business Services (GBS)