Re: Questions on dynamic execution and sqlca

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.