Bill Epstein wrote > 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
You likely need to use "EXECUTE" in PostgreSQL > 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 Since "COMPONENT, ACTIVITY, AUDIT_LEVEL, NOTE, SQL" is only 5 columns and you are sending 6 it is not surprising that you are getting an error. > 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). You still have to deal with the fact that PostgreSQL functions operate in the transaction context of the caller; they cannot set their own. Depending on how you write the function and the caller if you eventually ROLLBACK you could lose the logging. > - What's the difference between hitting the Execute Query and Execute > PGScript buttons? Both seem to compile the functions. Execute Query just sends the statement(s) to the server Execute PGScript wraps the statements in a transaction so that either they are succeed or all fail. Basically with Execute Query if a statement in the middle fails everything before it still commits (auto-commit) For a single statement there is no difference. > - What are the differences among PL/SQL, PL/PGSQL and pgScript. The first two are languages you write functions in. pgScript is simply an informal way to group a series of statements together and have them execute within a transaction. > - 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? No idea - but probably. But there are likely many similarities too. > 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 Didn't even open these... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Questions-on-dynamic-execution-and-sqlca-tp5813929p5813934.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general